Use Access (SQL) query to group (histogram) data by date with multiple fields

Question

I'm looking to try and put together a histogram for returns and group it by division, but just for an example, let's say we have drivers that are prone to crashing cars. I want to know how many times each driver has crashed a car in each quarter so I can see if they're getting better worse at the whole driving thing.

Here's a table of example data:

crash_date  driver  car
10/26/2013  Jane    Hundo
11/3/2013   Bob     Hundo
11/6/2013   John    Ferd
11/12/2013  John    Superoo
11/12/2013  Bob     Ferd
1/20/2014   John    Superoo
3/5/2014    John    Superoo
3/17/2014   Bob     Superoo
5/24/2014   Bob     Hundo

What I'd like to get is the number of crashes organized by driver and quarter:

Quarter Bob     Jane    John
Q4Y13   2       1       2
Q1Y14   1       0       2
Q2Y14   1       0       0
Q3Y14   0       0       0

Using the info from this question got me this far:

SELECT  crash_year as Year, Count(*) as Bob
FROM    (SELECT  year(crash_date) as crash_year
        FROM    (SELECT DISTINCT crash_date, driver
                FROM            crash_table
                WHERE           driver = "Bob") AS subQuery1) AS subQuery2
GROUP   BY crash_year
ORDER   BY crash_year;

Which gives me this result:

Year    Bob
2013    2
2014    2

I haven't had much more luck with my searches up to this point, but I'll keep poking around.

Have you tried ms-access pivot? stackoverflow.com/questions/16546305/pivoting-data-in-ms-access — Charlesliam
– Charlesliam, Commented Jul 4, 2014 at 2:58

Brad · Accepted Answer · 2014-07-04 04:09:31Z

1

This will work with a dynamic number of drivers

TRANSFORM Nz(Count(D.driver),0) AS CountOfdriver
SELECT "Q" & DatePart("q",[crash_date]) & "Y" & Year([crash_date]) AS Quarter
FROM Table1 D
GROUP BY "Q" & DatePart("q",[crash_date]) & "Y" & Year([crash_date])
PIVOT D.driver;

output looks like this

Quarter Bob Jane    John
Q1Y2014 1   0       2
Q2Y2014 1   0       0
Q4Y2013 2   1       2

answered Jul 4, 2014 at 4:09

Brad

12.4k4 gold badges51 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan B Over a year ago

That's just about perfect!

Gordon Linoff · Accepted Answer · 2014-07-04 21:58:51Z

0

Perhaps this does what you want:

select datepart("yyyy", crash_date) as yr, datepart("q", crash_date),
       sum(iif(driver = 'Bob', 1, 0)) as Bob,
       sum(iif(driver = 'Jane', 1, 0)) as Jane,
       sum(iif(driver = 'John', 1, 0)) as John
from crash_table
group by datepart("yyyy", crash_date) , datepart("q", crash_date)
order by 1, 2;

edited Jul 4, 2014 at 21:58

answered Jul 4, 2014 at 2:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Brad Over a year ago

Is quarter a function in access? I get sub or function not defined

Collectives™ on Stack Overflow

Use Access (SQL) query to group (histogram) data by date with multiple fields

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related