Count in a query

L

Loretta

I need to do a count in a query or form that I can use for further
calculations. I have the query done but it counts the ID and then multiplys
it by the count. So if I have three records it reports it as 9, 2two records
as 4, four records as 12.
This is a select query. I tried to attach and send the file but it was
refused.
 
W

Wayne Morgan

You can "upload" the query by opening the query in design view then changing
to SQL view. Copy and paste the text of the SQL view into your message.
 
L

Loretta

SELECT DISTINCTROW Count([Alarm responses].ID) AS CountOfID, [Alarm
responses].ID, First(Lookup.Company) AS [First Of Company],
First(Lookup.Address1) AS [First Of Address1], First(Lookup.Address2) AS
[First Of Address2], First([Alarm responses].[Alarm Date]) AS [FirstOfAlarm
Date]
FROM Lookup RIGHT JOIN [Alarm responses] ON Lookup.ID = [Alarm responses].ID
GROUP BY [Alarm responses].ID;
 
W

Wayne Morgan

I created 2 tables with the names and fields you have listed in your query.
I then put in some dummy companies and dummy response dates. Next, I copied
and pasted your SQL into a query (adjusting for the line wrapping in the
news reader). When I opened the query it showed the number of responses for
each company in the CountOfID field.
 
L

Loretta

What could I be doing wrong? I just duplicated what you did and I still come
up with the count multiply itself.
My first Table:
Alarm responses ID Alarm Date
2 1 /31/2005
2 4 /13/2005
2 2 /15/2005
2 6 /12/2005
3 2 /15/2005
3 5 /12/2005
5 6 /1 /2005
15 4 /24/2005
15 6 /18/2005


The second table:
Alarms Initial Renewal Alarm Company Address1 Address2 Exempt Check Type
Paid Permit PermitPrint
No Yes B02 99 Main Street Bldg 99 Main Street
No 1 Fire No 1 No
No Yes B24 A-1 Auto of Somersworth 208 Route 108
No 1 Police Yes 2 No
No Yes 0272 Advance America 110 Tri-City Plaza
No 1 Police Yes 3 No
No Yes 0270 Aikens, Margaret 34 Blackwater Road
Yes 1 Police No 4 No
No Yes 072 18 12 Laurel Lane
No 1 Police No 5 No
No Yes 0256 American Discount Outlets dba Office Price Outlet Store
340 High Street No 1 Police/Fire Yes 6 No
No Yes O0193 Blue Dolphin Screenprint 100 Main Street, 4th Floor
No 1 Police Yes 15 No


I should get a count of 4 for permit #2, acount of 2 for permit#3, 1 for #5
and 2 for #15. In stead I get 16 for permit #2, 4 for #3, 1 for #5 and 4 for
#15.

You said your counts came out correct. Any suggestions?
Thank You,
Loretta

Wayne Morgan said:
I created 2 tables with the names and fields you have listed in your query.
I then put in some dummy companies and dummy response dates. Next, I copied
and pasted your SQL into a query (adjusting for the line wrapping in the
news reader). When I opened the query it showed the number of responses for
each company in the CountOfID field.

--
Wayne Morgan
MS Access MVP


Loretta said:
SELECT DISTINCTROW Count([Alarm responses].ID) AS CountOfID, [Alarm
responses].ID, First(Lookup.Company) AS [First Of Company],
First(Lookup.Address1) AS [First Of Address1], First(Lookup.Address2) AS
[First Of Address2], First([Alarm responses].[Alarm Date]) AS
[FirstOfAlarm Date]
FROM Lookup RIGHT JOIN [Alarm responses] ON Lookup.ID = [Alarm
responses].ID
GROUP BY [Alarm responses].ID;
Click to expand...
Click to expand...
 
L

Loretta

I got it!!!!
Thank You,
Loretta
Loretta said:
What could I be doing wrong? I just duplicated what you did and I still
come up with the count multiply itself.
My first Table:
Alarm responses ID Alarm Date
2 1 /31/2005
2 4 /13/2005
2 2 /15/2005
2 6 /12/2005
3 2 /15/2005
3 5 /12/2005
5 6 /1 /2005
15 4 /24/2005
15 6 /18/2005


The second table:
Alarms Initial Renewal Alarm Company Address1 Address2 Exempt Check Type
Paid Permit PermitPrint
No Yes B02 99 Main Street Bldg 99 Main Street
No 1 Fire No 1 No
No Yes B24 A-1 Auto of Somersworth 208 Route 108
No 1 Police Yes 2 No
No Yes 0272 Advance America 110 Tri-City Plaza
No 1 Police Yes 3 No
No Yes 0270 Aikens, Margaret 34 Blackwater Road
Yes 1 Police No 4 No
No Yes 072 18 12 Laurel Lane
No 1 Police No 5 No
No Yes 0256 American Discount Outlets dba Office Price Outlet Store
340 High Street No 1 Police/Fire Yes 6 No
No Yes O0193 Blue Dolphin Screenprint 100 Main Street, 4th Floor
No 1 Police Yes 15 No


I should get a count of 4 for permit #2, acount of 2 for permit#3, 1 for
#5 and 2 for #15. In stead I get 16 for permit #2, 4 for #3, 1 for #5 and
4 for #15.

You said your counts came out correct. Any suggestions?
Thank You,
Loretta

Wayne Morgan said:
I created 2 tables with the names and fields you have listed in your
query. I then put in some dummy companies and dummy response dates. Next,
I copied and pasted your SQL into a query (adjusting for the line wrapping
in the news reader). When I opened the query it showed the number of
responses for each company in the CountOfID field.

--
Wayne Morgan
MS Access MVP


Loretta said:
SELECT DISTINCTROW Count([Alarm responses].ID) AS CountOfID, [Alarm
responses].ID, First(Lookup.Company) AS [First Of Company],
First(Lookup.Address1) AS [First Of Address1], First(Lookup.Address2) AS
[First Of Address2], First([Alarm responses].[Alarm Date]) AS
[FirstOfAlarm Date]
FROM Lookup RIGHT JOIN [Alarm responses] ON Lookup.ID = [Alarm
responses].ID
GROUP BY [Alarm responses].ID;
Click to expand...
Click to expand...
Click to expand...
 
Top