Help with query...

[tbronc]

I have a table with athlete information

Sample: (Table- Athletes)
AthleteID - Primary Key
AthFName - first name
AthLName - last name
...
College1 - Combo box values from tblColleges
College2 - Combo box values from tblColleges
College3 - Combo box values from tblColleges
College4 - Combo box values from tblColleges
College5 - Combo box values from tblColleges

(Table - tblColleges)
CollegeID - Primary Key
AhtleteID - foreign key
College
Address...
Recruiter
Recruiter email

I need to have a query that will return a list of all athelets for each college choice so that I can send a report via email to the recruiter.

For example: ahtlete Joe Blow has college choice of 1. UCLA, 2. Stanford, 3.Texas, 4.Florida and 5.Boise State. And athlete Matt Man has college choices of 1. Boise State, 2. Florida, 3. Ohio St., 4. Stanford, 5. Arizona St.
The query would show something like this...



Boise State
Joe Blow
Matt Man

UCLA
Joe Blow

Stanford
Joe Blow
Matt Man

Florida
Joe Blow
Matt Man

Arizona
Matt Man

Texas
Joe Blow

Ohio St.
Matt Man

I can use any help anyone could offer.

I am able get a list of athletes with their 5 choices but would rather get a list of athletes by college, this way I can send a report for each athlete to the recruiter.

Thanks,
T.C.

[pbaldy]

Your data is not normalized, which will make getting data out problematic. If it's not too late, I'd fix that. The normalized structure would be a colleges table (with no fields for athletes), an athletes table (with no fields for colleges), and a junction table that contained fields for college ID and athlete ID. If I as an athlete chose 5 colleges, I would have 5 records in that table.

[tbronc]

Thanks for the reply, It's too late for this run but I will try it and see if I can get it to work. I will keep you posted.

I'm not a programmer nor do I know databases I just know there is an easier way to do what I am trying to do...I will have more questions I am sure.

Thanks again

T.C.

[pbaldy]

Well, one work-around would be to create a UNION query that pulls the data into a normalized format, then it's a fairly simple query against that to return the data you want.

[tbronc]

How do you do a union query? I am not at work so I don't have the database here. Is there a wizard or instructions somewhere?

[pbaldy]

In your case it would along the lines of:

SELECT AthleteID, College1
FROM TableName
UNION ALL
SELECT AthleteID, College2
FROM TableName
UNION ALL
...

You'd probably want to add a WHERE clause to eliminate empty college choices.