I am trying to run a query that will give me all of my customers who last purchased on or before 2004. I do not want anyone who purchased anything in 2005. I am including, name, address, telphone number, salesman, order date, and shipping method. I leave all criteria blank, and I have the order date totaled at "max", I run the query, then I filter the form for the shipping method of "no cod". When I run the query, it gives me my customer listing for everyone who purchased from 1999 on and where shipped out no cod. That is fine, except that some of the customers are listed a few times with different dates. Say, John Smith, my query is coming up with an order from 1-5-99 and then a few pages later, (because I am also putting it in descending order) he is on another page with the date of 2-5-2000. Why is this? In my criterea for the "order date" I have it as "MAX", which should bring me this customer only once. Is that correct? I am really having trouble with this. Can anyone help? I only want the customers listed once with their last order date.
last order date of 2004 (1 Viewer)
- Thread starter MarySan1
- Start date
mresann
Registered User.
- Local time
- Today, 06:44
- Joined
- Jan 11, 2005
- Messages
- 357
Create a subquery that contains just the customer ID in the SELECT line, with the WHERE clause the transaction date, limiting the field to only transaction dates from 1-1-2005 and later (probably from the Orders or the Transactions table). Once the subquery is finished, copy the SQL. Open your original query. In the Conditions box of the Customer ID field, type "Not In()". Paste the subquery between the paranthesis.
See if this helps.
See if this helps.
