Create a report from multiple queries

[Roy33]

I am writing a small sales tracking db in access and I am having trouble with a report.

The goal is to make a report that lists the top salesperson in each category. I already have the data from 5 queries -- each query has the top sales person and the number of sales in the query's category.

Two of these queries for example are:

query_computers
--------------
FIRST ID_EMP COMP_NUM
John Doe 5

query_stereos
-------------
FIRST ID_EMP STEREO_NUM
John Public 3

These queries came from other queries -- I won't go into detail because I don't think it matters for this question (but I will if I need to later).

I want to make a report like this -- the ( ) show where I think each item in the report should come from:

report_totals
-----------

Most computers sold: John Doe 5
(textlabel) (all three cols from query_computers)

Most stereos sold: John Public 3
(textlabel) (all three cols from query_stereos)


Thanks for any and all help!

 

[jrbarnett]

Roy,

You can union the SQL query code together (this can't be generated by the QBE grid), the code looks something like

select first, id_emp, comp_num, 'computer' as type from query_computers
union
select first, id_emp, stereo_num, 'stereo' as type from query_stereos

To enter this, go to query design and click View -> SQL and copy and paste the code from above.

The fourth category adds a label that can be used to identify the type of data that the line represents.

John

 

[zrzr]

I know it's a bit (I don't know the word in English, maybe brutal ...), but in these cases, I use sub reports, each subreport based on one query.

So, what word do you use for this ? ;o)

 

[jrbarnett]

Roy,
It just occurred to me actually, if the sales data is all in one table then it may be easier to take the whole lot and Group By on it, pulling the largest total for each group which gets around the multiple queries problem.

Zrzr
Brutal will do nicely for this, "Over the top" is another that will fit in to your situation as well.

John

 

[Roy33]

I just found an awesome "shortcut" to answer my own question!

It's easier to demonstrate than explain:


query_1: the results of a query from my entire sales table
--------
FIRST ID PRODUCT
John Doe Computer
John Doe Computer
John Doe Stereo
John Public Stereo
John Public Stereo
John Public Computer
Jane Doe Stereo

query_2: based on query_1 and counts the total of ONE of the products for each employee (e.g. stereos). This query is designed with a COUNT function and sorted descending:
------
FIRST ID STEREO_NUM
John Public 2
John Doe 1
Jane Doe 1

Now here comes the part where I pull the top person from query_2.

I took the SAME query_2 (yes, the one that I acutally USED to create query_2, NOT the returned table from query_2) and in the query design view, I clicked on the line where the count feature is listed. At the top of the tool bar, next to the summation symbol, there is a white text box (looks like the one where you select zoom in word) and is has the number 100. I typed in the number 1, and pressed return.

POW!
The count function now returns only the first record of my query. I used this tiny modification to adjust my original count query! Note you can do this with %'s too!

Hope this helps. If you want something a little more technical with details, I'll be happy to post more.