Group Boxes: Ensuring Options are Check Boxes, Not Radios

[jdanniel]

Hello.

I'm new to Base, and have a question about creating and using group boxes with check boxes and radio buttons.

I'm designing a flat-file database for my movie collection. I want a group of check boxes, and I want a separate group of radio buttons.

I'm pretty sure I figured out how to create such groups. I use the Group Box tool, with the Wizard feature enabled.

I've put in 15 different entries for the first box, which is for Genre, such as Drama, Comedy, etc.

(I want to use check boxes for this, and not radio buttons, since a movie can fall into more than one genre category. )

The box looks good, but the problem is this: There are radio buttons, not check boxes.

How do I control whether the group box contains check boxes or radio buttons?

If you need further information from me, please let me know. Thank you.

J. Danniel

[Villeroy]

What you are trying to do can not work with a single table since one field can not take more than one value.
You could create a main table with a film-id and a category-id among other fields, another table for categories with id and name of category and a third table where you map the categories with categories.

Another approach would map categories to binary digits:
1 A
2 B
4 C
8 D
16 E
so a value of 6 would include categories B and D and 31 would include all categories. I believe, this is one of the cases where some scripting is required in order to represent this logic in a form.

[jdanniel]

Thanks for the reply.

I'm going to have to think about this. Being a novice, I'm seeking easy methods to get what I want. I'll continue dabbling.

Jd

[Villeroy]

For a small amount of categories the most simple solution would be X boolean (yes|no) fields, one for each category.

[jdanniel]

I'm just thinking out loud here, so please bear with me.

How about I create two radio button groups, Genre 1 and Genre 2.

I'd limit users (my friends and family) to two genres per film.

(Even though a few films have even more than two.)

That would certainly be easier than trying to mess with things I don't understand.

But I do foresee one problem with this idea. If I, or someone else, can figure out a solution to it, that would be great.

Here's the problem I foresee:

Let's use the film Spaceballs as an example. It's a comedy, and it's science fiction.

Let's say I select COMEDY for Genre 1, and SCIENCE FICTION for Genre 2.

But someone else does the exact reverse in their copy…. Science Fiction for Genre 1, and Comedy for Genre 2.

When you search for comedies in Genre 1, Spaceballs won't show up.

If this person and I share databases, to compare what films we have, I wouldn't know he/she had Spaceballs, if I went by what I used.

Does this make sense?

So…is there a way to set up a query, simply, to do an either-or search of these two fields?

Have it search for Comedy in either Genre 1 or Genre 2, and search for Science Fiction in either or?

Would this idea complicate what I'm doing further, or should I just forget about this and just make one radio button group for genres, and force people to pick one genre per film, and leave it at that?

[kabing]

You could create another table, called Movie Genres.

Fields would be:

ID (integer)
MovieID (relates to your primary table)
Genre

There could be multiple records per movie; on the form it would be a subform table.

I'm rushed now, but if this sounds interesting, post back and I can provide more details.

kabing

[jdanniel]

It does sound interesting, mainly because it's something I don't fully understand, and dabbling with it might help me learn database design further.

I never imagined this would be so complicated. Maybe it's complicated only to me....thanks for the reply! Jd

[kabing]

Doing what I suggested means going from a flat-file databse to a relational database.

When you open your database, what does it say at the very bottom of the window?
If it says "Embedded database" and "HSQL database engine," then you're fine. If it says something else, we'll need to change some other things first.

Here's the basic idea: you can have more than one genre for a movie, so you create a separate table that connects movies to genres.

Do you have a field identified as a primary key your existing movie table?
If not, that will need to be fixed first.

You might find the To Base and Back Again turorial series helpful. Specifically in your case, the following "episodes":
Designing the Database on Paper
Establishing Relationships
Creating a Data Entry Frorm with a Subform

