I'm building a shopping site and would like to email customers who've registered but haven't bought anything from the site in the last 6 months.
I have two tables:
tblcustomers is the table where the registered customers are stored, the key fields are:
custId (unique id)
custName (their name)
custEmail (their email address)
I then have another table for orders placed on the website: tblshoporders, the key fields are:
OrderId (unique id)
OrderCustId (the id of the customer who placed the order)
OrderDate (the date of the order (YYYY-MM-DD)
I need to create a query that will make a recordset of customers who have not bought anything in the last six months. If they have made a purchase (within the last 6 months) they can be ignored.
I've been trying various queries but the problem I'm getting is that a single customer may have placed several orders over different dates but I only need to make sure they haven't bought anything in the last six months - also my query is finding only customers that "have" made a purchase, I also want any customer that hasn't made a single purchase yet (i.e. is not in the tblshoporders table).
Any help would be gratefully received.