How to total the number of people in a field?

[barnsey88]

I need to display the total number of people booked on the tour "AUS25", from the field "Tour ID". Could someone please give me some help with this to make this query.

Cheers

 

[PHV]

A starting point:
SELECT Count(*) FROM yourTable WHERE [Tour ID]='AUS25';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SJMcAbney]

Remember, to make the criteria part a parameter or else you'll end up with a mutlitude of queries hardcoded with a TourID.

Stewart J. McAbney | Talk History

 

[barnsey88]

Thanks guys for your help, but Stewart, I am unsure as to how to do that.

 

[PHV]

You may consider create a saved query named, say, qryGetCountBooked:
SELECT [Tour ID], Count(*) AS CountBooked
FROM yourTable
GROUP BY [Tour ID];

You can then filter this query on the [Tour ID] field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SJMcAbney]

The common way is, rather than having 'AUS25' as your criteria, you put [Forms]![MyForm]![MyCombo] as your criteria.

You then have a combobox on a form with all the TourIDs selected as its RowSource via a query.

When you run the query it points to the combobox to get what value it is querying on.

SELECT Count(*) FROM yourTable WHERE [Tour ID] = [Forms]![MyForm]![MyCombo];

You would, obviously, substitute MyForm and MyCombo for the name of the form and combobox respectively.

Stewart J. McAbney | Talk History

 

[barnsey88]

OK thanks - that makes things clearer.

 

[barnsey88]

One thing though - once substituted into SQL, i go into the query, where it comes up fine however it only shows "1" in the count column - where the should be 5.

 

[SJMcAbney]

What 5?

Stewart J. McAbney | Talk History

 

[barnsey88]

well there are 5 bookings for AUS25 however it comes up as only counting 1 as the total.

 

[PHV]

Well, why not posting your table(s) structure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244