CODEWITHSUNDEEP
sqlexcelvbams-access
OliEshmade
OliEshmade

Reputation: 121

MS Access SQL - Count for each record in a recordset

I use the query to return all of the values from one table, along with a count of votes from one table, and a count of comments from another.

I have got it to work for loading an individual record

DCount('[query_id]','[comments]','[query_id]=" & Target & "')

However if I add something similar to the query that returns every query_id, the count shows the same for them all.

Is there a different function I can use than DCount to achieve this?

My previous issue was using count, and as the query had non unique data it was counting all votes from a person (i.e if I had made 6 votes, the count would show as 6 for any record my user id was attached to).

Upvotes: 0

Views: 718

Answers (2)

OliEshmade
OliEshmade

Reputation: 121

I have managed to get the results I expected by using sub select statements in the SELECT clause

SELECT issues.query_id, issues.query_raised_by, issues.query_raised_date, issues.query_summary, issues.query_status, issues.query_status_date, issues.query_detail, issues.query_response
(SELECT COUNT(*) FROM vote WHERE query_id = issues.query_id) AS voteCount, 
(SELECT COUNT(*) FROM comments WHERE query_id = issues.query_id) AS commentCount

Upvotes: 1

Gustav
Gustav

Reputation: 55981

You may be after something like:

DCount("*","[comments]","[person_id] = " & [Target] & "")

where [Target] is the field holding the PersonId of the other table.

Upvotes: 1

Related Questions