how to create query of customers with a certain last order date 1

[MarySan1]

I want to create a query that will return me the customer name, address, telephone #, city, state and zip and the product they ordered. I want to be able to also select a certain dates that the last order was placed. I have a query set up already, but when i put in a specific last order date, it returns me with every order for the customers. I only want the customer to show once, with the specific order date. Can anyone help?

 

[Crowley16]

well, are you sure you've got the condition right?

post the sql so we can have a look...

 

[MarySan1]

here is the sql
SELECT DISTINCTROW Customers.CompanyName, Customers.Name, Customers.Address, Customers.city, Customers.state, Customers.SecPhone, [Order Details].ProductID
FROM ((Customers INNER JOIN (Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) ON Customers.CustomerId = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID
WHERE ((([Order Details].ProductID) Like 36) AND ((Orders.OrderDate) Between #1/1/2003# And #12/31/2003#) AND ((Employees.LastName) Like "boy*"))
ORDER BY Customers.state DESC , Orders.OrderDate DESC;

 

[Crowley16]

you are returning all records between 01/01/2003 abd 12/31/2003...

if you want to return last order date, then you need to have the condition orders.orderdate = #last order date#

 

[MarySan1]

WHERE DO I PUT THIS CONDITION? i'M NOT VERY EXPERIENCED IN QUERIES

 

[MarySan1]

how do I put this condition into the sql? I'm trying and it's telling me the syntax is invalid. Where exactly do I place this and do I use it as an expression?

 

[TomCologne]

Here's a very simple example:

SQL View

SELECT tblBusName.BnListID, tblBusName.BnBusTradeName, Last(tblBusName.BnMemberDate) AS <LastOfBnMemberDate>(<Default name when totaled by Last, can be renamed>)
FROM tblBusName
GROUP BY tblBusName.BnListID, tblBusName.BnBusTradeName;

Design View

To apply this to your qry:

Click the "Totals" button (sigma, the funky 'E') on the qry menu, this adds a row 'Totals' to the qry grid. From the drop down combo for field 'Orders.OrderDate' choose Last or Max.

Note that the "Where" clause is invisible in a totals qry.

TomCologne

 

[MarySan1]

thanks, tom that was very helpful and I used your suggestion for the design view and it worked.