Combining Multiple Query Results Into One 1

[CharlieT302]

Hi Everyone,

I have a table that lists Item #'s in separate fields (fyi: it has to stay this way).

For Example:
Date Item1 Item2 Item3
1/2/2012 502 201 705
1/3/2012 301 222 725

As you can see, each date contains three Items for a single date. I use a query to attach this table to primary table to provide additional info regarding each item. I have a separate query for each item. See below (Temperature, Time are from other table)

Query 1
Date Item1 Temperature Time
1/2/2012 502 350 degrees 1 hour

Query2
Date Item2 Temperature Time
1/2/2012 201 275 degrees 1.5 hour

Query3
Date Item3 Temperature Time
1/2/2012 201 400 degrees .5 hour

The additional tables are not the issue. What I need is a way to combine each query result into a single result, so that all items displays on separate lines even though they are from the same date (see below). In other words "stack" the results of each query onto the last. I do not want to use a Make Table query as that would not be efficient. Is there a query solution?

Date Item Temperature Time
1/2/2012 502 350 degrees 1 hour
1/2/2012 201 275 degrees 1.5 hour
1/2/2012 201 400 degrees .5 hour
1/3/2012 301 325 degrees 1.5 hours
1/3/2012 222 375 degrees 1 hour
1/3/2012 725 300 degrees 2 hours

Ideas?

 

[SkipVought]

Append? Is that what you're looking for?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PHV]

Why not simply UNION your three queries ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

Hi,

I do not want an Append. All the info is coming from the same table now. I do not want to have to create another table, if I can avoid it.

Just want one the results of each query to display in total within the same data set.

 

[PHV]

Why did you not post the relevant SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

PHV,

I just tried a Union query, but am experiencing an error. The SQL code below is attempting to connect two of the five tables that need connecting. I want to get these working before I attempt to connect the rest.

Error Code:
Syntax error in query expression [Adult Menu],Item-1_Num = Heat_and_Portion_Control_Menu.[Menu_Item_Number]
Select [Adult Menu].I'

