last month / year, current month year same query (1 Viewer)

[whitesmoke]

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

 

[RV]

What you need is NOT EXISTS.
Basic SQL you need is:

SELECT *
FROM tblTransaction
WHERE NOT EXISTS
(
SELECT *
FROM tblFactory
WHERE tblFactory.facid = tblTransaction.facid
)
AND NOT EXISTS
SELECT *
FROM tblCustomer
WHERE tblFactory.tblCustomer= tblCustomer.tblCustomer
)

If you want to seperate overviews, one for customers, one for factories, split up the query into two separate ones.

Adapt the query to your object names.

RV

 

[whitesmoke]

thanks for the input RV . i may have left to clarify that i need both the records of the factories and customers that match the criteria and the records that do not, in a crosstab type of view, at this point i'll settle for a report! as shown cust1 and cust2 have values, but cust 3 only meets certain criteria and other criteria is left with 0 values. would the notexist work this way?


also i noticed to say, there is not link between the customer and the factory, except in the transactions table. its a many to many.

you just gave me the idea to use a UNION with exist and not exist, but can you help with the logic. thanks again.

just to note to.. i have to use the criteria of format(orderdate, 'mmyyyy') based on a date search criteria and today's date