Group results to show last sale

[tweety]

I have a working system (sales and stock) i would like the system to show me when a customer placed their last order.

i have made a query to show the shop name (from the customer table) and the date of sale (from the sales table)

the query works but shows all the sales carried out. so if one shop has 10 sales the query will display that shop name 10 tens, whereas i would only like to show the last or lastest sale.

I have made a report based on this query - maybe its more easy to look at the attachment




any ideas anyone? Thanks

[pbaldy]

You may just want the first query:

http://www.baldyweb.com/LastValue.htm

[SealM]

Assuming your table is called [shop], here is the query that would show ONLY the last date:

SELECT [shop].[shop name], Max([shop name].[date of sale]) AS [MaxOfdate of sale]
FROM [shop]
GROUP BY [shop].[shop name];

[pbaldy]

Assuming your table is called [shop], here is the query that would show ONLY the last date:

SELECT [shop].[shop name], Max([shop name].[date of sale]) AS [MaxOfdate of sale]
FROM [shop]
GROUP BY [shop].[shop name];

No, that would throw a parameter prompt.

[SealM]

I am so sorry. Here is the correction...

SELECT shop.[shop name], Max(shop.[date of sale]) AS [MaxOfdate of sale]
FROM shop
GROUP BY shop.[shop name];

[tweety]

Thank you for your help, i couldnt apply the sql given so i used the max from the totals

i noted while testing, if a customer has never placed an order, then their name is not listed at all in the report

how do i go about that?

[pbaldy]

Try to add your customer table to the query, and edit the join appropriately.

[SealM]

Can you post the SQL of your query?

[tweety]

i used the design view to get in running as iam not good with coding, but the sql in below

SELECT tblCustomer.ShopName, Max(tblSales.DateofSale) AS MaxOfDateofSale
FROM tblCustomer INNER JOIN tblSales ON tblCustomer.CustomerID = tblSales.CustomerID
GROUP BY tblCustomer.ShopName;

[pbaldy]

Change INNER to LEFT and see what happens.

[tweety]

thanks that has sloved the problem, it now shows a customer who has never placed an order. IN the report, this is no date (as there is no sale ever made) is there a way to display a message saying to "no sale made"

May i ask what the difference between inter and left is please

[pbaldy]

Check out the Nz() function for the no date issue.

If you double-click on the join line between the tables in design view, you'll get a good description of what the 3 options do (LEFT, RIGHT and INNER).

[tweety]

I found this on the web, but where do i place it? under which event?

If Me![MaxOfDateofSale] = Null Then
MaxOfDateofSale= ("No sales made")
Exit Sub
End If

[pbaldy]

Well, it wouldn't work, but if it did you'd want the detail format event. Did you not like the Nz() function?

[tweety]

the nz fucation requires coding (which i dont really like) however i came accross this http://www.techonthenet.com/access/f...dvanced/nz.php
is that what i should be using or applying? should i be add a variable in the in the query like
expr1: nz([MaxOfDateofSale],'No Sale Made')

thanks