Can't figure out the best way to join queries

[snipe]

Hey guys,



I have 2 queries made, and work perfectly. 1 shows a salespersons total amount sold in each category (bedding and furniture), the other shows the amount sold in each category that has the extended warranty also sold on it (dubbed "covered").

For instance: a salesperson sales a $200 sofa to customer A, and a $300 sofa with the extended warranty to customer B. His total would be $500 and covered would be $300. These queries are used to calculate warranty sales percentage.

Only recently did we split into the bedding and furniture sub-categorys ("sub" meaning underneath the "covered" and "total"). I have been trying to modify the original queries/tables/reports to work with the new system. I am on pretty much on the final query but I can't figure out how to join the queries up now. I keep getting multiple records, but not necessarily duplicates. Pic attached:

The top query is the one where I am trying to join them, middle is covered, and bottom is total. "Bucket" is the category. For the highlighted salesperson, the top query should have only 2 records:
Total _______Covered_________ Bucket
1579.75______1028.50_________Bedding
24033.33_____5089.21_________Furniture

Every salesperson will be in the Total query, but only ones that sold at least $1 in extended warranty will show up in the covered query. So I have an outer join on "SLSP" going from total to covered. (did I say that right? The join arrow points towards the covered query).


How can I get this to work correctly?

Thanks guys

[snipe]

Don't know if this helps you, but:
Attachment 15668

[orange]

snipe,

I get an invalid link on the attachment

How about posting the SQL for each.

[June7]

This is expected with one-to0-many joins, especially if there is more than one 'many' table in the query. Either summarize data with aggregate queries then join the summary datasets to a master dataset of all categories. Or build report with subreports.

[snipe]

Ah that worked. Made the 2 base queries into 4 queries. Works perfectly. Appreciate it June7.