Distinct on one field, showing all fields

[krkearney]

I need to show all records, but use the distinct function on only one field. I have been searching around and I have seen on some sites that there is a SELECT DISTINCT ON function, but this doesn't seem to work with access. This is for a football schedule database I have been working on. The way the data comes in is by school schedule. Most games "location" is either 'HOME' or 'AWAY' so I can eliminate all duplicates on about 95% by selecting "...where location <> 'AWAY'" but there are few cases where the "location" is a neutral location, therefore I can't filter that out.
The following are my fields:
Date Scheduled, TeamName, Opponent, Location, Win/Loss, Score
Select Distinct doesn't work because it will still show both records. for example:

Date Scheduled|TeamName|Opponent|Location|Win/Loss|Score
9/2/2006|Bowling Green|Wisconsin|Cleveland, OH|null|0-0
9/2/2006|Wisconsin|Bowling Green|Cleveland, OH|null|0-0

In this case it doesn't matter what record is displayed, I just need one of them to show up.

Make sense?

Here is what I currently have. This will display everything correct except it shows the one game scheduled at the neutral location twice as seen above.

SELECT Schedule.[Date Scheduled], Schools.TeamName, Schedule.Opponent, Schedule.Location, Schedule.[Win/Loss], Schedule.Score
FROM Schedule, Schools
WHERE ((([Schedule]![Date Scheduled])>#8/27/2006# And ([Schedule]![Date Scheduled])<#9/4/2006#) AND (([Schedule]![Location])<>'AWAY'))
ORDER BY Schedule.[Date Scheduled];


thanks

 

[addy]

If it doesn't matter which record is returned then would SELECT TOP 1 work?

 

[addy]

Actually, this wouldn't work as you would only get one record back, not one for each location.

 

[ZmrAbdulla]

How about this SQL?

SELECT
    First(Schedule.[Date Scheduled]) AS [Date Scheduled]
  , First(Schedule.TeamName) AS TeamName
  , First(Schedule.Opponent) AS Opponent
  , Schedule.Location
  , Schedule.[Win/Loss]
  , Schedule.Score
FROM
   Schedule
GROUP BY
   Schedule.Location
  , Schedule.[Win/Loss]
  , Schedule.Score;

You need to add date parameters and all , but first you try if it is returning the correct result

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[krkearney]

At first glance that doesn't appear that it will work out. Maybe if I adjust a few items on it I can get it to work the way I need it to. I did find a work around for now. Each team has an "OrgID" so I added in there, where ((Schools_1.OrgID)>[Schedule].[OrgID]));


SELECT
Schedule.[Date Scheduled]
, Schools.TeamName
, Schedule.Opponent
, Schedule.Location
, Schedule.[Win/Loss], Schedule.Score
FROM
(Schedule LEFT JOIN Schools ON Schedule.OrgID = Schools.OrgID) INNER JOIN Schools AS Schools_1 ON Schedule.Opponent = Schools_1.TeamName
WHERE
(((Schedule.[Date Scheduled])>#8/27/2006# And (Schedule.[Date Scheduled])<#12/4/2006#) AND ((Schedule.Location)<>'HOME' And (Schedule.Location)<>'AWAY') AND ((Schools_1.OrgID)>[Schedule].[OrgID]));

This works as long as the schedule I am downloading is accurate, I got lucky in this case because there were 4 teams that weren't reporting yet, so there weren't any duplicates associated with it. If half of those games had the Home Team having a > OrgID then the other half, it wouldn't work. I was lucky and all Home Teams had the greater OrgID.

To get the full schedule I then made a Union with a separate query that was HOME games only, and all seems to work now. Thanks for your help..

If anyone has a better idea, please let me know, for I am still learning a lot of this.