• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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
0
Shanan212
Asked:
Shanan212
  • 2
  • 2
1 Solution
 
als315Commented:
Can you show expected result?
0
 
FlysterCommented:
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
0
 
Shanan212Author Commented:
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)
0
 
FlysterCommented:
The code I provided will give you that result with the addition of a count field which tells you how many such entries exists. If you don't need the count, you can use this:

SELECT YourTable.[Trip Date], YourTable.ORIGIN, YourTable.DESTIN
FROM YourTable
GROUP BY YourTable.[Trip Date], YourTable.ORIGIN, YourTable.DESTIN;
0
 
Shanan212Author Commented:
That works! Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now