Using Count in query

N

NewSysAdmin

I am having trouble with the count function in a parameter query. It returns
all 1s instead of counting the actual records. I believe the reason is
because it is grouping on the other fields and then getting confused when
it's trying to count. When I delete the group by from the fields, I get an
error message. Please help! I've been struggling with this for days.
Please see below SQL code. Thank you so much for any suggestions.

SELECT ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]) AS [RMA Tracking #], [RMA/TR Tracking table].[Problem Code], [RMA/TR
Tracking table].[Two-digit year], Count([RMA/TR Tracking table].[Record ID#])
AS [CountOfRecord ID#]
FROM [RMA/TR Tracking table]
GROUP BY ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]), [RMA/TR Tracking table].[Problem Code], [RMA/TR Tracking
table].[Two-digit year]
HAVING ((([RMA/TR Tracking table].[Problem Code])=[Enter Problem Code]));
 
J

John Spencer (MVP)

The problem is that the count is counting the number of records in the group,
not the the total number of records in the recordset.

You might be able to use the DCOUNT function - but that could be slow.

... DCount("[Record ID#]","[RMA/TR Tracking table]","[Problem Code]=" & [Enter
Problem Code]) as CountOfRecord.
 
N

NewSysAdmin

Thank you for helping me, but this didn't quite work. It gives me an error
when I type in the parameter. Below is my new SQL code. Thanks.

SELECT ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]) AS [RMA Tracking #], [RMA/TR Tracking table].[Problem Code], [RMA/TR
Tracking table].[Two-digit year], DCount("[Record ID#]","[RMA/TR Tracking
table]","[Problem Code]=" & [Enter Problem Code]) AS CountOfRecord
FROM [RMA/TR Tracking table];

John Spencer (MVP) said:
The problem is that the count is counting the number of records in the group,
not the the total number of records in the recordset.

You might be able to use the DCOUNT function - but that could be slow.

... DCount("[Record ID#]","[RMA/TR Tracking table]","[Problem Code]=" & [Enter
Problem Code]) as CountOfRecord.


I am having trouble with the count function in a parameter query. It returns
all 1s instead of counting the actual records. I believe the reason is
because it is grouping on the other fields and then getting confused when
it's trying to count. When I delete the group by from the fields, I get an
error message. Please help! I've been struggling with this for days.
Please see below SQL code. Thank you so much for any suggestions.

