Microsoft Access Query

[Hugh Hethner]

Hey guys, sorry for not being active on here, just been really busy with work.

So, I'm doing my ICT GCSE Project which is a database. It is basically a database containing data for a video game store. It has 4 tables; tblOrder, tblCustomer, tblProduct and tblSupplier.

So I've successfully made 2 queries so far which show all the orders between 2 user inputted dates. The other query shows any video games which have a stock level below 10 but are not preowned games.

I now have this new idea. I wish to create a query which would search for any members who have not purchased a game from the store within the past 3 months, from the current date. I have my relationships right but I have no idea how to go about doing this. In my orders table I do have things like Order ID and Order Date. The Orders table also contains a Cust ID which is linked with the Cust ID from the customer table. So I need my query to only show members who have not purchased a game within the past 3 months, from the date that the query is ran.

Can anyone please shed some light on the type of criteria I am supposed to use? Please just keep it simple and effective, I don't want to have to learn a new programming language just to create a silly query.

Thanks.

I've scoured the internet and found this criterion I can use. >=DateAdd("m",-3,Date())

This, basically shows me all the records which are in the past 3 months. What I need is the records showing, which are not, in the past 3 months. But I also need this to be linked to the customer, in the way that I explained above. If I did it this way, what if a customer bought a game in both time periods? So it is quite a tricky query this is, so I would appreciate some guidance.

Okay, by simply switching the ">" sign around, I managed it to get it to show me all of the records, NOT in the past 3 months. Now I need it to show me the customers who have NOT ordered a game in the past 3 months. It is quite tricky because some customers may have ordered in both time periods. Basically right now its showing me a load of inactive orders. I need a list of inactive customers.

[NeoPa]

You would need first to connect up your [Customer] and [Order] tables with a LEFT JOIN.

Next ensure the query is has a GROUP BY clause specifying to group on the Customer Account.

Your HAVING clause would then exclude any groups where the Max() of the [Order Date] is either Null or greater than a date of three months ago. Something like :

HAVING Nz(Max([Order Date]), #1/1/1900#) > DateAdd('m', -3, Date())