The tutorioals are written for NeoOffice. Since NeoOffice is based on OpenOffice.org, the instructions should work fine for OpenOffice.org, if you keep the following things in mind:
1) references to the command key in a Neo tutorial are the equivalent to the control key in OOo. (i.e. Command-C becomes Control-C) Note that I'm not sure if this is true with OOo for X11.
2) control-clicking is the same as right clicking
3) icons sometimes look different, as NeoOffice 2.2.2 has a custom icon set.


kabing

[jdanniel]

Doing what I suggested means going from a flat-file database to a relational database.

This is something I've tried to avoid doing, because I've never really understood relational databases. The concept of "one-to-many" eludes me.

I toyed with the idea of a relational database at the outset, because of the Director field. But I decided it wasn't worth the effort.

Maybe it's time I try a relational db with Base.

It won't hurt to try it. The worst that can happen is I abandon joined tables, and just create 15 different fields in a flat-file db...one for each genre.

[Villeroy]

I suggested a relation in my first reply, but when I had a similar problem I could not figure out how to map this approach to a Base form without writing macros.
How to bind a dozed of check boxes, one for each category?

[kabing]

If the OP goes the route I'm suggesting, check boxes won't enter into the picture. In fact I'm afraid I forgot that's where this question started. But I still think this is the best way to go if the OP wants to be able to assign more than one genre to a movie.

In terms of relational databases, I wish I could point you to a good book on the subject, but I'm afraid I've learned by doing.

Maybe a couple of examples?

In relational databases, records in one table relate to records in another table. In some cases, one record in the first table relates to one--and only one--record in the second table. This is called a one to one relationship. For example, in a personnel database, it may be necessary to restrict access to salary information to a few people, but more people need the names and addresses of employees. So the database has one table for the employees' contact information, and a separate table for salary information. Each employee has only one record in each table. The two tables are linked (or related) by an Employee ID field, so the database engine knows which salary record goes with which employee.

But sometimes a record in the first table relates to more than one record in a second table. This is called a one to many relationship. Here are some examples:
Birdwatching: On any given field trip (table 1), a birder will see many different birds (table 2) the tables are related by field trip ID.
Movies: Any given movie may have more than one genre

In some cases, one appears to need a many-to-many relationship. i.e. for a school database, classes will include many students, but students will take several classes. This requires a different approach, as many-to-many relationships are not permitted, but there's no need to go into the "work around" for that here.

kabing

[Villeroy]

In terms of relational databases, I wish I could point you to a good book on the subject, but I'm afraid I've learned by doing.

I find the Wikipedia entries quite helpful.
Two starting points:
http://en.wikipedia.org/wiki/Relational_database
http://en.wikipedia.org/wiki/Database_normalization
The latter points to some well chosen external links:
http://en.wikipedia.org/wiki/Database_n ... rnal_links

[jdanniel]

I suggested a relation in my first reply, but when I had a similar problem I could not figure out how to map this approach to a Base form without writing macros.
How to bind a dozed of check boxes, one for each category?

Oh man, don't discourage me!

Is it really going to be this complicated? Just for a simple little movie database?

I'm thinking I might be better off just creating 15 Boolean fields, one for each genre, and using Boolean check boxes. Wouldn't that be easier than creating relational databases that I might not understand?

[Villeroy]

I'm with you. The Base component is too bad. It is sufficient to import tabular data from various sources, run simple SELECT queries and create pretty reports in Writer. But the tools to create or manage relational databases are a failed proof of concept. They are insufficient, incomplete and full of bugs.

[kabing]

You don't have to write a macro for the method I'm suggesting, as long as you don't have to have check boxes; there are other ways to set the genre.

But a set of check boxes as you suggest will work, and you wouldn't have to go the relational route that way.

What is it about the relational aspect that you don't understand?

I have not been as frustrated as Villeroy with the use of Base as a relational database. But I also don't have Villeroy's experience with it, either.

kabing

[jdanniel]

