Grouping, sorting and counting correctly.

[CrakD]

I have a db, in which there are several employees and on any given day they get an item abbreviation. Like this


Date | Employee Name | Count|
06/1 | George | BEV |
06/2 | Chris | ICE |
06/3 | George | BEV |
06/4 | George | ICE |

What I want is to be able to count in a month, the times george got a BEV, and an ICE.

something like this
employee | Item | Number|
George | BEV | 2 |
George | ICE | 1 |

This way I can make a chart. I hope someone understands this..i know I can do this in Excel..but i'm hoping to do a one button kinda thing..in Access...if anyone has any ideas plz let me know thanks.

 

[lespaul]

First off, you need to change your field name from COUNT to something else!

do you have a table that lists all the items in the COUNT field?



Leslie

 

[CrakD]

It's not actually named any of those fields..i'm just using that as an example...It's actually a referral system..I didn't want to reveal to much...but...here's how it works...I need to be able to track referrals that nurses give the clinic. People call..they give basic information about the patient and what type of therapy is needed. So for instance on 6/22/04 Dr. Seuss's nurse will call. The patient will be recommended for Speech Therapy. And then Dr. Soandso's will call same day and refer another patient for Registered dietician. I'd like to be able to show a graph.

So..
Date_of_Call | Doc_ID | Discipline |
06/24/2004 | Dr. Seuss | ST |
06/24/2004 | Dr. Soandso| RD |
06/25/2004 | Dr. Seuss | OT |

for the month of June:
Doc_ID | Discipline | number of instances(made up) |
Dr. Seuss | ST | 1 |
Dr. Seuss | OT | 1 |
Dr. Soandso| RD | 1 |

all of this is stored in the table: referrals
and the patient information is stored in another table. The discipline is actually a lookup value.
Does this help?

 

[PHV]

Something like this ?
SELECT Format([Date],"yyyy-mm") As [Month], [Employee Name], [Count], Count(*) As CountOf
FROM theTable
GROUP BY Format([Date],"yyyy-mm") As [Month], [Employee Name], [Count];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

After seeing the complementary post:
SELECT Format(Date_of_Call,"yyyy-mm") As [Month], Doc_ID, Discipline, Count(*) As [number of instances]
FROM referrals
GROUP BY Format(Date_of_Call,"yyyy-mm"), Doc_ID, Discipline;
If you're only interested by June:
SELECT Doc_ID, Discipline, Count(*) As [number of instances]
FROM referrals
WHERE Format(Date_of_Call,"yyyy-mm")="2004-06"
GROUP BY Doc_ID, Discipline;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244