Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 18th, 2008, 08:21 PM
Authorized User
 
Join Date: Mar 2008
Location: , NJ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default 2 names in a record

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

 
Old March 18th, 2008, 11:20 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 19th, 2008, 07:12 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 19th, 2008, 07:14 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 19th, 2008, 05:11 PM
Authorized User
 
Join Date: Mar 2008
Location: , NJ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I was able to do this using a union query

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete a record row, not just the record. Coby Access VBA 1 April 30th, 2007 06:29 AM
how to add new record as first record in dataset hunzian78@yahoo.com ASP.NET 1.0 and 1.1 Professional 4 April 21st, 2006 05:23 AM
To Many Names bachuss SQL Language 0 January 18th, 2006 12:40 PM
Record locking - user needs the next queued record cbtoolkit SQL Server 2000 0 December 6th, 2004 08:29 AM
Sub record not associating with Main record Ron V Access 1 August 31st, 2004 09:21 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.