CODEWITHSUNDEEP
sqlms-access
JD88
JD88

Reputation: 51

Getting a total for current year - SQL

I need to get the total number of miles travelled this year using SQL in an access DB. At the moment I'm trying the following query but its not working:

   SELECT 
      SUM(Mileage) 
   FROM 
      [Mileage-Expenses] 
   WHERE 
      YEAR(DatePurchased) = YEAR(CURRENT_TIMESTAMP)

Any ideas how to fix this?

Upvotes: 1

Views: 1421

Answers (2)

HansUp
HansUp

Reputation: 97101

A WHERE clause based on YEAR(DatePurchased) requires the db engine evaluate that expression for every row in the table. You would get better performance by adding an index on DatePurchased, if you don't already have one, and then using a WHERE clause which allows the db engine to retrieve only the rows from the current year before computing SUM(Mileage).

SELECT
    SUM(Mileage)
FROM 
    [Mileage-Expenses]
WHERE
        DatePurchased >= DateSerial(Year(Date()), 1, 1)
    AND DatePurchased < DateSerial(Year(Date()) + 1, 1, 1)

Upvotes: 3

GarethD
GarethD

Reputation: 69749

I've not used MS Access for a long, long time, but as far as I remember it does not support CURRENT_TIMESTAMP, you will need to use DATE() or NOW() instead:

   SELECT 
      SUM(Mileage) 
   FROM 
      [Mileage-Expenses] 
   WHERE 
      YEAR(DatePurchased) = YEAR(DATE())

http://office.microsoft.com/en-gb/access-help/use-current-date-and-time-in-calculations-HP001098479.aspx

Upvotes: 1

Related Questions