Tables for Cascading Combos

[Navyguy]

Hi Everybody

I am looking for some suggestions on splitting a table up for lookup purposes. I tried the table analyzer but it did not make sense to what I wanted to do.

What I have right now is one table with about 200 records. The field are:

Tbl-Events

EventNumber (PK) Number
Divisions Text
Gender Text
Level Text
AgeGroup Test

Sample date would look something like this:

1 Alpha Male Novice 8 – 9 Years

2 Alpha Female Novice 8 – 9 Years

3 Bravo Male Intermediate 8 – 9 Years

Some other information that may be useful:

There is 6 division levels, 3 Gender options, there are 6 Level Options and 15 age group options.

I am trying to use cascading combo boxes as an entry form so the user action would be something like (I think):

I am in Division “Alpha”, I am a “Novice”, I am in the age group “8 – 9 Years”, and that should leave me with the only option left of being a “Male” or “Female” and depending what I pick I will either get EventNumber “1” or “2”

Is cascading combos the way to go here? Right now I have 1 table with about 200 records, but If I go Cascading Combos, I think I would have many more tables and many more records just to do the combos.

Can somebody point me in the right direction?

As always, thanks for your help.

 

[Len Boorman]

You never split tables to suit combos. You only split tables in a normalisation process. That's my opinion anyway.

Now you could have control tables for the various items. These would not only serve the combo boxes (to a degree) and also control the input of data.

However you could also use validation rules to contril the input of data. Rewally the choice is yours.

Regarding thje combos I would suggest that yopu use the actual records as the source for you combos. You can set the combos row source to the main data table and the field such as Division. Use a DISTINCT set and you have your combo list. Cascading combos treated in the same manner

HTH

Len B

 

[Navyguy]

Hi Len

I was not sure if the normalization rules applied to "Lookup" tables. I guess I am trying to make the inputting of the information as easy as possible and trying to foresee possible problems with the data entry, that is why I was thinking of the cascading combos (limits the possible errors).

I am not sure if I am even approaching this right. The 200 records are simply all lookup information for the various age groups and skill levels. I have all separate tables for entries and registration etc.

In one respect it seems easy enough, but trying to employ the normalization and the possible data entry problems it seems to get a bit tougher.

 

[Navyguy]

Just another question…in my example above I was thinking that the “Event Number” would be the last item to be found. Maybe another aspect would be to know the “Event Number” and have the other fields be completed automatically.

I guess the only problem is that the reports that are required are based on the event number and the other information is not required to be stored, as they should have met the criteria to be entered in that event in the first place (age group, gender, level etc).

I think maybe I am just confusing the issue more then it needs to be. Any thoughts are welcome.

 

[Len Boorman]

Sit back and take a few minutes off

My approach is as follows.

1) Design the database without any consideration of anuthing else but the data. i.e Correct normalisation, PK's etc. All the usual bits.

2) Consider.... Where do I need to control data entry, What are likely to be my search fields. Can I control simply with validation rules i.e. M or F. Are the potential search fields likely to contain unpredictable data or could I make a reasonable stab at say 90% of the entries

As you can see not a black and white area.

In the end I make a decision, people tend to get a control table cos I just might want to include other "People" data later

Things that maybe will be subject to some extension of the database, in my case Parts is a good example

If my data set is small I tend not to go for control tables because it would be only too easy for a search using a control table entry to come up with zilch. In these cases I would tend to use the data table as the source for search combo's so that blank search results do not occur

HTH

Len B

 

[Navyguy]

Hi Len

So I have sat back for awhile now and then dove back into it. I am happy with the data table(s). In fact I think they are quite simple. My hassel is dealing with the Entry Forms so I have the least possbile chances for error.

I have been working on a variety of cascading Combos, using my reference books and I am just not having any luck. I keep running into the same problem of having so many lookup records it will end up being bigger then my data tables!!!

I am sure there must be a simple way but I just can't seem to get my head wraped around it.

There must be a solution...you mentioned parts...this is much the same I think. You make the selection for a part by asking question(s) and you get the part number, Make, Model, Year, Engine Size and then you get a list of parts.

 

[Len Boorman]

Okay lets say that you have a Table called tbl_Main. In this table there is a field called Country and you want to control the entry.

Option 1 is a separate table with all the countries which is the source of a combo. Okay no probs with this option.

Option 2 is to have a combo whos source is a Value List. Needs you to type in all the countries and there may be a limit to the list. (not sure myself. I would only use this option for very short lists M, F or Y and N)
so not really an option

Option 3 is to have a combo who source is the field you will be populating.
So the row source for the combo is
Select Distinct tbl_Main.Country From tbl_Main Order by tbl_Main.Country (ASC);
Now this will give you a list of cCountries that exist in the Table already. Set the limit to list as No and put a label alongside the combo saying Select from list or Type New

Now option 3 is a bit of a halfway house really. With luck most Users will check the list first and even then as they type the combo will attempt to match their entry.
There will be some users who manage to put in every version of U.S.A possible with spaces, dots etc.

If you believe your main data table will be relatively small then maybe Option 2 or 3 will fit thebill. One of the advantages of 3 is that you never have to add countries to a lookup table or value list but its not perfect

len

 

[Navyguy]

?Success At Last?

Well I managed to figure it out. I am not sure how efficient it is but I know it works and the cascading combos work well.

From my initial table of 231 records I ended with 5 tables for a total of 632 records. What I ended up doing was duplicating the data and assigning it a unique number so the cascading would work. So like I said it works but I don’t think it is efficient…or normalized for that matter.

Thanks for all your suggestions and help.

 

[Len Boorman]

Glad I was able to help.

It sounds as if you have created what are effectively control tables. Quite possibly the values in these tables are in fact Unique values and therefore would be Primary Key candidates in their own right.

Quite possible then you would find that these may be linked to your main table values. The main table values acting as Foreign keys.

This is quite an interesting situation really. In the future if needed you will find that you are able to extend the application to include additional data regarding these "control" items very simply indeed. Your database remains fully normalised with data integrity enforced.

Anyway glad you have a solution.

L