SELECT [Adult Menu].ID, [Adult Menu].[Serve Date], [Adult Menu].Item_1_Num, Heat_and_Portion_Control_Menu.order, Heat_and_Portion_Control_Menu.[Menu Description], Heat_and_Portion_Control_Menu.[Food Type], Heat_and_Portion_Control_Prep_Codes.[Portion Serving], Heat_and_Portion_Control_Prep_Codes.Utensil, Heat_and_Portion_Control_Prep_Codes.[Heat Convection], Heat_and_Portion_Control_Prep_Codes.[Heat Conventional], Heat_and_Portion_Control_Menu.[INGREDIENT 1], Heat_and_Portion_Control_Menu.[INGREDIENT 2], Heat_and_Portion_Control_Menu.[INGREDIENT 3], Heat_and_Portion_Control_Menu.[INGREDIENT 5], Heat_and_Portion_Control_Menu.[INGREDIENT 4], Heat_and_Portion_Control_Menu.[INGREDIENT 6], Heat_and_Portion_Control_Menu.[INGREDIENT 7], Heat_and_Portion_Control_Menu.[INGREDIENT 8], Heat_and_Portion_Control_Menu.[INGREDIENT 9], Heat_and_Portion_Control_Menu.[INGREDIENT 10], Heat_and_Portion_Control_Menu.[INGREDIENT 11], Heat_and_Portion_Control_Menu.[INGREDIENT 12], Heat_and_Portion_Control_Menu.[Item cost], Heat_and_Portion_Control_Menu.[Labor cost], [item cost]+[labor cost]+[add items] AS [Total item cost], Heat_and_Portion_Control_Menu.[Add items]
FROM [Adult Menu] INNER JOIN (Heat_and_Portion_Control_Menu INNER JOIN Heat_and_Portion_Control_Prep_Codes ON Heat_and_Portion_Control_Menu.[Prep Code] = Heat_and_Portion_Control_Prep_Codes.[Prep Code]) ON [Adult Menu].Item_1_Num = Heat_and_Portion_Control_Menu.[Menu Item Number]
SELECT [Adult Menu].ID, [Adult Menu].[Serve Date], Heat_and_Portion_Control_Menu.order, [Adult Menu].Item_2_Num, Heat_and_Portion_Control_Menu.[Menu Description], Heat_and_Portion_Control_Menu.[Food Type], Heat_and_Portion_Control_Prep_Codes.[Portion Serving], Heat_and_Portion_Control_Prep_Codes.Utensil, Heat_and_Portion_Control_Prep_Codes.[Heat Convection], Heat_and_Portion_Control_Prep_Codes.[Heat Conventional], Heat_and_Portion_Control_Menu.[INGREDIENT 1], Heat_and_Portion_Control_Menu.[INGREDIENT 2], Heat_and_Portion_Control_Menu.[INGREDIENT 3], Heat_and_Portion_Control_Menu.[INGREDIENT 5], Heat_and_Portion_Control_Menu.[INGREDIENT 4], Heat_and_Portion_Control_Menu.[INGREDIENT 6], Heat_and_Portion_Control_Menu.[INGREDIENT 7], Heat_and_Portion_Control_Menu.[INGREDIENT 8], Heat_and_Portion_Control_Menu.[INGREDIENT 9], Heat_and_Portion_Control_Menu.[INGREDIENT 10], Heat_and_Portion_Control_Menu.[INGREDIENT 11], Heat_and_Portion_Control_Menu.[INGREDIENT 12], Heat_and_Portion_Control_Menu.[Item cost], Heat_and_Portion_Control_Menu.[Labor cost], [item cost]+[labor cost]+[add items] AS [Total item cost], Heat_and_Portion_Control_Menu.[Add items]
FROM [Adult Menu] INNER JOIN (Heat_and_Portion_Control_Menu INNER JOIN Heat_and_Portion_Control_Prep_Codes ON Heat_and_Portion_Control_Menu.[Prep Code] = Heat_and_Portion_Control_Prep_Codes.[Prep Code]) ON [Adult Menu].Item_2_Num = Heat_and_Portion_Control_Menu.[Menu Item Number];

 

[PHV]

Do a Google search about UNION QUERY

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Without looking at everything, it is apparent you are missing the UNION ALL phrase

SELECT [Adult Menu].ID, [Adult Menu].[Serve Date], [Adult Menu].Item_1_Num, Heat_and_Portion_Control_Menu.order, 
Heat_and_Portion_Control_Menu.[Menu Description], Heat_and_Portion_Control_Menu.[Food Type], Heat_and_Portion_Control_Prep_Codes.[Portion Serving], 
Heat_and_Portion_Control_Prep_Codes.Utensil, Heat_and_Portion_Control_Prep_Codes.[Heat Convection], Heat_and_Portion_Control_Prep_Codes.[Heat Conventional], 
Heat_and_Portion_Control_Menu.[INGREDIENT 1], Heat_and_Portion_Control_Menu.[INGREDIENT 2], Heat_and_Portion_Control_Menu.[INGREDIENT 3],
Heat_and_Portion_Control_Menu.[INGREDIENT 5], Heat_and_Portion_Control_Menu.[INGREDIENT 4], Heat_and_Portion_Control_Menu.[INGREDIENT 6], 
Heat_and_Portion_Control_Menu.[INGREDIENT 7], Heat_and_Portion_Control_Menu.[INGREDIENT 8], Heat_and_Portion_Control_Menu.[INGREDIENT 9], 
Heat_and_Portion_Control_Menu.[INGREDIENT 10], Heat_and_Portion_Control_Menu.[INGREDIENT 11], Heat_and_Portion_Control_Menu.[INGREDIENT 12], 
Heat_and_Portion_Control_Menu.[Item cost], Heat_and_Portion_Control_Menu.[Labor cost], [item cost]+[labor cost]+[add items] AS [Total item cost], 
Heat_and_Portion_Control_Menu.[Add items]
 FROM [Adult Menu] INNER JOIN (Heat_and_Portion_Control_Menu INNER JOIN Heat_and_Portion_Control_Prep_Codes 
 ON Heat_and_Portion_Control_Menu.[Prep Code] = Heat_and_Portion_Control_Prep_Codes.[Prep Code])
 ON [Adult Menu].Item_1_Num = Heat_and_Portion_Control_Menu.[Menu Item Number]
