ansaurus

Question

Access database query for using month from a date

Answer 1

+2 A:

Try:

SELECT SUM(ABS(lunch)) AS lunchCount, SUM(ABS(snacks)) AS snackCount, SUM(ABS(tea)) AS teaCount
FROM <TableName>
WHERE eventDate >= #1/1/2009# AND eventDate < #2/1/2009#

Ikarii Warrior 2009-08-07 05:12:14

SUM will not work for me as these fields are boolean fields; we need to count only the 'true' ones. If it is difficult then I could change the field to be int and then sum them.

Krishna Kumar 2009-08-07 05:30:43

Answer 2

+3 A:

In Access, True is -1 and False is 0. So you can use the absolute value of the sum of those values to indicate how many were True.

SELECT Abs(Sum(lunch)) AS SumofLunch, Abs(Sum(snacks)) AS SumofSnacks, Abs(Sum(Tea)) AS SumofTea
FROM YourTable
WHERE eventDate >= #2009/08/01# And eventDate < #2009/09/01#;

HansUp 2009-08-07 06:28:55

Trouble is other databases true is +1. Thus I'd suggest putting abs() around the sums. Also your WHERE (((Month([eventDate]))=8)) will give you two years worth of numbers in August of 2010.

Tony Toews 2009-08-07 06:39:59

I made suggested changes. Thanks, Tony.

HansUp 2009-08-07 07:32:28

You can use Between, it is inclusive of first and last value: eventDate BETWEEN #2009/08/01# And #2009/08/31#

Remou 2009-08-07 08:21:07

I considered eventDate might include time values, so #2009/08/31 01:00# would not be included unless I used "DateValue(endDate) BETWEEN ...". Is there a compelling reason to favor BETWEEN over ">= And <" in this example?

HansUp 2009-08-07 13:39:19

I, for one, avoid storing both date and time in the same field. It makes life much easier when you need to do date operations more often than time-sensitive ones.

David-W-Fenton 2009-08-07 23:31:11

"I, for one, avoid storing both date and time in the same field" -- yes, that's why the Access database engine has dedicated DATE and TIME data types... NOT! So presumably you are faking it with DATETIME data types. Regardless, you are splitting into subatomic parts, using Validation Rules to ensure each subatomic elements has a 'magic' date/time part (1899-12-30 and midnight respectively, I'd guess) and spend half you time joining them back together. Classic design flaw! Suggestion: read the Snodgrass book, no need to remain ignorant ;) http://www.cs.arizona.edu/people/rts/tdbbook.pdf

onedaywhen 2009-08-10 11:32:38

@HansUp: "Is there a compelling reason to favor BETWEEN over ">= And <" in this example?" -- yes but only if you are using closed-closed representation (i.e. where the end date is considered included in the period) with one second granularity. Most Access users employ closed-closed representation but erroneously assume Access will honour one day granularity (you need explicit Validation Rules or CHECK constraints for that). Therefore the ">= And <" pair of predicate is preferred even though it requires two expressions.

onedaywhen 2009-08-10 11:40:20

Answer 3

A:

I'd attack this in access using IIF statements (think like CASE statements in other databases). IIF in Access returns a value when the specified condition is true (after the first comma) or another value if it evaluates to false (after the second comma). Because you are using booleans, checking for a true condition and rolling up via an aggregate sum will do the trick for you.

SELECT Month(EventDate) AS TheMonth, Year(EventDate) AS TheYear, 
SUM(IIF(Lunch, 1, 0)) AS LunchCount,
SUM(IIF(Snacks, 1, 0)) AS SnackCount,
SUM(IIF(Tea, 1, 0)) AS TeaCount
FROM YourTable
GROUP BY Month(EventDate), Year(EventDate)
ORDER BY Month(EventDate), Year(EventDate)

The above query of course returns counts for all months and years however you could easily key in on a date range like below if need be.

SELECT Month(EventDate) AS TheMonth, Year(EventDate) AS TheYear, 
SUM(IIF(Lunch, 1, 0)) AS LunchCount,
SUM(IIF(Snacks, 1, 0)) AS SnackCount,
SUM(IIF(Tea, 1, 0)) AS TeaCount
FROM YourTable
WHERE EventDate BETWEEN #1/1/2009# AND #1/31/2009#
GROUP BY Month(EventDate), Year(EventDate)
ORDER BY Month(EventDate), Year(EventDate)

Jakkwylde 2009-08-09 18:53:15

ansaurus

tags:

views:

answers:

Access database query for using month from a date

related questions