Comboboxes go where?

[PaulCW]

I'm trying to build a database where one can quickly make notes of observable events. One would first name the event. Observing it, one would point and click through three cascading comboboxes (from general Questions to more specific Areas to most specific Categories). From there one would enter date, degree of significance (1-10) and finally a memo.



Questions would likely have 3-4 options; Areas up to 20 or so split between the Questions and 5-30 Categories per Area.

I've figured out cascading comboxes but not how to get them to enter data that stays. I can set up a separate form with cascading comboboxes and drill down through them, but can't figure out how to integrate it with my Main form.

Haven't yet found the answer here, great resource that it is, or elsewhere. Any help very much appreciated,
Paul

[alansidman]

I would start here.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

I am confused by your tables. They seem to have duplicative data. This does not make sense to me. You need to normalize your data base and set up relationships. Read the link above -- it will give you a good basic starting point.

[PaulCW]

Thanks for your response and my apologies for lack of clarity. Yes, I've studied pages like the one you linked to.

As I have understood the first rule of normalization, it's one fact per table:
Tables
Contacts: Name of event
Questions: Top level classification. Ex: Family or Sport
Areas: Second level Ex: Genus or Football
Category: Third level. Ex: Species or Offense

As I see it, the three lists are related but not synonymous. Am I wrong? I can see Questions having 3-5 records, Areas with 15-20 and Category with 100-150.

QAC: Brings Questions, Area and Category together. An early attempt for comboboxes. I tried running comboxes off of a query and coudn't get it to work. As I remember it, all I would get back were numbers, not text. Left in case it was needed.

Info: All the unique observation data for a given Question/Area/Category event.

There are some fields in the Question, Area, Category tables that are probably not needed, like Quest_Sel. That was for a separate column with some sort of identifier to be used in selecting from the Question field. They're from earlier attempts and left in in case they were needed.

As far as relationships go... I'm assuming you mean reset relationships after changing the tables. As it stands, the database works, aside from the comboboxes.

I hope this makes what I'm trying to accomplish more clear. Once the tables normalization issue has been cleared up, where would the comboboxes go?

Your advice is most welcome,
Paul

[goodguy]

I have gone over your database and apart from what Alan has said about normalization, I don't see anything wrong with it. I also could not find how cascading comboboxes would help you, as your lookup tables are not tied to each other (yet).

[PaulCW]

OK, the db has been modified. I've included a form that shows what I want to happen. The combo boxes allow me to drill down from a given sport to unique aspects about that sport to particular elements of that sport (hence Sport, Area, Category). From there the Info table holds data as to date, the degree of impact of what was observed and a memo field for details.

Sport, Area and Category have their own tables. Note that the Area table could easily expand to 20-30 records. Category could easily go out to 150+. Hence the desire for drilling via the cascading comboboxes.

I've been able to generate cascading comboboxes by building a table combining Sport Area and Category. However, I think it got flagged for being redundant. I haven't been able to get cascading comboboxes to work right off of a query yet. And even when I got the cascading comboboxes to work, I wasn't able to get the information to stay with the event record.

So, is the recommended solution to build the cascading comboboxes straight off of tables, or a query?

Second, how do I tie the results to the event record?

Thanks in advance,
Paul

[goodguy]

I checked your new db and found that instead of the Info table holding foreign keys to the lookup tables, you have placed the Info field as a foreign key in each lookup table. A foreign key CANNOT be on the 1 side of the relationship; by its very nature the 1-side is the primary key.
I fixed your db design by creating text lookup fields in Info table for each lookup table. Since your lookup tables consist of single columns, I simplified them by making the text column as the primary key and deleting the non-essential fields. I have linked the lookup fields in Info table to the respective tables and set the display control for each to combo box.
I have also fixed up the relationships for all tables. Since each table has just one relationship (with Info table), the question of cascading comboboxes does not arise as none of the lookups is dependent on any other. I have created a form based on Info table in the attached file.

[PaulCW]

Wow. From what I'm seeing, way impressive.

I should still like a cascading combobox, at least for Categories. Why? Having to remember 150+ categories to start typing them is too much for most folks, and scrolling through a 150+ list will take too long for field use. If the Category displayed hinges on what Area has been selected, the max scrolling would be roughty 15.

I look forward to your next amazing answer. Thank you,
Paul

[alansidman]

Here's a tutorial on cascading combo boxes.

http://www.fontstuff.com/access/acctut10.htm

Alan

[PaulCW]

Thank you Alan. Yes, I've seen this.

From that page:

The plan is to have the contents of the cboCity list change to reflect the user's choice from cboCountry. This will be achieved by programmatically defining the Row Source property of cboCity using the After Update event of cboCountry. Here's the code that does the job:
PrivateSub cboCountry_AfterUpdate()
On Error Resume NextSelect
Case cboCountry.Value
Case "France" cboCity.RowSource = "tblFrance"
Case "United Kingdom" cboCity.RowSource = "tblUnitedKingdom"
Case "United States" cboCity.RowSource = "tblUnitedStates"
End SelectEnd Sub

The tutorial is for Access 97-2000. Do things run differently for Access 2010?

As I understand this, I would need to do a "Case" for the 15 or so Areas in my database. Any change of the Areas would mean going back in and changing the SQL. When I had my earlier versions of cascading comboboxes running, any addition to a table was reflected in the combobox. How would that work here?

I didn't see my final question addressed on the page you linked to: My comboboxes did indeed cascade, but the results weren't tied to a record. Thoughts as to what I was doing wrong?

Thanks again, Alan,
Paul

[alansidman]

Thoughts as to what I was doing wrong?

I'm guessing that your combo boxes (controls on your form) were not bound to your table. They were unbound and interacted only with the lookups.

I have used maintenance screens that were visible only to admins to add or delete the values in the look up tables. This involved building a form that opened the table in datasheet view in the form. It allowed for simple and quick updates of the lookup tables.

As to how all this relates to 2010, the background is the same. Visually, is the big difference in the forms for this step of the project.

I hope this clears up some of the issues for you.

[goodguy]

Assuming a 1-M relationship between Area and Categories, add Area as a foreign key to Categories table. Then filter the category combobox in the After_Update event of the Area combobox as shown in Alan's link.
P.S. If the relationship is M-M, then you need a junction table for AreaCategories which has both foreign keys.

[PaulCW]

Thanks for the pointers and insight. Very much appreciated.

[goodguy]

You are welcome, pal!