Hey everyone, I've searched around on how to use count, but either I'm misunderstanding the information I'm getting (still kinda new to access) or it doesn't apply to what I'm trying to do. Here's my issue.

I have a large database that consists of service calls. Each time a call is made information is entered. The info fields are.... "Date, Common Name, Location, Type". For the time being I'm only concerned with the info of Common Name and Location. Here is an example of the database data....

DATE COMMON NAME LOCATION TYPE
2/17/2000 BALLY'S 2701 BURGLARY/AUTO/CONVEY
3/8/2000 LONGHORN 2375 BURGLARY/AUTO/CONVEY
3/19/2000 URGENT FAMILY C 2337 BURGLARY/BUSINESS
3/26/2000 COCO LOCO'S 2101 BURGLARY/BUSINESS
3/28/2000 BALLY'S 2701 BATTERY/SIMPLE WO/IN
3/29/2000 COCO LOCOS 2101 BATTERY/AGGRAVATED
4/4/2000 LONGHORN 2375 BURGLARY/AUTO/CONVEY
4/10/2000 BALLY'S 2701 BURGLARY/BUSINESS
4/14/2000 BALLY'S 2701 BURGLARY/AUTO/CONVEY
4/14/2000 BALLY'S 2701 BURGLARY/AUTO/CONVEY
4/25/2000 BALLY'S 2701 BURGLARY/AUTO/CONVEY
4/27/2000 PROMENADE WEST 2300 ASSAULT/THREAT

What I want eventually is a list of each "Common Name" with the "Location" next to it and next to that a number for how ever many service calls made. Right now I have a query set up, Unique Values (in properties) is set to yes, so instead of seeing multiple entries for each "Common Name" I see just one, then the next field in my query is Location, which shows the number next to the "Common Name" (so far so good). Now I'm just having trouble getting access to count how many times a service call has been made. I'm pretty sure that I need to use the "Count" function however I"m having trouble there, can anyone help?

Eventually I want to be able to do further analysis like percentage of calls in given date ranges, what percentage of calls are "BURGLARY/BUSINESS" etc. however I think once I figure out COUNT the rest will fall into place.

Thanks
-Rob

Don't use the "Unique Values" - unclick that.

Start a new query and add your table. Then add the [COMMON NAME] and LOCATION fields to the grid. Then add the [DATE] field.

Next turn this query into a Totals query; from the menu click View, Totals or click on the Sigma icon. This adds the Total: row to the query grid. Change the [DATE] columns Total: row to Count.

Something to remember as you move on to your other goals - if your goal is a report then you can do a lot of this in the report - grouping, sorting, summarizing.

Thanks for the help, it worked well. You are right though, I should probably be using reports to do this, I'm not sure how to do all of this in reports, but I'm sure a little fumbling around will solve the problem.

This first query won't show much performance difference whether you do the sorting/grouping/summing in the query or the report but your future ones look like they could benefit from doing this in the report.

One common gotcha in report design is failure to use/view the Sorting and Grouping settings (use View, Sorting and Grouping from the menu). They override settings in the query so occasionally someone might not understand why the sort order they set in the query is not affecting the report's results. Its because there is an overriding setting in the Report's Grouping and Sorting. Just a FYI...

Just curious, how does the count of date give me what i'm looking for? And also how did just doing the count of date give me the grouping that I wanted? I'm trying to find out the logic so I can apply it to other things but it's not making sense to me.

The key is that you are using the "Group By" keyword for [COMMON NAME] and LOCATION. The query retrieves all the records and then groups them by unique values of [COMMON NAME] and LOCATION.

Then within each group it counts the number of records - I just used the [DATE] field to count; any (non-Null) field would have done.

If you convert the query back to a non-Totals type query then you lose the ability to group by and to do aggregate functions (like Sum, Count, Avg, etc.).

There are other ways to accomplish your goal; the Totals query is one of the simpler ways to do it.

