473,326 Members | 2,175 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Count and Group By two fields in a Query

58
How can I Count the number of Customers that a Sales Person had in a City X? The idea is to Group By [Location Name] and Sub Group By [Sales Person], and then Count the rest of the fields. This is the scenario:

[Location Name]……[Sales Person]…….[CountOfCustomers]…..etc

………CITY1
……………………….......Steve K………………………..25
…………………………….John M………………………..15
…………………………...Dianne L……………………….26
……..CITY 2
………………………......Scott P………………………….36
…………………………..Steve N.……..………………….24
…………………………Christian G……………...……….10

So far, I can Count (in this case Customer Name or any other field) if I Group By only one field, for instance:

Code:
SELECT [Sales Table].[Location Name], Count([Sales Table].[Customer Name]) AS [CountOfCustomers], Count([Sales Table].[Appointments]) AS [CountOfAppointments]
FROM [Sales Table]
GROUP BY [Sales Table].[Location Name];

The problem I have is, when I Group By two fields, the Count is 1 when the record is duplicated.
Any help will be greatly appreciated.

Thank you in advance.
plaguna
Oct 16 '08 #1
1 4214
Hi

Can you paste the SQL code you have problems with and example of the tables fields + records that cause the problem?

Regards
Emil
Oct 17 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Anne | last post by:
Hi I have got a table with some sales transactions. It has got 3 fields 1) Ordernumber 2) Item number 3) Shippingdate Now I want to make ONE (not two) query that shows how many different...
2
by: Anne Heddal | last post by:
In the first line I'm trying to count the the unqiue values of the ID column, but I'm getting syntax error, any idea how to format the distinct count? SELECT Count(test.ID) AS IDCOUNT FROM...
2
by: cefrancke | last post by:
I can't seem to find a straight answer for my specific issue. Any help would be appreciated. I would like to count the various items in a table where the fields have a 'group' relationship. I...
1
by: sunilkeswani | last post by:
Hi I am still new to access. I want to know how i can build a query which can display results from 4 different columns/fields Like. Field1 Field2 Field3 Field4 1 2 1 ...
2
by: john | last post by:
I have a table with 5 fields. In a query I would like to make a count for every unique combination of field 2,3, and 4. I fiddled with the count and dcount option, but can't get it to work for more...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
1
by: zafm86 | last post by:
Hi everyone! I'm sure my problem is pretty easy to solve but I've been working on it for a long and my my brain is not working correctly anymore. I'm working with an AS400 and I mhave to do an...
4
by: vincibleman | last post by:
Howdy all, Working my way into SQL from Access. Think I might have the hang of the basics, but would really appreciate a sanity check. The stored procedure listed below works, but I can't help...
14
by: zufie | last post by:
I have to create a QA report regarding callers calling into a phone hotline. The report consists of many checkboxes such as: Did the IBCCP agency contact you? Yes/NO How many days passed...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shζllξpτpο 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.