whitesmoke
Registered User.
- Local time
- Yesterday, 17:19
- Joined
- Jun 23, 2006
- Messages
- 29
I've been busting my head on this, Thank you in advance to everyone that can help.
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 to to do an outer join with both the factories and customer table where it shows all factories not having transactions and all customes not having transactions.
thanks to anyone that can help
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 to to do an outer join with both the factories and customer table where it shows all factories not having transactions and all customes not having transactions.
thanks to anyone that can help