Combining multiple fields to one field

[klallen810]

Hello,
*
I am currently designing a database to keep track of my nutrition and exercise.* Included in the nutrition portion I am adding recipe’s and planning meals a week ahead.* The purpose of this is to put the list of ingredients needed over the next week into a shopping list report.* I did not design it the best way to make this happen.* I currently have the ingredients from the recipe going into one large text box.* I hit ctrl-enter-down arrow to get to the next row, so all of the ingredients for the recipe are in one field.* When I put this information into a report it separates the ingredients by recipe, so if one ingredient, say an apple, is needed for two recipe’s it is listed not only twice, but not in order next to each other.* I am sure with the way the database is designed I cannot add the ingredients together to make a total, but if at all possible I would like to run this so it puts all of the ingredients in one field.* That way when I alphabetize it there may be duplicate ingredients, but at least they will be next to each other so I don’t have to scan over the whole list over and over.* Does anyone have any ideas on how I would accomplish this?* If not what will I have to do to redesign the db so I can accomplish this?* Thanks in advance!

 

[mdlueck]

Perhaps simply switch the query from a SELECT to SELECT DISTINCT so that only unique items are returned.

 

[klallen810]

Thank you for your response but it did not work. I tried to use a concatenate stmnt but I could not get that to work either

 

[mdlueck]

Thank you for your response but it did not work.

You are still receiving multiple instances of a particular ingredient with SELECT DISTINCT?

 

[klallen810]

No, but I need different instances to see how much of the particular ingredient I need. I just need them in alphabetical order, not alphabetical by recipe but all of them listed in alphabetical order

 

[mdlueck]

Precisely... you need the QtyPer of each DISTINCT item Sum'ed up and only the DISTINCT item names listed on the report.

You will need a subquery to obtain the Sum per DISTINCT item.

This looks like good material to refer to:
"SQL GROUP BY techniques"
http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

 

[klallen810]

Thanks!* That blog will certainly help.* I do believe I will have to re-format the db a little bit though.* I currently have the ingredients in a recipe table in a field listed as:
Apple, 1 whole
Vinegar, 1 cup
Olive Oil, 1 tbsp
Etc.* I believe I will need to create an ingredients table and have them listed as the ingredient, measuring unit (vinegar, cup) and have a section on the form where you select from this list and then pick the amount of the measuring unit to use.* This way I can add them together for the shopping list.* I am fairly new to access, do you believe this is the best solution?* If you have any other ideas please let me know.* Thanks!

 

[mdlueck]

Correct... borrowing terminology for an application I have developed...

Products <--> ProductPartLink <--> Parts

So that multiple Products and link to the same shared Parts as other Products.

The Link table needs cols ID, ProductID, PartID where the ID is an autogen field for that table, and the other two ID's refer to the ID's in the respective other tables.

Should be the same overall schema in your case I believe.

Oh, and I almost forgot... the ProductPartLink table is where the QtyPer goes.