By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,827 Members | 2,155 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,827 IT Pros & Developers. It's quick & easy.

Totals query for the last date

P: n/a
I have a problem with building a totals query showing data
for the last date only.Under the last date i understand the last date
of field invoicedate.This invoice date is in the table orders.
Therefore in the column of the query i have entered :

DMax("InvoiceDate";"Orders")

I have also tried with :

DayNumber: DatePart('d';[invoicedate])

However both my approaches have led to a query that does not show the
right results.This is because i do not understand well the functions of
Date part and DMax.
Can somebody help me building the query ? My aim is to show
the quantity from all those invoices that have an invoice
with an invoice date equal to the last date of the orders
entered.

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

Further to my question about the last date i have managed to rebuild
the query with the DMax function,however the query gives the total
quantity of all days and not only for the last day.
May i have some comment about my query?
SELECT Sum([order details].liters) AS SumOfLiters,
DMax("InvoiceDate","Orders") AS Expr1, customers.afid
FROM (customers INNER JOIN orders ON (customers.Customerid =
orders.customerid) AND (customers.Customerid = orders.customerid))
INNER JOIN (products INNER JOIN [order details] ON (products.Productid
= [order details].ProductID) AND (products.Productid = [order
details].ProductID)) ON orders.orderid = [order details].OrderID
WHERE (((orders.paymentid)>0))
GROUP BY DMax("InvoiceDate","Orders"), customers.afid;

Nov 13 '05 #2

P: n/a
the DMAX stuff should be in your WHERE clause...

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.