Counting records

[mike]

Hi Guys
I Have Three tables
TblEntries
FdEntryID
FdEntryDate
FdExhibitorID
FdClassID
FdEntryScore

TbClasses
FdClassID
FdClassDivision
FdClassDescription
FdClassLetter

TblExhibitor
FdExhibitorID
FdExhibitorFullName
FdExhibitorContact

I am trying to get a query to show a count of the number of Exhibitor
entries in each class, but I also need to show the classes that have no
entries (show a count of 0). I have been using a query with Count against
the FdExhibitorsID in TblEntries but if a class has no entries I cannot get
it to show in the results like this:-
FdDivClassLetter FdClassDescription Count
A Flowers 5
B Pictures 10
C Woodwork 0
D Art 9
I have been trying to use the IIF function but I just cannot get the syntax
correct. Can some one assist or is it just not possible in one query or am
I approaching this from the wrong direction?
TIA
Mike

 

[Bob Quintal]

Hi Guys
I Have Three tables
TblEntries
FdEntryID
FdEntryDate
FdExhibitorID
FdClassID
FdEntryScore

TbClasses
FdClassID
FdClassDivision
FdClassDescription
FdClassLetter

TblExhibitor
FdExhibitorID
FdExhibitorFullName
FdExhibitorContact

I am trying to get a query to show a count of the number of
Exhibitor entries in each class, but I also need to show the
classes that have no entries (show a count of 0). I have been
using a query with Count against the FdExhibitorsID in TblEntries
but if a class has no entries I cannot get it to show in the
results like this:- FdDivClassLetter FdClassDescription Count
A Flowers 5
B Pictures 10
C Woodwork 0
D Art 9
I have been trying to use the IIF function but I just cannot get
the syntax correct. Can some one assist or is it just not
possible in one query or am I approaching this from the wrong
direction? TIA
Mike

You just need a totals query.

Start with the tblClasses table, then add the TblEntries table. If a
relationship line shows up between the two tables, double click it,
and change the relation type to show all records from TblClasses and
only records that match from the TblEntries table. If the relations
line doesn't show automatically, drag and drop the
TblClasses.FdClassID onto the TblEntries.FdClassId, then double click
as above.

Now double click the FdDivClassLetter and FdClassDescription from
TblClasses and type TheCount: nz(count(TblEntries.FdEntryID),0) as
the third field name.

Click on the Totals button in the toolbar, and change the Group BY
entry in the totals row to Expression underneath the TheCount field
you typed in.

Save and run.