SQL Hellp

[ChewDoggie]

Hello All!

I've been on this for four days. A previous post today was trying a different approach but didn't work out.

I'm having an "issue" with a sql query. My db is Access 2000 and my query concerns two tables: "Racer" and "Registration".

THe racer's table contains all racer's information (racerid, name, address, etc). The registration table contains all registration data, including classes they've been registered for and classes they've been deleted from.

I've constructed this aql statement and it seems to work pretty good except for one (lousy stinkin') condition:

sql = "select distinct racer.racerid, racer.lname, racer.fname, racer.amanum, racer.tempnumber, racer.city, IIF(registration.Status = 'Y' and registration.eventid = " & entId & ", 'YES', 'NO') from registration RIGHT JOIN racer on (racer.racerid = registration.racerid) order by racer.lname, racer.fname"

THe one condition is....if a racer has been registered for one class (status = "Y") and deleted from another class (status="D"), then that racer's name appears TWICE in the resulting recordset. This is an unacceptable result. I want each name to appear in the list ONCE.

I tried incorporating a "where clause" into the sql statement, but that only acted like a filter and eliminated a bunch of racer's from the result set which is even more unacceptable.

Can anyone help me with this?

Thanks!


ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Golom]

There are a few options but you first need to decide which record you want to see when there are multiple records for a racer.

I'm assuming that the "Status" field is the one being duplicated. It also looks like you have a bit of a normalization problem with your database. Presumably things like "FName", "LName", "City", etc., are always the same for a particular racer but the existence of multiple records indicates that those fields must all be duplicated in the table so that you can have different values for "Status".

 

[ChewDoggie]

that's correct...the status field is the one being duplicated. The simple solution is that when a racer is deleted from a class (registration table), then delete the record INSTEAD OF keeping it and marking it with a status='D'. But he's in charge of system design and I'm stuck trying to figure out how to code this seriously de-normalized db.

I want to display the status='Y' duplicate data. How could I modify my query to do this?

Thanks!

ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[lespaul]

Ok, so if there are two records which do you want to see? the actual registration or the removal?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ChewDoggie]

actual registration.


ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[ChewDoggie]

I didn't see any follow up to this thread. Does anyone have an idea how to make this work?

Thanks!



ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org