asked on

Summarize by Date in Access Query

Hi all,

I am doing a query (please see picture for where I stand)

What I really want to do is summarize origin & destination columns data by each date

There are 12 unique origin and destin for each date

eg:

Trip Date ORIGIN DESTIN

2/07/2012 TO VA
2/07/2012 TO CA
2/07/2012 TO ED
2/07/2012 TO WI
2/07/2012 TO OTHER
2/07/2012 ML VA
2/07/2012 ML CA
2/07/2012 ML ED
2/07/2012 ML WI
2/07/2012 ML OTHER
2/07/2012 REST REST

How do I go on to summarize the data by date from this point on?

Any help in correct step is much appreciated!
Temp.PNG

als315

Can you show expected result?

Flyster

If you're looking for a count of each unique value, you can use this:

SELECT YourTable.[Trip Date], YourTable.[ORIGIN], YourTable.[DESTIN], Count(YourTable.ORIGIN) AS CountOfORIGIN
FROM YourTable
GROUP BY YourTable.[Trip Date], YourTable.[ORIGIN], YourTable.[DESTIN];

Flyster

Shanan212

ASKER

The expected result is this

2/07/2012 TO VA
2/07/2012 TO CA
2/07/2012 TO ED
2/07/2012 TO WI
2/07/2012 TO OTHER
2/07/2012 ML VA
2/07/2012 ML CA
2/07/2012 ML ED
2/07/2012 ML WI
2/07/2012 ML OTHER
2/07/2012 REST REST

My current results are in the screenshot

Flyster, its not nessarily unique value as for each date, there is are multilple "TO" as origin and "VA" available (example)

ASKER CERTIFIED SOLUTION

Flyster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shanan212

ASKER

That works! Thanks!