Function?!?

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi:

Store Date
1 01-Aug-04
1 03-Aug-04
2 04-Aug-04
2 05-Aug-04
3 06-Aug-04
****************
I have many stores thata should send data every day for
the whole month/year. I would like an expression that will
show that store 1 did not send data August 2 and so forth
for the other stores...

Any HELP is appreciated
 
Hi,



Assuming that if no store send anything at a given date, that date is
irrelevant:


SELECT DISTINCT [Date] As theDate FROM myTable
saved as Dates

SELECT DISTINCT store FROM myTable
saved as Stores



SELECT x.*
FROM ( SELECT store, theDate FROM Stores, Dates) As x
LEFT JOIN myTable As z
ON x.store=z.store and x.theDate=z.[date]
WHERE z.store IS NULL
ORDER BY x.store

should do, if the cross product is not too large. The technique generates
all the possible couple (store, date), and keep those not present.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top