help on making an archive query..

[matorme]

i want a query that will show all records that are 6 months old but that also that is ther only record since then.
eg: A load of orders for customers but i want to archive customers who havnt ordered in 6 months.

iv been tryin to get the query to work so it wud be something like:
>Date()-183 And ""Not got an order past >Date()-183""

just cant think how the syntax for that wud be lol

 

[pl456]

what fields do you have in your query

 

[matorme]

CustomerID,Name,Address,PostCode,OrderDate

and at the moment i have got in the criteria for OrderDate:
<=Date-183 And >Date() Is Null

..but that last bit 'Is Null' doesnt work..

the first part works, shows customers who have orders which are 6months or more old..but some of these customers have orders from yesterday also..

 

[matorme]

anyone have any idea how i can do this, if at all????

 

[Rabbie]

You could check that the Max(orderdate) for a particular customer is <=Date-183

 

[matorme]

how and where would i put that into the query, in the criteria? that makes sense, because i only want the customers who's max (ie latest) order is 6months ago..

 

[matorme]

i guess this doesnt have an easy solution goin on the reply rate! lol o well, guess i'll just have to skip over this query lol..

 

[matorme]

( .......

 

[pl456]

create 3 queries

query 1 to bring back results of anything <Date()-183

query 2 to bring back results of anything >Date()-183

query 3, create unmatched query based on the above two comparing and looking for customers that do not exist in query 2.

 

[matorme]

just figured it out 2day, cant believe how stupid and easy it was lol. i just changed the total for the OrderDate from Grouped By to Max. this then shows me the max order date for each customer and then in the OrderDate i added the crit. <Date()-183 and it works.

"sometimes the hardest problem is solved by the easiest solution." doesnt apply any better than to this situation! lol