query design. current month. ytd, last ytd/month same query (1 Viewer)

[whitesmoke]

I know i know, double posting.. sorry posted it in wrong thread figured i get more hits this way...

if anyone of you experts can help i'd greatly appreciate it.


Structure:
customer table - custid pk
factory table - facid pk
transaction table
- custid
- facid
- orderdate
- amount
The results should look like this in a query doing a search on order date between 01/01/06 and 05/30/06

factory name
salesman
cur mo lst yr mo DT Range Lst Yr Range %Diff %Ovrall Curr YTD
(0606) (0605) 01/01/06- 01/01/05 - Total
05/30/06 05/30/05
-------------------------------------------------------------------------
cust1 $123.00 50.05 60.00 120.00 -50 1.27 500.00
cust2 $223.00 70.05 100.00 .00 100 15.00 00.0
cust3 0.00 0.00 0.00 50 -0 .2 50.00

hopefully you get the idea. completed sum amount groupings by facid, emp id and cust id.
i've tried breaking it down to current month year query and last month year queries. the trick is trying first combine the categories and do an outer join with both the factories and customer table where it shows all factories that DO have a match to the query and showing factories and all customers not having transactions.

thanks in advance to anyone that can help..