• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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
0
FFNStaff
Asked:
FFNStaff
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now