Totals / count Query ???

[dan_s]

Hi,

I have a table showing members of a club.

I need to show how many members use each particular mode of transport.

Dan

 

[neileg]

Not enough info!

You need to tell us about your tables and the data.

 

[dan_s]

The table holds data on members...

Name, DOB, etc etc.

There is a Lookup for mode of transport and there are 8 to choose from.

I need to count how many of each mode of transport there are.

Thanks in advance

Dan

 

[Brianwarnock]

So data is
Fred car
bill bus
joe car
etc

You need a query with 8 fields of the type
CountofTransport1: Sum(iif(fldTransport="transport1",1,0))

Brian

 

[dallr]

If you structure is similar to this :
tbl_Members
MemberId (PK)
TransportID (FK)
FirstName
LastName
DOB

tbl_Transport
TransportID (PK)
TransportType

If I understand your requirements correctly you can use.

SELECT COUNT(M.TransportID) As RecordCount, T.TransportType
FROM Tbl_Members M INNER JOIN tbl_Transport T
GROUP BY T.TransportType

Dallr

 

[Sum Guy]

Or if you are doing it in a report you could add to the footer:

=Count([TransportID]="Bus")

 

[dan_s]

Thanks very much for the replies!

There is only one table, and it can't really be changed.

Brian...
So that would be:

Field: Car

Criteria: CountofTransport1: Sum(iif(fldTransport="transport1",1,0))

(?)

Sorry for the confusion - I'm fairly new to Access!

Thanks

Dan

 

[Brianwarnock]

No its not entered in the criteria its entered in the field row. You are creating new fields, the "criteria" for each sum is in the IIF and thus the Sum effectively becomes a count of that condition.

Brian

 

[dan_s]

Hi,

Sorry for being difficult...

As i understand it, I hould have smething like

CountofBus: Sum(iif(fldUsualTransport="Bus",1,0))

(?)

Correct me if I'm wrong - sorry for the confusion!

Dan

 

[Brianwarnock]

That's correct Dan, the way it works is that for each record in the table one of the 8 fields will reurn a 1 and the rest a 0 then the 1s are summed to give a count.
Don't worry about asking for clarification we all have to start somewhere and I've found that a lot of newcomers are puzzled by the use of Sum to get a count when there is a Count function.

Brian

 

[dan_s]

That's great!
Thanks very much for the help - it's appreciated

Dan