SELECT ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]) AS [RMA Tracking #], [RMA/TR Tracking table].[Problem Code], [RMA/TR
Tracking table].[Two-digit year], Count([RMA/TR Tracking table].[Record ID#])
AS [CountOfRecord ID#]
FROM [RMA/TR Tracking table]
GROUP BY ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]), [RMA/TR Tracking table].[Problem Code], [RMA/TR Tracking
table].[Two-digit year]
HAVING ((([RMA/TR Tracking table].[Problem Code])=[Enter Problem Code]));
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Is problem code a number field? or is it a text field?

If the latter then you need to wrap quotes around the value of problem code.

DCount("[Record ID#]","[RMA/TR Tracking table]","[Problem Code]=""" & [Enter
Problem Code] & """") AS CountOfRecord

That is 3 quotes and 4 quotes respectively. If your Problem Code does not
contain apostrophes, then you can use
DCount("[Record ID#]","[RMA/TR Tracking table]","[Problem Code]= '" & [Enter
Problem Code] & "'") AS CountOfRecord

By the way, it helps diagnose problems if you give us the error message. My
solution may not apply since I am guessing at the specific error you are getting.

I am going off line for a week, so if you need further help I suggest you might
start a new thread.
Thank you for helping me, but this didn't quite work. It gives me an error
when I type in the parameter. Below is my new SQL code. Thanks.

SELECT ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]) AS [RMA Tracking #], [RMA/TR Tracking table].[Problem Code], [RMA/TR
Tracking table].[Two-digit year], DCount("[Record ID#]","[RMA/TR Tracking
table]","[Problem Code]=" & [Enter Problem Code]) AS CountOfRecord
FROM [RMA/TR Tracking table];

John Spencer (MVP) said:
The problem is that the count is counting the number of records in the group,
not the the total number of records in the recordset.

You might be able to use the DCOUNT function - but that could be slow.

... DCount("[Record ID#]","[RMA/TR Tracking table]","[Problem Code]=" & [Enter
Problem Code]) as CountOfRecord.


I am having trouble with the count function in a parameter query. It returns
all 1s instead of counting the actual records. I believe the reason is
because it is grouping on the other fields and then getting confused when
it's trying to count. When I delete the group by from the fields, I get an
error message. Please help! I've been struggling with this for days.
Please see below SQL code. Thank you so much for any suggestions.

SELECT ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]) AS [RMA Tracking #], [RMA/TR Tracking table].[Problem Code], [RMA/TR
Tracking table].[Two-digit year], Count([RMA/TR Tracking table].[Record ID#])
AS [CountOfRecord ID#]
FROM [RMA/TR Tracking table]
GROUP BY ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]), [RMA/TR Tracking table].[Problem Code], [RMA/TR Tracking
table].[Two-digit year]
HAVING ((([RMA/TR Tracking table].[Problem Code])=[Enter Problem Code]));
Click to expand...
Click to expand...
Click to expand...
 
N

NewSysAdmin

Thank you so much for guiding me in the right direction. I was able to get
it to count using dcount. I did need to add another field (Problem Code) to
my query and reenter the parameter to get the parameter to work.

John Spencer (MVP) said:
Is problem code a number field? or is it a text field?

If the latter then you need to wrap quotes around the value of problem code.

DCount("[Record ID#]","[RMA/TR Tracking table]","[Problem Code]=""" & [Enter
Problem Code] & """") AS CountOfRecord

That is 3 quotes and 4 quotes respectively. If your Problem Code does not
contain apostrophes, then you can use
DCount("[Record ID#]","[RMA/TR Tracking table]","[Problem Code]= '" & [Enter
Problem Code] & "'") AS CountOfRecord

By the way, it helps diagnose problems if you give us the error message. My
solution may not apply since I am guessing at the specific error you are getting.

I am going off line for a week, so if you need further help I suggest you might
start a new thread.
Thank you for helping me, but this didn't quite work. It gives me an error
when I type in the parameter. Below is my new SQL code. Thanks.

SELECT ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]) AS [RMA Tracking #], [RMA/TR Tracking table].[Problem Code], [RMA/TR
Tracking table].[Two-digit year], DCount("[Record ID#]","[RMA/TR Tracking
table]","[Problem Code]=" & [Enter Problem Code]) AS CountOfRecord
FROM [RMA/TR Tracking table];

John Spencer (MVP) said:
The problem is that the count is counting the number of records in the group,
not the the total number of records in the recordset.

You might be able to use the DCOUNT function - but that could be slow.

... DCount("[Record ID#]","[RMA/TR Tracking table]","[Problem Code]=" & [Enter
Problem Code]) as CountOfRecord.



NewSysAdmin wrote:

I am having trouble with the count function in a parameter query. It returns
all 1s instead of counting the actual records. I believe the reason is
because it is grouping on the other fields and then getting confused when
it's trying to count. When I delete the group by from the fields, I get an
error message. Please help! I've been struggling with this for days.
Please see below SQL code. Thank you so much for any suggestions.

SELECT ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]) AS [RMA Tracking #], [RMA/TR Tracking table].[Problem Code], [RMA/TR
Tracking table].[Two-digit year], Count([RMA/TR Tracking table].[Record ID#])
AS [CountOfRecord ID#]
FROM [RMA/TR Tracking table]
GROUP BY ([RMA/TR Tracking table].[Customer Model Code] & "-" & [RMA/TR
Tracking table].[Record ID#] & "-" & [RMA/TR Tracking table].[Two-digit
year]), [RMA/TR Tracking table].[Problem Code], [RMA/TR Tracking
table].[Two-digit year]
HAVING ((([RMA/TR Tracking table].[Problem Code])=[Enter Problem Code]));
Click to expand...
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

Query works but report has no data 2
Please help with crosstab queries 6
Need to Show Value of Zero 8
Records in a query between a one-many rel. not allowing data entry 3
Copy previous record into form field 0
Create folder and name field value 10
Add a field in a query. Easy. . .so I though. 5
Shared subform table 1

Top