Architectural question

[davidbodhi]

I have a database with several tables that define types of products. Here are a few examples:

Classes
.....ClassID
.....Name
.....Date
.....Time
.....Location
.....etc

Products
.....ProductID
.....Name
.....Manufacturer
.....Price
.....etc

Gift Certificates
.....GiftCertificateID
.....Title (what it can be applied to)
.....Amount
.....Purchased by
.....etc

I also have a form showing transactions that includes purchases. Any of these kinds of things can be purchased separately or together.

I'd LIKE the receptionist at this place to have a combo box to pick from a list of all these things (the name of the item, rather than the ID), but have no clue what the best way is of doing so.

Putting them all in one table will leave me with a million empty fields to deal with IsNull's for, since the data is so different for each kind, yet a query won't let me combine them, since they have no direct relationship to one another.

Can I have a suggestion or three as to intelligent ways of doing this?

 

[davidbodhi]

I misspoke - I can create a query using the names of each type of item, but they come in one row, when I want them all in one column and, when selected, the price of each should populate a text field.

I can get that to work, but, so far, only when using one table, since I haven't gotten all the items into one pick list, yet.

 

[davidbodhi]

Thank you, Pat -

I'm afraid I'm not quite clear enough. I'm bright, but very untrained.

In your example, am I creating two new tables, or is tblItem one of my existing tables?

Is tblItemType, therefor the parent table you mention?

If so, I then have my combo box looking at tblItemType and pulling info from the various tblItem's to populate my text field(s)?

Did I get that right? I have the feeling I didn't.

Or, did I create two new tables, relating my existing tables to tblItem and, through it, to tblItemType?

If that's what I'm doing, I can do so, but I don't understand why that does good things for me.

Thanks for further clarification.

Also, can you suggest good learning sources for planning databases, structurally, other than the classroom of experience?

 

[davidbodhi]

Okay, thanks... I get your drift now.