Access 2010 using a date formula in a query

Hi,

In Access, in a date field, I need the data from 1, 2 and 3 years ago.  The data will then be totaled by product for each year (i.e., 2014, 2013 and 2012).  Is there a way to do this in one query?  And, how do I write the expression?

Thank you.

Pat
FFNStaffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bcnagelCommented:
Hello Pat.

Using the Year() function (like, Year(MyDateColumnGoesHere)), you can return the year from any date field at all. If you then group by product and the Year() value, you should have what you need.

Does that make sense?
0
Rey Obrero (Capricorn1)Commented:
to elaborate on what bcnagel posted, here is a sample query

to get the total price of each product

select year([datefield]), product, sum([price])
from tableName
group by year([datefield]), product

to get the count of product

select year([datefield]), product, count([product])
from tableName
group by year([datefield]), product
0
Gustav BrockCIOCommented:
You will have to filter on the years:

    Select
        ProductID,
        Year(PurchaseDate),
        Sum(Amount) As TotalSales,
        Count(Quantity) As TotalQuantity
    From
        tblYourTable
    Group By
        ProductID,
        Year(PurchaseDate)
    Having
        Year(PurchaseDate) Between Year(Date())-3 And Year(Date())-1

Replace table and field names with those of yours.

/gustav
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

FFNStaffAuthor Commented:
Gentlemen,

I'm a novice in Access so please have patience.

I think I understand the functions you're using but I don't know where to use them.  Am I using them in Expression Builder in a separate column?  Am I using them in Criteria cells under the various Fields in Query Design?

The goal is to have information for each sales person for each year for each product.  It would look like this:

Person    Prdct   2015   2014  2013  2012
S Jones       AUM     $10     $9        $8     $7
S Jones       Plan      $15
M Kay         AUM      $75    $50      $25    $10

Thank you.
0
Gustav BrockCIOCommented:
Then create a Query like this:

   Select
         Person,
         Product,
         Year(PurchaseDate),
         Amount
     From
         tblYourTable
     Where
         Year(PurchaseDate) Between Year(Date())-3 And Year(Date())

Now run the Crosstab Query Wizard using this query as source.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FFNStaffAuthor Commented:
Thanks to all of you for responding.  I felt that Gustav provided the clearest information.

Gustav, your answers worked well after a bit of experimentation to understand what you were telling me.  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.