Getting info from subtable? (1 Viewer)

[cGREGgo]

Ok, I have a database with 11,000 mixed drinks & their ingreidents. One table has the drinks listed, 11000 of them, and another table has the ingreidents, 1400 of those.

When I click on the plus sign on the drinks table, it opens a smaller window, like a sub database, and the fields there just show the id number for the ingreident, not the ingreident itself.

How can I get the queries to display the drinks in one column, with the names of the ingriedents in the 2nd column? I'm slowly working on a data access page. I understand the basic functions of Access, but this is way over my head. PLEASE HELP!

 

[pdx_man]

You need a third table. Let's call it Recipes. This will relate the Drinks to the Ingredients. So, Drink #247, a Harvey Wallbanger, will have ingredients #8, #41, #365 or Vodka, Orange Juice and that yucky, yellow, syrupy stuff (Galliano). Yeck!

You need this because you have a many-to-many relationship between Drinks and Ingredients. So, you create what is called a Foreign Key table which properly relates the information from one table, with the other.

 

[cGREGgo]

You make it sound so easy! hehe. I'll give it a try and let ya know....

 

[cGREGgo]

ARGH! Still trying to figure it out....On the drinks table, the subdata sheet has the ingreident id's, but not the names theirselves.... I'm trying to create that table you said, cant find anything anywhere about a foreign key table. So I just make the drink the primary key. No luck getting the data transfered from the subdatasheet though.

I did manage to get a querry to display all the drinks, with the ingreident names, however, the ingreidents werent combined into one field, so if a drink has 4 different ingreidents, it had 4 different records, each with a different ingreident, instead of combining them into one....

NOT MUCH HAIR LEFT! PULLING IT ALL OUT!

 

[pdx_man]

You need to create the third table (Tbl_Recipes) with, let's say, 3 fields:

FK_Drink_ID
FK_Ingrd_ID
FK_Amount

In the Tbl_Drinks you would have:
Drink_ID
Drink_Name
Drink_Glass

In the Tbl_Ingredients you would have:
Ingrd_ID
Ingrd_Name

So then, how does this all work? Let's add some data.

Tbl_Drinks
1 Rum and Coke Rocks
2 ScrewDriver Collins
3 Harvey Wallbanger Rocks
:

Tbl_Ingredients
1 Vodka
2 Rum
3 Tequilla
4 Gin
5 Coke
6 7-Up
7 Orange Juice
8 Cranberry Juice
9 Triple Sec
10 Grand Marnier
11 Galliano
12 Frangellico
13 Orange Slice
14 Lemon Slice
15 Lemon Rind
16 Lime
17 Cherry
:

So then, the all important Tbl_Recipes

1 2 1.25 Ounces
1 5 3 Ounces
2 1 1.25 Ounces
2 7 5 Ounces
2 13 Garnish
3 1 1 Ounce
3 12 0.5 Ounce
3 7 3 Ounces
3 13 Garnish

Now, you must define the relationships in the Relationships window. A One-to-Many relationship between Tbl_Drinks and Tbl_Recipes via Drink_ID and FK_Drink_ID. And another One-to-Many relationship between Tbl_Ingredients and Tbl_Recipes via Ingrd_ID and FK_Ingrd_ID. This is how we get the Many-to-Many type of relationship.

Add these tables to a query and select the fields you want. Play with it, see what happens. It's all an adventure.


*** Fun Hint *** Create a report Grouped by the Drink Name.