Help with count in 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.
 
J

Jeff Boyce

Loretta

It would help to see your query.

Open the query in design mode. Change the view to the SQL view. Copy the
SQL statement and paste it into your next post.

Regards

Jeff Boyce
<Access MVP>
 
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;
 
J

Jeff Boyce

Loretta

Are you certain that you and Access are using the same definition of the
aggregate function "First"? (I suspect not)

What happens if you only count the IDs? If you can get that portion
running, perhaps you could then "chain" (my terminology) additional queries
by using the output of the first query as part of your source in a second,
and so on.

Good luck

Jeff Boyce
<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;

Jeff Boyce said:
Loretta

It would help to see your query.

Open the query in design mode. Change the view to the SQL view. Copy the
SQL statement and paste it into your next post.

Regards

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Top