Count Query

M

Missy

I have one table and I want to bring a count based on the section and
subsection. So basically I want to know for each dealer if a technician
called about the same section and subsection which I have combined the fields
in the query, how many calls were there. It is working for some, but for the
most part the result is counting 1 and showing several lines for the same
section and subsection. Below is the SQL to help:

SELECT DISTINCT [Tech Names No Dups]!REGION & "-" & [Tech Names No
Dups]!DEALERCODE AS RegDlr, [Tech Names No Dups].SvcDst, [Tech Names No
Dups]!TECHLASTNAME & "," & [Tech Names No Dups]!TECHFIRSTNAME AS Name, [Tech
Names No Dups]![SECTION] & "-" & [Tech Names No Dups]![SUBSECTION] AS
[Section-Subsection], Count([Tech Names No Dups]![SECTION] & "-" & [Tech
Names No Dups]![SUBSECTION]) AS [Section-Subsection1]
FROM [Tech Names No Dups]
GROUP BY [Tech Names No Dups]!REGION & "-" & [Tech Names No
Dups]!DEALERCODE, [Tech Names No Dups].SvcDst, [Tech Names No
Dups]!TECHLASTNAME & "," & [Tech Names No Dups]!TECHFIRSTNAME, [Tech Names No
Dups]![SECTION] & "-" & [Tech Names No Dups]![SUBSECTION], [Tech Names No
Dups].DATECREATED, [Tech Names No Dups].KEYWORD
HAVING ((([Tech Names No Dups].DATECREATED)>#11/30/2006#) AND (([Tech Names
No Dups].KEYWORD) Like "*Immob*"))
ORDER BY [Tech Names No Dups]!REGION & "-" & [Tech Names No
Dups]!DEALERCODE, [Tech Names No Dups].SvcDst, [Tech Names No
Dups]!TECHLASTNAME & "," & [Tech Names No Dups]!TECHFIRSTNAME;


Any help would be greatly appreciated. I am at a loss at this point. I
sure it is something simple I am just not seeing.
 
M

Missy

No, I did not figure it out it is still giving me multiple lines for a tech
that called about the same section and subsection
 
K

KARL DEWEY

Try removing , [Tech Names No Dups].KEYWORD

--
KARL DEWEY
Build a little - Test a little


Missy said:
No, I did not figure it out it is still giving me multiple lines for a tech
that called about the same section and subsection

Missy said:
I have one table and I want to bring a count based on the section and
subsection. So basically I want to know for each dealer if a technician
called about the same section and subsection which I have combined the fields
in the query, how many calls were there. It is working for some, but for the
most part the result is counting 1 and showing several lines for the same
section and subsection. Below is the SQL to help:

SELECT DISTINCT [Tech Names No Dups]!REGION & "-" & [Tech Names No
Dups]!DEALERCODE AS RegDlr, [Tech Names No Dups].SvcDst, [Tech Names No
Dups]!TECHLASTNAME & "," & [Tech Names No Dups]!TECHFIRSTNAME AS Name, [Tech
Names No Dups]![SECTION] & "-" & [Tech Names No Dups]![SUBSECTION] AS
[Section-Subsection], Count([Tech Names No Dups]![SECTION] & "-" & [Tech
Names No Dups]![SUBSECTION]) AS [Section-Subsection1]
FROM [Tech Names No Dups]
GROUP BY [Tech Names No Dups]!REGION & "-" & [Tech Names No
Dups]!DEALERCODE, [Tech Names No Dups].SvcDst, [Tech Names No
Dups]!TECHLASTNAME & "," & [Tech Names No Dups]!TECHFIRSTNAME, [Tech Names No
Dups]![SECTION] & "-" & [Tech Names No Dups]![SUBSECTION], [Tech Names No
Dups].DATECREATED, [Tech Names No Dups].KEYWORD
HAVING ((([Tech Names No Dups].DATECREATED)>#11/30/2006#) AND (([Tech Names
No Dups].KEYWORD) Like "*Immob*"))
ORDER BY [Tech Names No Dups]!REGION & "-" & [Tech Names No
Dups]!DEALERCODE, [Tech Names No Dups].SvcDst, [Tech Names No
Dups]!TECHLASTNAME & "," & [Tech Names No Dups]!TECHFIRSTNAME;


Any help would be greatly appreciated. I am at a loss at this point. I
sure it is something simple I am just not seeing.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Permission in Outlook 0
Find duplicates query not working..... 1
eliminate duplicates 2
update query - key violation error 2
Deleting Duplicate Records, Keeping First Occurance 3
Please help with Query "dups" 7
Updateable Query Problem 9
Exceeds amount of items 7

Top