ELSIF Query to Read Duplicate Records

[Overzero]

I have been tasked with writing a query to help distribute tickets for a High School fundraiser. I was given a roster with all the athletes and I made a database that looks like this:



Name Year Sport Season
Jane doe 10 basketball 2
mike smith 12 football 1
mike smith 12 track 3

Where 1 is a fall sport, 2 is a winter sport and 3 is a spring sport

So since Jane Doe is in a sport she gets 5 tickets from her basketball coach, but since Mike Smith is in two sports we only want him to recieve 5 from his football coach and none from his basketball coach.

So if the database reads a 2 or a 3 I need it to check if that athlete also has a 1 or a 2 as well and report false.

I was thinking of using some kind of elsif expression to get this done but I don't know how to get the program to look at multiple rows for a single return.

Any advice would be appreciated.

[Bob Fitz]

Hi

Name Year Sport Season
Jane doe 10 basketball 2
mike smith 12 football 1
mike smith 12 track 3

Does this represent the records of one table? If so, what is it called?
If "Name" and "Year" are the names of fields in a table they need to be changed because both are "Reserved" names in Access.

If it the result of a query, can you post the query's SQL?

[Overzero]

They are records in one table. I can change the names of the fields to "Student" and "Grade"

The table is called Roster_2011

[Bob Fitz]

Hi

I have created your table so that a can try to write the required query but I need some clarification please.

Where 1 is a fall sport, 2 is a winter sport and 3 is a spring sport

Does it matter when the sport is played?

So since Jane Doe is in a sport she gets 5 tickets from her basketball coach, but since Mike Smith is in two sports we only want him to recieve 5 from his football coach and none from his basketball coach.

Why might Mike Smith get tickets from the basketball coach, if it not a sport that he plays?

How many tickets can student have that plays one sport?
How many tickets can student have that plays two sports?
How many tickets can student have that plays three sports?
What is the max number of tickets that any one student can have?

[Overzero]

Each student gets 5 tickets total. So for athletes who play two sports they don't want to accidentally give them 10 (5 from a fall sport and 5 from a spring sport for example).

The way they want to handle that is essentially giving the sports that are earlier in the school year priority.

So a fall sport coach would be the one to give the student his or her 5 tickets since it comes earlier in the year than a spring sport, but since the students name would still be on the list given to the spring sport coach there needs to be a note saying 'this student has already received tickets'

I hope that made sense

[Bob Fitz]

I am guessing from your language that you are in the USA. Please forgive my ignorance, but I have no knowledge of when you would play these sports. Is the number in the season column an indication of this order?

[Overzero]

Yes the numbers are in order. 1 is a priority over 2 or 3.

[Bob Fitz]

Hi Overzero

Sorry it has taken me so long to reply but I've been rather busy.

Hope this will make the wait worth while

 
SELECT [Roster_2011].[Student], [Roster_2011].[Grade], DLookUp("Sport","Roster_2011","Student='" & [Student] & "'") AS Activity
FROM Roster_2011
GROUP BY [Roster_2011].[Student], [Roster_2011].[Grade];

Just cut and paste the code to the SQL of a new query.

Regards

Bob

I have tested it and it does give results. I hope I have understood what you want. If the results are NOT exactly what you need please post back.