criteria help! past orders

[shutzy]

sorry about the title. i dont know how to say this in a nut shell.

i have a query that gives results of previous orders for customers. but it will only give the most recent.

what i am trying to do is generate a mailing list reminding customers of their previous orders. asking for feedback, if they are ready to place another one etc. the problem i have is that it is a salon. so customers can have future bookings. so i would like to deduct from the mailing list the items that the customer has in the future.

eg. a customer ordered item 1 and item 2 in feb 2010. the customer has already booked for item 1 again in june but not item 2. so i am wanting to have a query that will give me the most recent item that a customer does not already have booked in.

the sql i already have is below but it is missing the most crucial minus the future orders.

SELECT tblOrders.ClientDetailsID, tblClientDetails.FirstName, tblClientDetails.Surname, Last(tblOrdersItems.OrdersItemsID) AS LastOfOrdersItemsID, Last(tblItems.Items) AS LastOfItems, Last(tblOrders.OrderDate) AS LastOfOrderDate, Last(tblCategories.Catergorie) AS LastOfCatergorie, tblOrderItemsType.Type, Last(tblItems.available) AS LastOfavailable, Last(tblItems.ItemsID) AS LastOfItemsID, tblClientDetails.EmailAddress, tblOrdersItems.EmailPurchaseReminder, Last(tblEmployeeList.FirstName) AS LastOfFirstName INTO tblEmailPurchaseReminders
FROM tblOrderItemsType INNER JOIN (tblClientDetails INNER JOIN (tblCategories INNER JOIN (tblEmployeeList INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.[ItemsID]) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblCategories.CategoriesID = tblItems.CategoriesID) ON tblClientDetails.ClientDetailsID = tblOrders.ClientDetailsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID
GROUP BY tblOrders.ClientDetailsID, tblClientDetails.FirstName, tblClientDetails.Surname, tblOrderItemsType.Type, tblClientDetails.EmailAddress, tblOrdersItems.EmailPurchaseReminder
HAVING (((Last(tblOrders.OrderDate)) Between DateAdd("m",-3,Date()) And DateAdd('m',-6,Date())) AND ((tblOrderItemsType.Type)="Retail") AND ((Last(tblItems.available))=Yes) AND ((tblClientDetails.EmailAddress) Is Not Null) AND ((tblOrdersItems.EmailPurchaseReminder)=No));

now im not sure if i am going to have to do this in this query or use multiple queries to compile the data and get the results i want.

thanks for reading this and if i have not made much sense just let me know because i found it hard to explain. i know what i want to do in my head and hopefully you guys also do now.

 

[jdraw]

So select booking records only up to today's date. Don't include any booking records for anything after today.

You're going to have to tell us in plain English what exactly is the criteria for selecting info for your mailing list.

 

[shutzy]

right. the criteria for the initial result is

the most recent item that client x purchased between 3 months ago and 6 months ago.

the reason i have set this date is because i have only just started doing this and i dont really want to badger customers that bought items 2-3 yrs ago. and the 3 months is because sometiem customers only want to come in every so often. so again i dont want to badger them.

so what i want is

data:
item 1 | client a | 01-08-12
item 2 | client a | 01-09-12
item 3 | client b | 01-08-12
item 4 | client b | 01-09-12

the initial results would show

item 2 | client a | 01-09-12
item 4 | client b | 01-09-12

because these are the most recent orders for these particular customers. but if

item 2 client a >date is there then the results would be

item 1 | client a | 01-08-12
item 4 | client b | 01-09-12.

this is because client a alread has booked in for item 2 and i really would not want to send them this because they will think that the email i send are junk. i want them to be relevant so they take notice.

i could do with the sql i had but with adding in

where ItemID.+ClientID does not exist >Date()

i dont know if this is possible but i hope this makes things clearer.

 

[jdraw]

I mocked up your table
and created a query to get the last BookingDate for each Client before a Date that you can enter.

The sql for the query is

SELECT tblClientItemDates.ClientId
, Last(tblClientItemDates.ItemNo) AS LastOfItemNo
, Last(tblClientItemDates.BookingDate) AS LastOfBookingDate
FROM tblClientItemDates
WHERE (((tblClientItemDates.BookingDate)<[Enter Date Dd/mm/yyyy]))
GROUP BY tblClientItemDates.ClientId
ORDER BY tblClientItemDates.ClientId;

The attached jpgs show

- the Table with data (not exactly like yours) and the query SQL
-results for 2 different dates.