Studying Access Databases

jjs8990 · #1 (**permalink**) 01-26-12, 15:57

In my class were are currently learning the basics of an Access database, and I am having a problem getting it to cooperate. The question is "How many orders per year?"

This is what I've gotten so far:

SELECT COUNT (Orderdate)
FROM Orders

Now obviously that will count all of the order dates, but what I need it to do is group the orders by year and count each group. I would assume that would be:

GROUP BY DATEPART ('YYYY')

However, that does not work. Any suggestions?

Sinndho · #2 (**permalink**) 01-26-12, 16:39

The DatePart() function expects at least 2 parameters: interval and date (both are mandatory):

Code:

DatePart(interval, date[,firstdayofweek, firstweekofyear]])

Try:

Code:

SELECT Count(Orders.Orderdate) AS CountByYear
FROM Orders
GROUP BY DatePart('yyyy', Orders.Orderdate);

healdem · #3 (**permalink**) 01-26-12, 16:55

or take advantage of some of the inbuiolt date tome functions such as year()
group by year (orders.orderdate)

jjs8990 · #4 (**permalink**) 01-26-12, 17:27

Quote:

Originally Posted by Sinndho

The DatePart() function expects at least 2 parameters: interval and date (both are mandatory):

Code:

DatePart(interval, date[,firstdayofweek, firstweekofyear]])

Try:

Code:

SELECT Count(Orders.Orderdate) AS CountByYear
FROM Orders
GROUP BY DatePart('yyyy', Orders.Orderdate);

Thank you! That worked wonderfully! May I ask where you learned this stuff? I am (and my classmates) are having troubles with our teacher not telling us where to find these things.

Sinndho · #5 (**permalink**) 01-26-12, 17:39

Try to learn how to use the Access help features: select a keyword (e.g. "DatePart") and press the F1 key. As far as the syntax is concerned, (almost) everything you need to know is in there. Experience helps too (in my case, almost 30 years as a professional in I.T.).

You're welcome, by the way!

Internet Services

Web Development

Digital Marketing

Consumer Tech