Listing Query

L

lilanngel

Hi there,

Let's say I have a database that looks something like this

Apple Blueberry Crust Sugar Cinnamon
Apple Pie Yes No Yes Yes Yes
Blueberry Pie No Yes Yes Yes No


I want a column that says "Ingredients Needed" and it would list all the
ingredients that I need if it says "Yes."

So for "Apple Pie," it should list "Apple, Crust, Sugar Cinnamon" and for
"Blueberry Pie," it should list "Blueberry, Crust, Sugar"

How can I do this?

Thanks!
 
J

John W. Vinson

Hi there,

Let's say I have a database that looks something like this

Apple Blueberry Crust Sugar Cinnamon
Apple Pie Yes No Yes Yes Yes
Blueberry Pie No Yes Yes Yes No


I want a column that says "Ingredients Needed" and it would list all the
ingredients that I need if it says "Yes."

So for "Apple Pie," it should list "Apple, Crust, Sugar Cinnamon" and for
"Blueberry Pie," it should list "Blueberry, Crust, Sugar"

How can I do this?

Thanks!
Click to expand...

Your table structure IS WRONG. This is a good design for a spreadsheet but
it's simply not appropriate for a relational database. Suppose you wanted to
add Rhubarb? You need to redesign your table, add a new field, rewrite all
your queries, redesign your forms... ouch!!!

In a normalized database you would resolve this many to many relationship by
using three tables:

Desserts
DessertID <autonumber primary key>
DessertName <e.g. "Apple Pie">

Ingredients
IngredientID
Ingredient <e.g. "Apples">

Recipes
DessertID <long integer link to Desserts>
IngredientID <long integer link to Ingredients>
<maybe other info about this ingredient in this recipe>

You can then create queries to combine the information from these tables.
 
L

lilanngel

John said:
Your table structure IS WRONG. This is a good design for a spreadsheet but
it's simply not appropriate for a relational database. Suppose you wanted to
add Rhubarb? You need to redesign your table, add a new field, rewrite all
your queries, redesign your forms... ouch!!!

In a normalized database you would resolve this many to many relationship by
using three tables:

Desserts
DessertID <autonumber primary key>
DessertName <e.g. "Apple Pie">

Ingredients
IngredientID
Ingredient <e.g. "Apples">

Recipes
DessertID <long integer link to Desserts>
IngredientID <long integer link to Ingredients>
<maybe other info about this ingredient in this recipe>

You can then create queries to combine the information from these tables.
Click to expand...

Hi John,

I understand the first two tables, but regarding the third... how would it
look like, exactly?

Would it look like this:
Dessert ID Ingredient ID
1 1
1 3
1 4
1 5
2 2
2 3
2 4


And let's say it should look like this - does it mean that I have to redo the
entire table? Let's say that this entire table is absolutely huge (it was
imported from a spreadsheet, which was filled in by various users). Is there
another way to get what I want (a list of ingredients), assuming that there
is a huge amount of data that would take a long time to re-do?
 
J

John W. Vinson

I understand the first two tables, but regarding the third... how would it
look like, exactly?

Would it look like this:
Dessert ID Ingredient ID
1 1
1 3
1 4
1 5
2 2
2 3
2 4
Exactly.


And let's say it should look like this - does it mean that I have to redo the
entire table? Let's say that this entire table is absolutely huge (it was
imported from a spreadsheet, which was filled in by various users). Is there
another way to get what I want (a list of ingredients), assuming that there
is a huge amount of data that would take a long time to re-do?
Click to expand...

You can migrate the data from the spreadsheet into the normalized structure
using a "Normalizing Union Query". It would help quite a bit if you could post
the actual table and fieldnames of your spreadsheet file.

The logic would be that you would create the three new tables and run Append
queries to migrate the data from your spreadsheet; first you'ld have a simple
SELECT DISTINCT query to move each row of the spreadsheet into a single record
in the Desserts table, not including any ingredient information. Then you'ld
need to manually create the Ingredients table (since its data does not exist
in any form other than fieldnames, which aren't very accessible). Finally
you'ld create a UNION query extracting each column individually. This is a bit
tedious but not at all hard, given the actual fieldnames.
 
Top