Hi,
I have a database with information on horse races.
it has the date track driver and trainer. ex:

date track driver trainer
2/1/08 1 31 25
2/1/08 2 31 31
2/2/08 1 19 25


what i need to do is add up the information for each person.
so how many times did #31 race. i'm thinking i can sort it by driver and use VBA code with a for loop to add it up. but what do I do to catch the times when the driver is also a trainer?
is there a better way to do this?
michael

The sort of thing you are asking can be accomplished using a sql statement. For example, to get how many times did #31 race (assuming the table name is Races:

Select count(*) from Races where driver = 31

If you simply want to get a count of all entries where the driver is also the trainer:

Select count(*) from Races where driver = trainer

If you want to get a count of all the times where the driver and trainer are 31:

Select count(*) from Races where driver = 31 and trainer = 31

... and so on.


Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit

I think the fundamental issue here is that the database structure needs to look more like this:

tblTrack
TrackID
TrackName
...

tblPersonnel
P_ID
LName
FName
...

Track_Personnel
TP_ID
TrackID
Driver - FK to P_ID
Trainer - FK to P_ID
...

Then you would have to create these two queries, I think:

qryDriverTotal
SELECT Track_Personnel.Driver, Count(Track_Personnel.Driver) AS DriverCount
FROM Track_Personnel
GROUP BY Track_Personnel.Driver

qryTrainerTotal
SELECT Track_Personnel.Trainer, Count(Track_Personnel.Trainer) AS TrainerCount
FROM Track_Personnel
GROUP BY Track_Personnel.Trainer

Then bring them together like this:

qryDriverAndTrainerTotals
SELECT tblPersonnel.PersonnelNo, qryDriverTotal.DriverCount, qryTrainerTotal.TrainerCount
FROM qryTrainerTotal RIGHT JOIN (qryDriverTotal RIGHT JOIN tblPersonnel ON qryDriverTotal.Driver = tblPersonnel.PersonnelID) ON qryTrainerTotal.Trainer = tblPersonnel.PersonnelID

That yielded this result for me:

PersonnelNo DriverCount TrainerCount
19 1
25 2
31 2 1

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry, I used "PersonnelNo" in my sample database instead of "P_ID" in tblPersonnel, hence the column name in the query results.

mmcdonal

Look it up at: http://wrox.books24x7.com

I was able to do this using a union query

select field1, field2
from trainers-query
union all
select field1, field2
from drivers-query;