• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • 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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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