query for last order date (1 Viewer)

[MarySan1]

I have tried posting this message before, but with no results. I am trying to get a query that will give me all of my customers and show what their last order date is. The query that I have now is coming back with ALL the dates the customers made purchases. Here is how I have the query set up:
Field1 Customer ID
Field 2 Customer name
Field 3 Address
Field 4 State
Field 5 Phone #
Field 6 Order Date (Last in Group by)
Field 7 Shipping Method
Field 8 Salesman name

The query is a select query and I hit the totals button on the toolbar. In Field 6-Order date, I have it grouped by "last".
Another problem that I have it I am working in a 97 access database. I have tried to convert it to 2003 with no success. So, I am not able to make NEW queries or reports. I can modify them, but when I close the query or report design, it does not save.

Can anyone help me with this query problem? It is very perplexing.

 

[reclusivemonkey]

You are asking the query to group on every field, which is why it is returning all the fields. You need to have CustomerID and Order Date ALONE in your query. Set the Order Date to Group on Last and Customer ID to Group and you should get the results you need.

 

[MarySan1]

Do you mean that I should have 2 seperate queires? If so, then the problem there is that I can not create new queries.

 

[MarySan1]

I want the query to give me all of my customers, but only once and to show the last date that they were sold. I really need help!!

 

[Murli]

Remove Field 7 - Shipping method and Field 8 - Salesman name from your query and you would have the results.

 

[MarySan1]

Great, that worked. Now what I want to do is add another table, Order Details, where I would take, unit price, quantity, product id, and the expression ([Quantity]*[UnitPrice]. When I do this and try to run the query, it comes up and says
The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258)

The problem with this now is that I cannot create a new query, because for some reason the "new" button is grayed out. Please help!!

 

[MarySan1]

This is the sql that I am using right now and the query works correctly. It is like you said, I have diffrent salesman selling a customer and also diffrent shipping methods.

SELECT DISTINCTROW Customers.CompanyName, Customers.Name, Customers.Address, Customers.city, Customers.state, Customers.SecPhone, Max(Orders.OrderDate) AS MaxOfOrderDate, Customers.CustomerId
FROM Customers LEFT JOIN (Employees RIGHT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) ON Customers.CustomerId = Orders.CustomerID
GROUP BY Customers.CompanyName, Customers.Name, Customers.Address, Customers.city, Customers.state, Customers.SecPhone, Customers.CustomerId
ORDER BY Max(Orders.OrderDate) DESC;


Now when I want to add the table Order Details, I get the error
The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258)

Now I really have no idea about sql and the joins. All of my tables are related. I can't understand why it will not let me add what the product is and the price.
When I add the table and select the fields that I want, this is the sql.

SELECT DISTINCTROW Customers.CompanyName, Customers.Name, Customers.Address, Customers.city, Customers.state, Customers.SecPhone, Max(Orders.OrderDate) AS MaxOfOrderDate, Customers.CustomerId
FROM (Customers LEFT JOIN (Employees RIGHT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) ON Customers.CustomerId = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Customers.CompanyName, Customers.Name, Customers.Address, Customers.city, Customers.state, Customers.SecPhone, Customers.CustomerId, [Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice
ORDER BY Max(Orders.OrderDate) DESC;


I really need this query to work.

Marysan

 

[Murli]

You have to write another query on the current one to get the unit price, quantity and other details.

 

[MarySan1]

okay, so, if I write a query with just the order details table, and select the unit price, product id, quantity and make an expression for a total, (unit price x quantity), and if it works correctly, where do i paste that sql into my original query? I'm really lost when it comes to this.

 

[Murli]

You have to query on the original query and the order detail table to get the results. Including the sql in the original query wont work.