Multi Query Report (1 Viewer)

[JPearson]

Hello all.
I have been unable to solve this and need some help.

I have a large amount of data that needs to be looked at from a number of angles and am having a problem with getting it all to show.

running the individual querys I obtain the information I need but I need to combine these into 1 report.

queries

User cnt:
Order Date
Total number of users that day.

Order information:
Date
Business unit
total count of unique order #
Total count of order lines
Total amount of product sold

Reserve Information:
Date
Business unit
total count of unique reserve#
Total count of reserve lines
Total amount of product reserved

The issue I have is the order information for any given day may contain different set of business units

ie Order info may have:
date BU #O #L Total
2/4/10 A 250 600 65871
B 25 90 5481
D 1 5 15

and Reserve info will have
date BU #R #L Total2/4/10 A 35 70 1500
B 2 10 542
C 1 5 15

If I join these querys on date they either leave both odd BU out or leave the BU out of one or the other.

Thanks in advance for your help.

-JP

 

[Trevor G]

What about appending the data into a temp table and use the temp table as the record source for your report?

Trevor

 

[JPearson]

That actually occured to me on the way in today.
Im not sure how to work it since I will be dealing with the same problem for both queries possibly having different data that I want to group on.
I think the best solution may be to use a temp table and write some VBA logic behind it to ge the data to line up right.

Thanks for the help!!!!!
-JP

 

[CCFL]

I am having the same issue. I have build 15 different queries, each query have the same fields but different data. I would like to have one report to show the sum of each query in one report.

Did you get yours to work and if so, how?

Thanks in advance

 

[boblarson]

Both of you can use subreports or, in the case of CCFL, might be able to create a UNION query for the data.

 

[JPearson]

I ended up using a temp table to combine the data with VBA running and putting the data into the correct fields on the table to generate the report. It actually works well since this report generates 4 lines of data per day from 2 seperate tables (10k+ lines and 2k+ lines) per day. This allowed me to archive the data and keep the Data base size down without losing the report information.

JP

 

[CCFL]

Cool, I just used UNION ALL and it worked but now how would I label or identify each line to show what query it came from?

Thanks

 

[boblarson]

Cool, I just used UNION ALL and it worked but now how would I label or identify each line to show what query it came from?

Thanks

You can use

Select Query1Name.*, "Query1" As QueryName
UNION ALL
Select Query2Name.*, "Query2" As QueryName
UNION ALL
Select Query3Name.*, "Query3" As QueryName

as long as each query has the same number of fields returned.

 

[CCFL]

This is what I tried adding to the query.

'HL' AS Exp1 INTO dbo_BalanceCash Drawer

When used on a regular query, it added a column and inserted HL for each line. But when used on a UNION ALL query, I got an error message.

 

[boblarson]

This is what I tried adding to the query.

'HL' AS Exp1 INTO dbo_BalanceCash Drawer

When used on a regular query, it added a column and inserted HL for each line. But when used on a UNION ALL query, I got an error message.

You don't use INTO.

You don't need to add it into the table. Just use what I showed to add a field at runtime which identifies the query it came from.

 

[CCFL]

You can use

Select Query1Name.*, "Query1" As QueryName
UNION ALL
Select Query2Name.*, "Query2" As QueryName
UNION ALL
Select Query3Name.*, "Query3" As QueryName

as long as each query has the same number of fields returned.

-----------------

YOU ARE THE BEST!!!!!! - It worked

 

[boblarson]

Glad we could help you out.