Thanks for all the help so far. I'm working on a similar project now, I should probably be doing it in reports like you say but I'm not sure I can ( don't know how). The main problem I'm running into is that I want to use the grouping I have when I use the totals button, but some of the columns I don't want grouped. When I don't select anything from the dropdown of total I get an error. How can I use the totals querry option without selecting grouping for all the rows?

Here's a sample of what I'm working on.

ID Categories Call_Number Complaint Actual_Incid_Location Internal_ESN Tract Date_Received Time_Received Disposition
1 POL. 01-00001347 1 8400 W OAKLAND PARK BLVD C146 7 1/5/2001 10:25:46 PM 1/5/2001 10:25:46 PM C
2 POL. 01-00007389 1 2451 N UNIVERSITY DR C137 4 1/26/2001 7:51:04 PM 1/26/2001 7:51:04 PM V
3 POL. 01-00007463 1 10000 SUNSET STRIP P049 6 1/27/2001 12:12:19 AM 1/27/2001 12:12:19 AM C
4 POL. 01-00014109 1 8200 SUNSET STRIP S019 3 2/19/2001 7:38:04 PM 2/19/2001 7:38:04 PM M
5 POL. 01-00014223 1 3300 N UNIVERSITY DR C050 7 2/20/2001 8:52:02 AM 2/20/2001 8:52:02 AM B
6 POL. 01-00020265 1 11900 W SUNRISE BLVD C103 12 3/13/2001 1:33:18 AM 3/13/2001 1:33:18 AM A


ID Categories Call_Number Complaint Actual_Incid_Location Internal_ESN Tract Date_Received Time_Received Disposition
16791 POL. 01-00023166 15 6800 SUNSET STRIP S190 3 3/23/2001 1:57:32 PM 3/23/2001 1:57:32 PM K
16792 POL. 01-00023160 15 3400 NOB HILL RD C152 8 3/23/2001 1:52:59 PM 3/23/2001 1:52:59 PM K
16793 POL. 01-00023192 15 2000 N PINE IS S028 4 3/23/2001 3:17:36 PM 3/23/2001 3:17:36 PM K
16794 POL. 01-00023187 15 9100 NW 21ST MNR S036 4 3/23/2001 3:07:10 PM 3/23/2001 3:07:10 PM K
16795 POL. 01-00023154 15 2000 N PINE IS S028 4 3/23/2001 1:48:43 PM 3/23/2001 1:48:43 PM K
16796 POL. 01-00024639 15 8200 SUNSET STRIP S019 3 3/28/2001 3:20:38 PM 3/28/2001 3:20:38 PM K



The examples taken from two different areas of the table to show you different complaint numbers. What these are are calls for services to a police department. The data I'm intersted in is the Actual Location, Date Received, Disposition and Complaint. I only care about entries of Disposition "A" so in my query I use "A" under criteria. I uncheck the show box because they are all "A" no need to show the row. The date doesnt matter for now, but I may need to break it up by dates I.E. 2001-2002 at a later time so I don't need to use that at the moment. Now comes the more difficult part. Complaint is a number that corresponds to a type of crime. What I need is a count for all the different types of crime for each address.

Here are the complaint numbers with what i'm looking for

Violent: 5, 24, 31AA, 31AS, 31BA, 31BS, 33, 34, 35, 41A, 41S

Property: 10, 21B, 21C, 21R, 21V, 25A, 30G, 30P, 52, 53, 64, 65

Nonviolent sex: 43, 47

So my idea was to add actual_incid_location , disposition and a few complaint columns to my query. Disposition would be hidden and is just used to weed out all of the other records i'm not interested in. Actual incident location would be set to group by (from when you make it a totals query) so that I only get 1 row for each unique address. I would then have 3 columns that counted each of the types of crime. Under the criteria I would just only use the numbers I would be intersted in (not sure how to make it use any of those numbers instead of requiring all of them, like make it "or" instead of "and"). This should give me a count for each of the types of crime at each individual address.

I know there is something wrong with how I'm setting this up so any advice would be helpful.

Well I managed to figure it out after hours of messing around =D.

What I did was query the data, just a regular query, no totals. I had the Actual_incid_location, next was complaint, then disposition. Under complaint's criteria/or area I just typed in each number for the category of crimes down the row (thats how I get the Or ! duh) and under disposition I had just "A", disposition was not shown on the querry.

After i had that data I created a new query where I used totals. I grouped by the actual location and counted the complaint column. The result was the number of complaints at the given location, grouped by location. I created these two querry sets for all four different types of crime. Not the most efficient way to do it probably, but none the less it worked. If there was a better way to do this let me know, I don't need it for the project but it'd always be nice to know multiple ways to do things.

-Rob