Doubt about "table" with "1-many" relationship

[capsula4]

Hi!

I have one table called "TRANSACTION" with:
[id transaction] [product] [product type]

And I have a 1-many relationship:
[id product] [product]

[id product] [id product_type] [product_type]

The doubt I have is how can I make the main table called TRANSACTION, once you choose a product, display on product type the types of that specific product and not all the types of all the products.

I have attached a db so as to make it clearer.

Thanks in advance!
Luis

 

[Guus2005]

remove product and product type from your transaction table and put id product in their place. now you have a normalised database.

 

[capsula4]

Thank for the tip Guus! But now I have two doubs!

I have make Product and Product Type a combobox in Transaction table, which takes the information from products table and product types table.

The problems are:

1. The combobox displays the ID's, not the text.
2. Once I choose a specific product, it still shows all the available product types (not just those related with the chosen product).

 

[JohnD]

Well, I was searching the database with your exact question

1. The combobox displays the ID's, not the text.

...I've yet to find anything but if I do, I will post the link. I just hope to find it before someone posts the answer due to available time.

For me, I have the following table:

Vehicle
(PK) Vehicle_ID
(FK) Make_ID
(FK) Trim_ID
(FK) Body_ID
Year
Make

then the other tables are laid out like this:

Make
(PK) Make_ID
Make

When I connect all of them, it displays just the ID, not the text, such as Ford or GMC.... You get the idea

back to searching

 

[Mike375]

When you make your Combo you can select the fields you wanted displayed in the drop down list.

You can determine what is listed in the combo by having a query as the basis of the combo and the query selects the records based on the criteria you have supplied.

Unless the data base is to be extremely simple, tables are not used, that is, you don't see them. You want everything done on forms. Forms are not just good for arranging the display or presentation of the fields but they also allow for code or macros to be attached to labels, buttons, fields and the form itself.

Think of tables as being like the foundations of a house.

 

[capsula4]

Thank you Mike and John for your explanation/help!

 

[Guus2005]

Glad to be of help

 

[neileg]

Table level lookups cause all sorts of problems but the one you have is the most common. I avoid them completely.

 

[capsula4]

Table level lookups cause all sorts of problems but the one you have is the most common. I avoid them completely.

What do you do instead?? Any suggestion for a newbie (me)?

 

[neileg]

I use a combo box on a form that is populated by either a query or (less likely) a table and bound to the field I need to store the value in.