Link to home
Start Free TrialLog in
Avatar of pcalabria
pcalabriaFlag for United States of America

asked on

Need Help writing query to calculate sales

Hello Experts, I'm using Access 2K.

I need to write a query to calculate four numbers:
Lifetime Orders
YTD Orders
LifeTime Sales (in dollars)
YTD Sales (in dollars)

 I already have code that calculates the first day of the current year.

Here's what I have so far.  The code below calculates three of the four values I need (LifeTime and YTD Orders, as well as Lifetime Sales in dollars), but I can't figure out how to calculate YTD sales in dollars with the same query.

   'New code to calculate Orders (YTD and Lifetime) and Lifetime Dollars
        strSQLtext = "SELECT Count(invoicenumber) AS LifeTimeOrders,"
        strSQLtext = strSQLtext & "SUM(ABS(shippeddate > #" & dteYTDstartDate & "# )) AS YTDOrders, "
        strSQLtext = strSQLtext & "SUM(price * Quantity) AS LifetimeDollars "
        strSQLtext = strSQLtext & "FROM invoicehistory "
        strSQLtext = strSQLtext & "where AccountNumber = " & Forms!ProspectInfo!AccountNumber & " And [quantity] <> 0;"

Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
List few records of data, and expected output.
Avatar of pcalabria


Perfect! Thank you!
To make that even more flexible (use it year after year), try:

SELECT AccountNumber
           , Count(InvoiceNumber) as LifeTimeOrders
           , SUM(IIF([ShippedDate] >= DateSerial(Year(Date()), 1, 1), 1, 0)) as YTDOrders
           , SUM([Price] * [Quantity]) as LifetimeDollars
           , SUM(IIF([ShippedDate] >= DateSerial(Year(Date()), 1, 1), [Price] * [Quantity], 0)) as YTDDollars
FROM InvoiceHistory
WHERE AccountNumber = 12344
AND [Quantity] <> 0
GROUP BY AccountNumber

I've replaced the reference to a specific date: #1/1/2013#
with: DateSerial(Year(Date()), 1, 1)

Which will give you the first day of whatever year you are in.