find multiple orders query

[Guest]

I want to build a query that gives me a list of TODAYS'S orders where that
same customer has placed another order in the past 3 weeks.

I built 2 queries one that shows todays orders, and one that shows trailing
3 weeks orders. I can't seem to figure out how to show only orders from
today that have another order in the last three weeks.

Thanks,
Billy

 

[tina]

write a third query based on the first two. link the CustomerID field of the
two queries together (called an INNER JOIN).

hth

 

[John Spencer]

You can try something like the following

SELECT Orders.*
FROM Orders
WHERE OrderDate = Date()
AND Exists (SELECT *
FROM ORDERS as O
WHERE O.CustomerID = ORDERS.CustomerID
AND O.OrderDate Between DateAdd("d",-21,Date()) AND
DateAdd("d",-1,Date()))

If you are doing this in the query grid
Field: OldOrder: SELECT * FROM ORDERS as O WHERE O.CustomerID =
ORDERS.CustomerID AND O.OrderDate Between DateAdd("d",-21,Date()) AND
DateAdd("d",-1,Date()))
Criteria: True

 

[John Vinson]

I want to build a query that gives me a list of TODAYS'S orders where that
same customer has placed another order in the past 3 weeks.

I built 2 queries one that shows todays orders, and one that shows trailing
3 weeks orders. I can't seem to figure out how to show only orders from
today that have another order in the last three weeks.

Thanks,
Billy

Create a third query joining these two queries by CustomerID. Select
only the fields from the today's order query.

John W. Vinson[MVP]