Count Function

[Sharky II]

Can't get my head around this difficult record count problem

Hi guys

Trying to simplify... basically i have a listbox which which displays a load of trips (from a dynamic query... the user may have just searched for all trips to barcelona, and so all the past trips to barcelona have come up in this list box). You can click on the trip you want more info on and the trip roster comes up for that trip (which will display the number of people going on the trip, and their details in a subform... here i could either look at teh record selectors, or i can add an unbound text box and stick a count(*) in to give me the number of people that are going/went on trip). Fine!

BUT!! What i need to be able to do, is display the NoOfCustomers going on each individual trip IN COLUMN IN THE LIST BOX! (ie count the number of records in the query where there tripID is the same...(and not where the destination/whatever is the same)).

I really need help on doing this - it's a simple task but has really puzzled me! If anyone can help, please give us a hand - i'll owe ya! Searching the forum didn't really help as my problem is a bit specific.

I've tried messing with the criteria of a NoOfCustomers field in the query but to no avail.

I understand that i haven't given all teh details here, because i don'twant people to read 3 lines then switch off and click the back button so if anything needs clarification please let me know!

Thanks

Edd

 

[Len Boorman]

Suggest that you use a query or queries to select and summarise the records so the query output has all the details you want in the list box

Then create the list box and use the created query as the record source

Len B

 

[Sharky II]

hi - thanks for your reply

this is what i'm doing already! what i don't know how to do is count the number of records for each trip and display them.

does anyone have any idea how this could be achieved?

 

[Len Boorman]

Okay so you must be able by query to recover the details of each trip,
something like

01/01/03 London
01/01/03 London
01/01/03 Edingurgh

Notice that I have been careful to list only minimum info and make sure that DISTINCT is not included in the Select clause

then another query sort of like

Select Date, Destination, Count[Destination]As NoPeople
From FirstQuery
Group By Destination


My SQL needs a bit of practice so the above is just an outline

Does this help or am I not understanding you

len B

 

[Sharky II]

hi there man. i think you are understanding it great, but just to make sure i added a picture of the forms.

what happens is this... you go to a trip search form and say i want all trip to barcelona. it searches a query for all records where there is a customer going on the trip, and the destination is barcelona - if you see the datasheet which is on the pic i've attatched, you'll see that it's a query of customers, with the trip info there too (but i've dragged the destination to near the customer ID so that i can show this explicitly).

now the list box is a query of this query! it shows only distinct trip,s cos i don't want the same trip appearing loads of times (one of each customer = bad!). The sql is here:

SELECT DISTINCT Dynamic_Query_Trip3.TripID, Dynamic_Query_Trip3.TripType, Dynamic_Query_Trip3.TripDestination, Dynamic_Query_Trip3.TripReferenceNumber, Dynamic_Query_Trip3.DateOfDeparture
FROM Dynamic_Query_Trip3;

Now i you can see that the list box shows that there are two trips and there are about 8 customers going to barcelona (you can tell from the datasheet there are 8 ppl). This means some of the 8 people are going/have gone on one trip and some of them went on the other! When you click the trip in the form it pops up a trip roster to show who, and how many many people are going on this distinct trip.

i need it to display the number of people going on the trip IN THE LISTBOX and i can't quiet do it Sorry that i've given such a logn winded explanation but i wanted to make sure that it was clear

Also, i didn't quite understand what you mean in your post above? How would this let me see how many people are going?

Cheers for any help you can give

Yours stupidly,

Edd

 

[Sharky II]

so in this example, 2 people went on the 'lifestyle' barcelona trip, and 6 people went on the 'prestige' one - so in another column i need it to say NoOfCustomer 2 and 6 (respectively for each trip)

I've got a NoOfCustomers field in the trip table but currently don't know if it's needed...

cheeyars...

 

[Len Boorman]

Okay back again

I have set up a table that has fields, You can have additional details

Trip Date Name
London 01/01/03 Fred
London 01/01/03 Charlie
Barcelona 02/01/03 Fred


Query 1
SELECT Table1.Trip, Table1.Date, Table1.Name
FROM Table1;

This gives basic data

Then Query 2
SELECT Query1.Trip, Query1.Date, Count(Query1.Trip) AS NoPeople
FROM Query1
GROUP BY Query1.Trip, Query1.Date;


Now Query 2 queries Query 1 and summarises the data so that I get Trip, Date and Count of trip that represents number of people

Use Query 2 as source for List box

Len B

 

[Sharky II]

hi

thanks for a quick reply! my problem is that i don't understand the bit in bold

SELECT Query1.Trip, Query1.Date, Count(Query1.Trip) AS NoPeople
FROM Query1
GROUP BY Query1.Trip, Query1.Date;

I don't really understand the count() function? Do i need a field called count? Or NoPeople? I'm a bit lost to how it calculates the no of people

Sorry for having to spell it out for me!

 

[Len Boorman]

Okay the bit in bold means

Count the number of records of Trip that appear in Query 1 AFTER creating Groups of Trip/Date combinations. After doing the count display the value in a field called NoPeople


This is a case of a calculated field. You do not have to create any new fields at all. Imagine that this Variable exists in space. It only gets created when you want it and it disappears afterwards but the wonderful thing is that each time it is recalculated so you always have the right answer

Okay

Get back if its still not clear

Len B

 

[Sharky II]

do you want to know what my vision of beauty is? check the gif below!

thanks SO MUCH for your help man, i really appreciate it!

 

[Len Boorman]

I assume that the gif is just about what you were after.

Glad I could help

Len B

 

[Sharky II]

for people who need help in the future and search and get this post... here's the code i used in the end...

SELECT [Dynamic_Query_Trip3 Query].TripID, [Dynamic_Query_Trip3 Query].TripType, [Dynamic_Query_Trip3 Query].TripDestination, [Dynamic_Query_Trip3 Query].DateOfDeparture, [Dynamic_Query_Trip3 Query].DateOfReturn, Count([Dynamic_Query_Trip3].[TripID]) AS NoPeople
FROM [Dynamic_Query_Trip3 Query]
GROUP BY [Dynamic_Query_Trip3 Query].TripID, [Dynamic_Query_Trip3 Query].TripType, [Dynamic_Query_Trip3 Query].TripDestination, [Dynamic_Query_Trip3 Query].DateOfDeparture, [Dynamic_Query_Trip3 Query].DateOfReturn;

excuse my bad qry naming - but [dynamic_trip_query3 Query] is the query of the query!

obviously i'll change that soon, i'm just working on the mechanics for now

cheers again len!

 

[Sharky II]

yeah the gif is just a screen shot - nothing saucy (unfortunately!)!!