Avatar of pcalabria
pcalabria
Flag 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;"


Thanks!
Microsoft AccessMicrosoft ApplicationsSQL

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Hamed Nasr

List few records of data, and expected output.
pcalabria

ASKER
Perfect! Thank you!
Dale Fye

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61