[COLOR=#A40000][b]UNION ALL[/b][/color]
SELECT [Adult Menu].ID, [Adult Menu].[Serve Date], Heat_and_Portion_Control_Menu.order, [Adult Menu].Item_2_Num, Heat_and_Portion_Control_Menu.[Menu Description], 
Heat_and_Portion_Control_Menu.[Food Type], Heat_and_Portion_Control_Prep_Codes.[Portion Serving], Heat_and_Portion_Control_Prep_Codes.Utensil, 
Heat_and_Portion_Control_Prep_Codes.[Heat Convection], Heat_and_Portion_Control_Prep_Codes.[Heat Conventional], Heat_and_Portion_Control_Menu.[INGREDIENT 1], 
Heat_and_Portion_Control_Menu.[INGREDIENT 2], Heat_and_Portion_Control_Menu.[INGREDIENT 3], Heat_and_Portion_Control_Menu.[INGREDIENT 5], 
Heat_and_Portion_Control_Menu.[INGREDIENT 4], Heat_and_Portion_Control_Menu.[INGREDIENT 6], Heat_and_Portion_Control_Menu.[INGREDIENT 7],
Heat_and_Portion_Control_Menu.[INGREDIENT 8], Heat_and_Portion_Control_Menu.[INGREDIENT 9], Heat_and_Portion_Control_Menu.[INGREDIENT 10], 
Heat_and_Portion_Control_Menu.[INGREDIENT 11],Heat_and_Portion_Control_Menu.[INGREDIENT 12], Heat_and_Portion_Control_Menu.[Item cost], 
Heat_and_Portion_Control_Menu.[Labor cost], [item cost]+[labor cost]+[add items] AS [Total item cost], Heat_and_Portion_Control_Menu.[Add items]
FROM [Adult Menu] INNER JOIN (Heat_and_Portion_Control_Menu INNER JOIN Heat_and_Portion_Control_Prep_Codes 
 ON Heat_and_Portion_Control_Menu.[Prep Code] = Heat_and_Portion_Control_Prep_Codes.[Prep Code])
 ON [Adult Menu].Item_2_Num = Heat_and_Portion_Control_Menu.[Menu Item Number];

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Sorry PH. I should have suggested the same as well as a search on "Normalization". Any time I see repeating columns, it raises a red flag regarding the table structures.

Duane
Hook'D on Access
MS Access MVP

 

[MattGreer]

Was about to say the same, dhookom. Star for you!

Thanks!!


Matt

 

[CharlieT302]

I appreciate the advice. Understand, this is a database that we inherited and are stuck with. The organization will not allow it to be recreated; although that would be ideal. Naming conventions of objects and controls are not following standardized norms and there is a certain amount of redundancy, but can be worked around.

I am not tied to a Union query or any other particular method. Just looking for a method of combining query results with the least amount of fuss.

I have tried the Union query with and without the Union All line. Am getting errors either way. Because of errors in naming conventions (ex: using keywords as names), I am trying to track down each error as it comes. Was sort of hoping for an alternative. If not, I can keep working on this.

Thanks

 

[CharlieT302]

Problems are solved. Incorrectly named fields in underlying tables was causing the errors. The Union query seems to be working correctly. Appreciate the input.