Counting Occurences of an ID (1 Viewer)

[bbulla]

Hi,

I have a Table A that is a list of ID's. In table B, each ID (from A) could appear mutliple times, or not at all.

How do I get a count of how many times each ID in Table A appears in Table B? ie. 0 or 1 or 5 or whatever

Thanks,

 

[pr2-eugin]

Something like..

SELECT TableA.ID, Count(TableB.tableA_CommonID) As CountOfID
FROM TableA INNER JOIN TableB ON TableA.ID = TableB.tableA_CommonID
GROUP BY TableA.ID;

But if you want to include the TableA.ID that are not in TableB you need to change the Inner join to LEFT JOIN, and make sure you capture the Nulls to 0.

SELECT TableA.ID, [COLOR=Red][B]Nz([/B][/COLOR]Count(TableB.tableA_CommonID)[COLOR=Red][B], 0)[/B][/COLOR] As CountOfID
FROM TableA [COLOR=Red][B]LEFT JOIN[/B][/COLOR] TableB ON TableA.ID = TableB.tableA_CommonID
GROUP BY TableA.ID;

More info on JOIN : http://msdn.microsoft.com/en-us/library/office/bb208894(v=office.12).aspx

 

[plog]

I don't think pr2's solution is quite right. It's not going to give you any 0 values (those in A but not in B) and it needs a GROUP BY clause (without it, it's not even going to run). You need a LEFT JOIN (http://www.w3schools.com/sql/sql_join_left.asp) AGGREGATE (http://www.599cd.com/tips/access/aggregate-query/) query.

In the query design view of access, bring in both tables and link them by the ID. Right click on the line that joins them and change the join to the option that selects all from Table A. That makes it a LEFT JOIN query.

Then, in the Ribbon, click the Sigma (aka Summation) symbol. That makes it an AGGREGATE query. Next, in the bottom section bring down the ID from Table A and the ID from Table B. Underneath the ID from Table A, select the option that says 'Group By', underneath the ID from Table B, select the option that says 'Count'. Run the query and you have your data.

 

[pr2-eugin]

I don't think pr2's solution is quite right.

Thanks plog, and Sorry bbulla. I quickly posted the CODE, while having some other thread in the Queue. By the time I came back to edit with reference, plog nailed it.

Hope it did not waste anyone's time !

 

[bbulla]

Excellent. Thanks for the step-by-step Plog.

I was then able to summarize that and created another query to show the count of each count: ie. total # of 0 occurences, etc.

Thanks for the help!