But a set of check boxes as you suggest will work, and you wouldn't have to go the relational route that way.
What is it about the relational aspect that you don't understand?

It's not easy for me to answer this question, and whenever I do try, I get frustrated because I can't even articulate what it is I'm not grasping.

I do not understand why two separate tables are required for one database, for example. For a relational database, do I need two wholly different databases linked together, or just two tables?

If I just need two tables, then I'm still going to have one database file, right? And it's going to be the same size, I assume, whether it's flat-file or relational. The same quantity of data is going to be stored. Or is that incorrect?

If the issue of a relational database is just size, then it's a non-issue, because I have so much storage space, I don't even think about size.

So, the purpose of linking fields or tables or however you do it must be for some other reason. What that reason is, I've never understood.

Then, there's another problem: How do I determine WHICH fields to link or join together? And how? I've read a substantial amount about this over the years, and it goes in one brain cell and out the other.

Here's an example: Let's say I want to find, in my movie collection, every film starring Tom Hanks. Many people have said to me, "Make it relational."

Why? Why do I need a separate table for that, and then connect two tables together for that?

Maybe relational databases aren't necessary for a movie or music collection, but they make sense for other things I know little about?

Anyway...here's what I did: I created a bunch of Boolean yes/no checkboxes, one for each type of genre. Voila. Done.

