Get the number of records which have each value of an FK

[Dan Neely]

I have an lnkItemState table and want to get the number of records in
in that have each value of stateId.


if lnkItemState, and lkupState contained the following:
lnkItemState
itemID StateID ...
1 1
2 1
3 1
4 2
5 2

lkupState
StateID ...
1
2
3

I'd like my query to return:
StateID ItemCount
1 3
2 2
3 0

How would I write this?

 

[Guest]

Using a Group By query, something like

Select StateID , Count(itemID ) As CountOfItems From TableName
Group By StateID

 

[Dan Neely]

Using a Group By query, something like

Select StateID , Count(itemID ) As CountOfItems From TableName
Group By StateID

This doesn't fully meet my needs because it doesn't return a row where
CountOfItems = 0.

Using the sample data from my original post what I'm getting back is
this:
StateID ItemCount
1 3
2 2

but what I need is this:
StateID ItemCount
1 3
2 2
3 0

 

[John Spencer]

If you want zero counts for the states that are not in the lnkItemState
table then you need to include the lkUpState table and JOIN it to the
lnkItemState table.

SELECT lkUpState.StateID, Count (lnkItemState.ItemID) as Count
FROM lkUpState LEFT JOIN lnkItemState
ON lkUpState.StateID = lnkItemState.StateID
GROUP BY lkUpState.StateID



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I have an lnkItemState table and want to get the number of records in
in that have each value of stateId.


if lnkItemState, and lkupState contained the following:
lnkItemState
itemID StateID ...
1 1
2 1
3 1
4 2
5 2

lkupState
StateID ...
1
2
3

I'd like my query to return:
StateID ItemCount
1 3
2 2
3 0

How would I write this?

Create a Query based on the lnkItemState table (or join it to the table with
state names if you want the user to see "Alabama", "Alaska", etc. rather than
an uninformative number); select the State or StateID field and the ItemID
field.

Change the query to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). Leave the default Group By on the newly appearing Totals
row for StateID, and change it to Count on the ItemID field.

John W. Vinson [MVP]