last date as criteria

[samotek]

i have a query showing litres per month week and date.
I need a query that shows the liters only for the last date
available.is there any possibility to give he query a criteria to show only the last date ?

SELECT Format([InvoiceDate],'mmmm') AS MonthName, DatePart('m',[invoicedate]) AS MonthNumber, DatePart('ww',[invoicedate]) AS WeekNumber, DatePart('d',[invoicedate]) AS DayNumber, Sum([order details].liters) AS SumOfLiters
FROM orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID
GROUP BY Format([InvoiceDate],'mmmm'), DatePart('m',[invoicedate]), DatePart('ww',[invoicedate]), DatePart('d',[invoicedate])
ORDER BY DatePart('d',[invoicedate]) DESC;

 

[lupins46]

NOt sure what you are asking- you appear to be saying that you want this query just to summarise one record (the last invoice). Can you clarify.

 

[samotek]

yeas but i have shown a stripped query only.Actually it will show the sales in the different cities for this day.
And for one day there are many records

 

[LittleSmudge]

SELECT TOP 1 Format([InvoiceDate],'Medium Date') AS TheDate, 
             Sum([order details].liters) AS SumOfLiters
FROM orders 
INNER JOIN [order details] 
      ON orders.orderid = [order details].OrderID
GROUP BY TheDate
ORDER BY InvoiceDate DESC;

Sum of all order place on the last date available

Is that what you mean ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[samotek]

Thank you.I got he following error :you tried to execute a query that does not include the specified expression "Format([Invoicedate],MediumDate) as part of an aggregate function.What may be tthe reason ?

 

[LittleSmudge]

Oh yes - right

SELECT TOP 1 Format([InvoiceDate],'Medium Date') AS TheDate, 
             Sum([order details].liters) AS SumOfLiters
FROM orders 
INNER JOIN [order details] 
      ON orders.orderid = [order details].OrderID
GROUP BY Format([InvoiceDate],'Medium Date')
ORDER BY InvoiceDate DESC;

I put the wrong side of the As in the Group By clause.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.