The next step for me is to find out how to do searches/finds/queries. Let's say a movie has three different genres. This Is Spinal Tap, for example. It's a comedy, a documentary (okay, a fake one, but that's beside the point) and a musical.

Three genres. Or Spaceballs, which is science fiction and comedy. I have to decide whether to do a search that requires all of the genres marked for a movie, or any. I think that would be an AND/OR situation.

The form design is done. Now onto queries....

[kabing]

I do not understand why two separate tables are required for one database, for example. For a relational database, do I need two wholly different databases linked together, or just two tables?

One database file, two or more tables in the file.

If I just need two tables, then I'm still going to have one database file, right? And it's going to be the same size, I assume, whether it's flat-file or relational. The same quantity of data is going to be stored. Or is that incorrect?

I doubt the size will be the same because the two structures, and thus the amount of actual data stored on the hard drive is different, even though the same information is stored. In any event, the decision to go relational isn't about space needed.

So, the purpose of linking fields or tables or however you do it must be for some other reason. What that reason is, I've never understood.

Here's a concrete example from my birding database. I go on a birdwatching field trip one day, and I see 20 different species of birds one day. The next week I go on another field trip and I see 15 species of birds. I want to be able to keep track of information on each of my field trips (date, time, place, weather, etc.) and I want to list each species of bird I've seen that day, along with other information about the birds (male, female, identified by voice or song, etc.)

If I try to do this with a flat database, I have to decide how many slots to allow for birds (Bird 1, Bird 2, Bird 3, etc). What if I allot 30 slots and then have a field trip bonaza and identify 50 species? I don't have room for them all. Plus, how do I search for all the times I saw a Bald Eagle? I have to build a query searching 30 different fields for the text "Bald Eagle." You realized this was an issue when you mentioned the problem with having a Genre 1 and Genre 2 field.

If I do this with a relational database, I can have separate tables. I have one table for field trips. Each record in that table represents one field trip. I have one table called Sightings for birds seen. Each record in the Sightings table corresponds to one sighting of a particular species. So I might have 12 records of Bald Eagle sightings, each from a different field trip. (In actuality, I have several other tables as well, but I'm trying to keep this simple for the moment).

Then, there's another problem: How do I determine WHICH fields to link or join together? And how? I've read a substantial amount about this over the years, and it goes in one brain cell and out the other.

Which fields link or join depends on how the tables relate to each other. In the birding example, the connecting point is the field trip. Both tables have a field for FieldTripID#, those are the fields that are linked. How is answered in one of the wiki articles I mentioned.

Here's an example: Let's say I want to find, in my movie collection, every film starring Tom Hanks. Many people have said to me, "Make it relational."

Why? Why do I need a separate table for that, and then connect two tables together for that?

You only need a relational setup for this if you want to assign more than one star to a given movie. Say you want to include Sleepless in Seatle and you want to list both Tom Hanks and Meg Ryan. Now you could have one field for main actor and one for main actress. But then what do you do with a movie like Brokeback Mountain? Or how do you decide who the "main actor" is for The Fellowship of the Ring? If you want to be able to list more than once actor per movie, then you need to have a relational database. A flat database and checkboxes work for Genres because that's a relatively short list. Can you imagine trying to do checkboxes for all the potential actors and actresses?

Maybe relational databases aren't necessary for a movie or music collection, but they make sense for other things I know little about?

If I were doing a movie or music collection database, I'd probably make it relational. But I do think there's a conceptual issue here for you; something about relational databases just doesn't click for you. All of us have those; for me it's spatial relationships; my dad can manipulate 3-D objects in his head in his sleep, but I can't do it fully awake, and no amount of trying on his part has gotten me to "grok" how to do it.

Anyway...here's what I did: I created a bunch of Boolean yes/no checkboxes, one for each type of genre. Voila. Done.

That's a great solution for this situation.

kabing

[jdanniel]

If I were doing a movie or music collection database, I'd probably make it relational. But I do think there's a conceptual issue here for you; something about relational databases just doesn't click for you. All of us have those;

Yes, exactly. And I think you pinpointed where my mental block lies:

Which fields link or join depends on how the tables relate to each other.

I think you've brought my mental block to the surface: WHAT fields do you join together?

I'm pretty sure I understand the purpose of a relational database, as it pertains to my database. You alluded to it. With a flat-file database, if I want to include actors, I am forced to limit the amount of Actor fields I have. The problem then is: How do I decide how many Actor fields to put in the form?

Main Actor 1
Main Actor 2

Okay, what if you have an ensemble picture such as The Big Chill? Or a concert film with a ton of musicians, such as The Last Waltz? Who do you leave in, and who do you exclude?

Am I understanding this a bit more now?

Is the following statement correct: With a relation set up, I need only one field for Actor, and can include as many actors as I want? Even two dozen?

The same applies for Actress, right?

With a relation set up, I'd probably need only four fields: Actor, Actress, Supporting Actor, Supporting Actress.

I would then have to design the form so that several boxes would appear, to enter and display each actor. This is another issue altogether, but we'll deal with that later.

Okay, so...here's where my mental block lies, as I've said before: What do I link together? This question doesn't just apply to a simple movie or music database. It applies to every database I'll ever think about writing. How about a Rolodex database? Do I make that flat file? It's like a phone book.

But what if several people use the same phone number? Does the database become relational then?

Anyway...if I can somehow figure out what fields to put into each table, as well as which fields to leave out, and then understand what fields to join together, then I may have conquered this mental block.

Thanks for replying. Your assistance and patience are both helpful and appreciated.

Jd

[kabing]

Is the following statement correct: With a relation set up, I need only one field for Actor, and can include as many actors as I want? Even two dozen?

You're closer, but not quite there. With a relational database, you don't need any field for actor in the Movies table, but you can include as many actors relating to that movie as you want in the MovieActors table. I'm guessing that statement has you confused; just set that aside for a minute and see if you can follow me.

The Movie table might include the following fields:
MovieID (the primary key; more on this below)
MovieTitle
DateReleased

For our purposes here, the MovieID is the most important. It is the primary key for the Movie table. One requirement for a primary key is that the contents of that field must be unique for each record in the table; in other words, no two movies can have the same number (or text) in the MovieID field. The easiest way to do this is to make it MovieID an integer type field and set it to autoincrement. Then Base takes care of assigning a unique number to each record. (Initially you might think you could use the MovieTitle field for this, but sometimes two or more movies have the same name, remakes like The Parent Trap or Sabrina, for example, or movies based on books, like Shadowlands or The Fellowship of the Ring.)

Then there is the MovieActors table. It might include the following fields:
MA_ID (the primary key for this table)
MovieID (the "foreign key"-more about this below)
First Name
Last Name
Role (the name of the character they played)
RoleType (Main, Supporting, EnsembleMember, etc.)

This table relates to the Movies table through the MovieID field. The MovieID field of the MovieActors field is the foreign key because it relates to the primary key of another table. In the MovieActors table, the MovieID field is not unique. More than one record can have the same MovieID number.

Note: tables can relate through fields that are not primary keys, as long as certain conditions are maintained; but I'm trying to keep things (relatively) simple here.

So, you might have the following data in the Movies table:
MovieID-----MovieTitle
1-----Sabrina
2-----Fellowship of the Ring
3-----Parent Trap

Then you might have the following data in the MovieActors table:
MA_ID-----MovieID-----First Name Last Name
1-----1-----Harrison Ford
2-----1-----Julia Ormond
3-----1-----Greg Kinnear
4-----2-----Elijah Wood
5-----2-----Sean Astin
6-----2-----Liv Tyler
7-----2-----Orlando Bloom
8-----2-----Viggo Mortensen
9-----3-----Lindsay Lohan
10-----3-----Dennis Quaid

See how the MA_ID is different for each line, but the MovieID is sometimes the same? The MovieID field tells base which movie these records relate to.

Don't start building yet, because we might want to take this all one step further (yet another table), and if we do, a few things in the MovieActors table setup will change. But I want this much to make sense to you first.

Notice also that I haven't talked about how you enter or look at the data; that involves building a form, and right now I just want to focus on the conceptual links between the tables. If you want to keep pursuing this, we'll move on to how you actually make the two tables relate on a form.

Thanks for replying. Your assistance and patience are both helpful and appreciated.

You're welcome. As you can probably tell, I enjoy doing this. And I've had others be equally (if not moreso) patient with me when it comes to my own mental blocks.

kabing

[jdanniel]

With the above reply, you probably have done more to help me get closer to understanding this than any manual or help documentation.

I do want to clarify something, though...I'm not sure if I made this clear or not, so I'll say it now.

I do not need, or really want, Actor or Actress fields in my database. They are not necessary for my purposes.

However, we may need to refocus our attention to the Genre field, and turn that into a relationship. Here's what I mean:

After re-reading something you wrote in a previous post, I'm wondering if my idea for 15 Boolean Yes/No check boxes, one for each genre type, is a good idea after all.

While I've successfully created a set of Genre checkboxes, we once again are back to this:

If I try to do this with a flat database, I have to decide how many slots to allow for birds (Bird 1, Bird 2, Bird 3, etc). What if I allot 30 slots and then have a field trip bonaza and identify 50 species? I don't have room for them all. Plus, how do I search for all the times I saw a Bald Eagle? I have to build a query searching 30 different fields for the text "Bald Eagle." You realized this was an issue when you mentioned the problem with having a Genre 1 and Genre 2 field.

I'm going to have to build a query with all 15 Genre check box fields. So, maybe instead of creating a relational database for the purposes of having many actors/actresses, perhaps I should base the relation on genres. One movie could have several genres...even as many as five, if you want to be obsessive about it.

Crouching Tiger Hidden Dragon:
Action
Martial Arts
Romance
Foreign
Fantasy

So...is it possible for you and I to focus on making a relational database based on Genre, instead of Actor/Actress, if it means streamlining the query process at a future time?

But we're at a point now where we might be going in circles, because another person posted a response in this thread saying he couldn't create check boxes with a relational database without macros.

Does this plan make sense?

Jd

[kabing]

I'm glad my explanation was helpful. We can certainly refocus on Genres.

It is possible to have checkboxes with a relational database; I think Villeroy's comment about macros was about using checkboxes on a form but having Base store a particular value if the box is checked. (For example, if Fantasy is checked, Fantasy is saved in the Genre field). If checkboxes are related to Boolean (Yes/No) fields, you don't need to worry about macros.

So you could still use checkboxes but I do think creating queries would be more complicated with checkboxes in a flat database file than going the relational route.

One issue with this route is consistent data entry. That is, how do you make sure that you always use precisely the same value for a particular genre. If you use Science Fiction one time and SciFi the net, a search for Science Fiction won't find any movies marked SciFi.

There are (at least) two ways to handle this. One uses two additional tables and (Genres and MovieGenres). The advantage is that you can force the user to choose from -- and only from-- a pre-defined list, to keep data entry entirely consistent. The disadvantage is that if you need to add a Genre to your list, you have to open another form to do so. This approach is also best for relatively short lists, because it uses a drop-down list box in the data entry form. To many items in the list, and the drop down becomes unmanageable.

The other option is to only use one additional table (MovieGenres) and use a ComboBox to have Base create a list of all previously used Genres. You can select one of those, or enter a new Genre. That new Genre will appear in the list in the future. This approach also uses a drop-down, but a diffrent type that allows you to begin typing and then Base will offer a suggested genre for you. It doesn't provide the same level of protection for consistent entry, but it is easier to add new items to the list.

I can walk you through either option, just let me know which way you'd like to go.

kabing

[jdanniel]

So you could still use checkboxes but I do think creating queries would be more complicated with checkboxes in a flat database file than going the relational route.

One issue with this route is consistent data entry. That is, how do you make sure that you always use precisely the same value for a particular genre. If you use Science Fiction one time and SciFi the net, a search for Science Fiction won't find any movies marked SciFi.

This is interesting...when I began thinking about this database, this is exactly why I decided to go the checkbox route. I wanted to prevent typographical errors that might come from manual entry of genre names. I felt check boxes would eliminate the possibility of people mistyping Science Fiction, or Cinema Verite, or something like that.

So, now I'm a little confused. Are you saying consistent data entry may still be a problem, despite my using check boxes? Or are you saying check boxes (Boolean yes/no) will eliminate the potential problem of consistent data entry?

You mentioned drop-down menus. We both agree that too many entries in a drop-down list will make it cumbersome. But I'm also confused about how more than one genre can be selected via a drop-down menu.

I do know there are boxes that permit multiple selections. This can be done with the CONTROL key or even the SHIFT key. Is that the direction you were planning to take me in?

If so, I would prefer not to go there, because some users of the database might not know how to do multiple selections, and that would require instructions nearby the drop-down menu. I'd prefer to keep the form as clean and text-free as possible.

That's why I chose check boxes.

Or...were you going in a different direction than this?

Also: I think you were talking about a drop-down menu with Auto-Complete. Am I correct about this?

This approach also uses a drop-down, but a diffrent type that allows you to begin typing and then Base will offer a suggested genre for you. It doesn't provide the same level of protection for consistent entry, but it is easier to add new items to the list.

I loathe auto-complete. It's only going to result in typos...I'll guarantee it. Again, this is why I selected check boxes, and am limiting the number of genres to 15. I could put in 50 Genre types (or even more) if I want to, just as you mentioned 50 different species of birds. But I do want to place some--not many, but some--restrictions. Thus, I'm restricting the number of Genre types available. I reserve the right to change my mind about this, however, if I learn more and realize my current state of thinking isn't ideal.

Jd

[kabing]

So, now I'm a little confused. Are you saying consistent data entry may still be a problem, despite my using check boxes? Or are you saying check boxes (Boolean yes/no) will eliminate the potential problem of consistent data entry?

Sorry; let me try to be clearer. If you want to continue with a flat database file, checkboxes tied to Boolean fields will elminiate the danger of inconsistent data entry. But searching would be slightly more complicated.

If we go the relational route, then we need to use another approach to keep data entry consistent. While it's possible to use checkboxes in relational databases, in this particular application checkboxes aren't what we need if you go relational.

You mentioned drop-down menus. We both agree that too many entries in a drop-down list will make it cumbersome. But I'm also confused about how more than one genre can be selected via a drop-down menu.

You can't choose more than one genre per record in the MovieGenres table, but more than one record in this table can relate to your Movies table. So for Crouching Tiger Hidden Dragon, you would have one record in the Movies Table, and five entries in the MovieGenres table that relate to that movie.

I loathe auto-complete. It's only going to result in typos...I'll guarantee it. Again, this is why I selected check boxes, and am limiting the number of genres to 15.

Then it sounds like we want to go the list box route, which will require two additional tables. Do you already have a Movies table started? If so, please list the field names and types for me. (i.e. MovieId--integer).

kabing

[jdanniel]

While it's possible to use checkboxes in relational databases, in this particular application checkboxes aren't what we need if you go relational.

I'm not sure I know enough about relational databases to understand why not. Can you elaborate, please, a little?

You can't choose more than one genre per record in the MovieGenres table

Okay...I am pretty sure I understand the first part of this...if this were a flat-file database, I could not have several selections for one field.

This is why I couldn't create a set of check boxes, each one linked to the Genre field. A field can only have one value. I can easily create a set of radio buttons, because it, by definition, can permit only one value.

but more than one record in this table can relate to your Movies table.

I'm still working on understanding this. I'm assuming that this statement is applicable only after two or more tables are created, and some joining together at some point is done.

I still need to understand what's joined together. Now, though, you are bringing in additional tables. I'm going to have at least three now! And they're all going to be joined together? I'm having enough trouble understanding the concept of just two linked together.

I'll provide you with the fields, but that will have to wait until Friday afternoon, I'm afraid. Thanks for keeping up with me here.

Jd

[kabing]

While it's possible to use checkboxes in relational databases, in this particular application checkboxes aren't what we need if you go relational.

I'm not sure I know enough about relational databases to understand why not. Can you elaborate, please, a little?

Checkboxes (as I'm talking about them) are used with Boolean (yes/no) fields. You can use Yes/No fields in a relational database. But you want to use a text field that stores the name of a genre; that means using a different type of form control.

but more than one record in this table can relate to your Movies table.

I'm still working on understanding this. I'm assuming that this statement is applicable only after two or more tables are created, and some joining together at some point is done.

Correct.

I still need to understand what's joined together. Now, though, you are bringing in additional tables. I'm going to have at least three now! And they're all going to be joined together? I'm having enough trouble understanding the concept of just two linked together.

I'll provide you with the fields, but that will have to wait until Friday afternoon, I'm afraid. Thanks for keeping up with me here.

Jd

I think this will make more sense to you once I spell out how to construct the tables and the data entry form. No hurry on the fields list; I'm tied up at least through Sunday afternoon, so I won't be able to get to it immediately in any event.

Oh, one more essential question. Open your existing move database file. At the very bottom, in the status bar, what does it say? (e.g. does it say "embedded database" and "hslq database engine" or does it say something else? That makes a difference for where we go from here.

kabing

[jdanniel]

But you want to use a text field that stores the name of a genre; that means using a different type of form control.

I'm not sure I understand why, but I'm going to take a stab here. Please let me know if I'm right.

Just a guess...is it because even with a relational database, I'd still have to use 15 fields in a query, one for each Boolean field?

You need the names of the genres, because otherwise you'd just be searching for YES and NO?

Am I close?

See you soon....thanks again.

Jd

[kabing]

Right!

[jdanniel]

As of right now, the database reads Embedded Database, and HSLQ database engine.

I've been dabbling, and reading. I think I successfully created a relational database! I've encountered a couple of snags, but I'm getting ahead of myself.

Here are the two tables I created:

Table 1
MovieID
Title
Director
Running Time
Location
Comments

Table 2: Genres

MovieID
GenreID
Genre Name

So far, so good? I linked the two together via MovieID.

I then went ahead and created a form with a subform. I set the subform up to be in datasheet view mode, so I can see all of the genres at once.

And you know what? It worked!

The snag, however, has to do with referential integrity, which I'm just beginning to understand--thanks to a book I'm reading. When I try to delete a record from the database, I get an integrity error warning.

I think I understand why. I haven't figured out how to check for integrity with Open Office, and therein lies the problem. I can't delete a record without also deleting the reference for the genre in the Genre table.

But this is a step I want to work on in the future. Right now, I want to figure out if there is a cleaner way to display all of the genres without the subform being in datasheet view mode. If not, no big deal.

I also want to start the MovieID integer increments at One, not Zero. If possible. Right now, the movie title with the 0 Movie ID number is "Dummy Placeholder."

I'm sorry if I got a little ahead of things while you were gone, but as you can see, I've learned a lot. Jd

[kabing]

I'm glad you went ahead; you've gotten the hang of this.

I'm sure about the integrity issue; that's beyond my expertise. My guess is it my be solved by certain settings in the Tools>Relationships window. I see you have another post about that, so hopefully someone else can help that.

I don't think the datasheet layout for the subform can be avoided, since the subform contains more than one recrod for each record on the main form. But I could be wrong.

Since you set the 0 movie to a placeholder, you should be able to delete that movie (delete any genres first if necessary). I don't think the 0 will get used again.

The only thing I would have done differently is named your Table 2 MovieGenres rather than Genres. You'll see why below, but we can work around it.

Now it's time to deal with the consistent data entry issue. From our discussion above, it sounds like you would prefer the list box approach since it offers more protection for consistency.

1)Start by creating a new table, named GenreNames; two fields: and ID field (integer, autoincrement), and a GenreName field (text). Close the table window.
2) Go to the Tools menu and choose SQL.
3) Enter the following statement into the top portion of the Execute SQL Statement window:

Code: Select all

alter table "GenreNames" add constraint "uq_GenreName" unique ("GenreName" )

Click the Execute button; the Status section will tell you if the command executed properly.
steps 2&3 create a unique constraint; no two records can have the same entry; this prevents any user from creating a duplicate entry.
4) Enter at least a few of the genres you want to use in this table. (Either create a form to do so or just add them in table view)
5) Open the form you created earlier in design mode (right click on the form in the main window and choose Edit).
6) right click on the Column header for the Genre Name field in the subform and choose Replace With and then Listbox
7) right click again of the same column header and choose Column... (in a non-datasheet format it would be Control)
8) In the Data tab, make sure the Data field is still set correctly
9) Set Type of list contents to SQL
10) In List contents, type the the following:

Code: Select all

SELECT "GenreName","GenreName" FROM "GenreNames"

Make sure that the field name and table name exactly match those you created in your third table. And yes, the GenreName field is mentioned twice here.

I think this will work; I can't quite get it to work in a more complicated database I have. I think that's related to some naming issues. If it doesn't work for you, first eliminate any spaces in table and field names involved in the SQL statement in the listbox properties window. If still doesn't work, you should probably start a new thread asking about how to configure the list box. (Actually, it would be good to search for list box first and see if that question has already been answered.)

Sorry not to be more confident here; I have set up combo boxes before, and I thought the list boxes would be just as easy.

kabing

[jdanniel]

Hi.

I'm not sure I'm ready to dabble with SQL. I really don't want anything in the database that I'm not familiar with, or don't understand. Maybe we can work with this farther down the line. But for the sake of curiosity, I'll try the code you offered, to see how it affects the form.

Right now, though, I think I'm more worried about what's causing the integrity errors whenever I try to delete a record.

Jd