concatnate across fields in a record and across records where ...

[H3rk]

could someone tell me how to create a new field that
1.) combines several fields concatnated together within the same record
and then
2.) combines several of the new fields from records where other related fields are the same, until yet another field is different. (Each ingredient is a record) I want a new record for meals differentiated by days.
...sorry, its hard for me to explain...

heres my example since my description leaves something to be desired.

In a query, I have the fields:
MealDate | Start Time | MealID | FoodName | Amount | Measure | Calories | Protein | Fat | Carbs |

Each meal is indicated by a similar number in the MealID field. However Each Ingredient is a seperate record. I want to put all of the Ingredient information (FoodName&Amount&Measure&Calories..etc)for the same meal, In one field. (So that outlook VBA code can pull it into the body of an appointment)So I ultimately have a new record with all the ingredients for the meal (and related info)combined into one field.

In addition to what expresion(s) I would use, I could use some help into where to put this. In another query?

Thanks in advance.

 

[JerryKlmns]

>combines several fields concatnated together within the same record

SELECT MealDate, Start Time, MealID, [FoodName] & " " & [Amount] & " " & [Measure] & " " & [Calories] & " " & [Protein] & " " & [Fat] & " " & [Carbs]
FROM yourTableName;

 

[dhookom]

Use the generic concatenate function found at faq701-4233. You can probably use syntax like:

Concatenate("SELECT FoodName & ' ' & Amount & ' ' & Measure & ' ' & Calories as TheValue FROM aquery WHERE MealID =" & [MealID],Chr(13) & Chr(10))

There is a sample of how to use this function at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[H3rk]

I'm getting errors (prompts asking for values) that lead me to believe The FROM section cant be a query, in this case. So I used the originating tables, with their relationships, and added the concatnate function at the end of the SELECT section, I replaced aquery with a the query that has all of the related information on it.

here, the query I have so far is:

SELECT Nutrition.MealDate, Nutrition.MealID, MealNames.[Meal Name], MealTimes.StartTime, Concatenate("SELECT FoodName & Chr(9) & Amount & ' ' & Measure & Chr(9) & Calories  as TheValue FROM QryIngred WHERE MealID =" & MealNames.[MealID],Chr(13) & Chr(10)) AS Ingredients
FROM (MealNames INNER JOIN MealTimes ON MealNames.MealID = MealTimes.MealID) INNER JOIN Nutrition ON MealNames.MealID = Nutrition.MealID;

QryIngred is:

SELECT Nutrition.MealDate, Nutrition.MealID, MealNames.[Meal Name], MealTimes.StartTime, Nutrition.FoodName, Nutrition.Amount, Nutrition.Measure, Nutrition.Calories, Nutrition.Protein, Nutrition.Fat, Nutrition.Carbs
FROM MealNames INNER JOIN ((WorkScheduleInfo INNER JOIN Nutrition ON WorkScheduleInfo.WorkDate = Nutrition.MealDate) INNER JOIN MealTimes ON WorkScheduleInfo.SchedFactor = MealTimes.SchedID) ON (MealNames.MealID = Nutrition.MealID) AND (MealNames.MealID = MealTimes.MealID)
ORDER BY Nutrition.MealDate, MealTimes.StartTime, Nutrition.FoodName;

When I run the upper query I get seperate records still, but with the desired fields within each record combined.

Please, point out my flaw.
I had to change ---WHERE MealID =" & MealNames.[MealID]--- from [MealID} to MealNames.[MealID] due to Access error message exlpaining I had several sources of MealID, I've tried all of the sources, and even QryIngred.


In the example in

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

the tables and relationships were neat, concise and simple. I figure, maybe my messy source is the problem.

 

[dhookom]

Are you suggesting that the concatenate function only returns one record's values? Did you expand the view of the query to see multiple rows since each record from QryIngred will be on its own line?

A simple method for checking the function is to open the immediate window (Press Ctrl+G) and enter:

? Concatenate("SELECT FoodName & Chr(9) & Amount & ' ' & Measure & Chr(9) & Calories  as TheValue FROM QryIngred WHERE MealID =[red]X[/red]" ,Chr(13) & Chr(10))

Replace the "[red]X[/red]" with an actual MealID value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[H3rk]

dhookom, that was simple (the second part) I probably would have thought to expand the view if I didn't see so many repeat entries in the query. That's some other problem I'm sure, thanks for your time and effort.

 

[H3rk]

ok, in the qryIngred, where the records are separate, I currently have 21 records.
The concatnate function works, It combined into one feild what I wanted, but Did so repeatedly. with now 21 records in the query. What causes that, or is that an affect I have to counteract.

 

[dhookom]

Your main query with the Concatenate should return only one record per MealID.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[H3rk]

no I get a number of records per MealID I got before, only they all look the same now.

 

[H3rk]

I put the .mdb file up, I am going to start again from scratch, being that I didn't spend too much time putting it together initially. The linked file is just a consecutive 1.)list of dates & 2.)a number 0,1,2,3 that indicates whether I work days or nights. That information changes as my boss changes the originating schedule, and I want the meal times that automatically go into outlook to be based on that. Nutrition table, I have to import from a renamed .xml file for someone elses program that didnt have the functionality I'm trying to create. Which brings up another question which I haven't looked into yet; Are there options for automatically (script?) copying a .xml file that has been given a different file extension? What are the limitations on linking to xml files. It looks like all I can do is import it.
Anyways, heres the link:

http://www.geocities.com/c_a_rile/MyFiles/MealSource.mdb

 

[dhookom]

The mdb file doesn't do much good without the xls file.

I haven't worked with XML.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[H3rk]

http://www.geocities.com/c_a_rile/MyFiles/Cal_transposer.xls

You'll have to rename it replacing the _ with a space.

 

[dhookom]

The file you provided doesn't have the range or worksheet that is expected.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[H3rk]

I updated Mealsource.mdb click the link above. I removed the external references, replacing them with imported files. I got it to work. If you select, in the query's properties, Unique Entries - Yes, it removes the duplicates. My final version will be more clear, clean, and concise hopefully. Thanks for your help. Now if I can figure out the ImportXML method, and have it automatically update from xml files.

 

[dhookom]

I would remove the Nutrition table from your Output query and use SQL that would need to set unique records on:

SELECT WorkScheduleInfo.WorkDate, DateAdd("d",MealTimes![Date Correction Factor],WorkScheduleInfo!WorkDate) AS [Date], MealTimes.MealID, MealNames.[Meal Name], MealTimes.StartTime, Concatenate("SELECT FoodName & Chr(9) & Amount & ' ' & Measure & Chr(9) & Calories &Chr(9)&Chr(9)&Protein& ' ' &Fat& ' ' &Carbs  as TheValue FROM QryIngredients WHERE MealID =" & MealNames.MealID,Chr(13) & Chr(10)) AS Ingredients
FROM MealNames INNER JOIN (MealTimes INNER JOIN WorkScheduleInfo ON MealTimes.SchedID = WorkScheduleInfo.ShiftID) ON MealNames.MealID = MealTimes.MealID
ORDER BY WorkScheduleInfo.WorkDate, MealTimes.MealID, MealTimes.StartTime;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]