Concatenate 3 queries to 1

[brucey54]

Hi folks, need some help with queries & reports, I have 3 queries;

• QryMorning_label
• QryAfternoon_label
• QryEvening_label

And then have 3 reports;

• Morning_Report_labels
• Afternnon_Report_labels
• Evening_Reoprt_labels

I need to print all the reports as 1 report to save on labels i.e. when morning report is finished printing.

I would like to use the remaining labels on the morning labels sheet instead of the printer starting on a new label sheet for the Afternoon labels.

Each label sheet contains 21 labels.

Each label contains the following data;

• Meal period i.e. Morning, Afternoon, Evening
• Client name
• Date
• Location

I’m thinking I need to create a temp table place all the meal periods into one column, then 1 query to feed 1 report?

 

[namliam]

Assuming your queries have the same columns you can use a union

Select * from QryMorning_label
Union
Select * from QryAfternoon_label
Union
Select * from QryEvening_label

And base your report on that union query

 

[plog]

No, no no. You need one simple query for this. Instead of having Morning/Afternoon/Evening in the name of the query, you bring it into a column in the query.

You will also need either a conditional statement to order the meal periods correctly (if [Meal Period]='Morning', 1; if [Meal Period]='Afternoon', 2;, etc.). Then use that query as the source of your report.

 

[brucey54]

Hi Plog, I understand this part
(if [Meal Period]='Morning', 1; if [Meal Period]='Afternoon', 2;, etc.). Then use that query as the source of your report.
But how do you output 1 as Morning meal, 2 as Afternoon meal, 3 as Evening meal on the report side?

 

[plog]

But how do you output 1 as Morning meal, 2 as Afternoon meal, 3 as Evening meal on the report side?

You use the value in the [Meal Period] field.

 

[brucey54]

if statement producing -1 for morning labels, what have I done wrong, it’s been a while since I’ve used a if statement!

Snack: IIf([Qry_07_MorningLabelsCusLabel]![MorningSnack],1,0) And IIf([Qry_08_AfternoonLabelsCusLabel]![AfternoonSnack],2,0)

 

[brucey54]

Also tried the following, giving me a -1


Snack: IIf([Qry_07_MorningLabelsCusLabel]![MorningSnack],"MorningSnack") And IIf([Qry_08_AfternoonLabelsCusLabel]![AfternoonSnack],"AfternoonSnack") And IIf([Qry_09_EveningLabelsCusLabel]![EveningSnack],"EveningSnack")

 

[plog]

Again, I wouldn't build this on top of your poorly structured existing queries. Slash and burn. Start from the beginning.


If you want help going from your tables to a report, post sample data from your tables and then what data you expect as a result of that initial data.

 

[namliam]

It will depend on his table (structure) what is wize to do... Though I agree it is likely to be better to go from the table directly.

You use the IF only for a sorting column
if [Meal Period]='Morning', 1; if [Meal Period]='Afternoon', 2;, etc.).
So you get the proper order of Morning, Afternoon, Evening
If you order by the meal period, you get Afternoon, Evening, Monring though it still groups them it isnt quite a logical order.