Query to pull multiples of same data from a table

[mpreston14]

Hello everyone,



I have a soccer/football referee database that I built that helps us score each referee by the level of match and league they work. We also have "observers" at some of these matches.

In the master data table, each referee's assignment is given a MatchID that links the gamedate, gametime, gamenumber together with the data of the two or three referees that officiated as well as the observer if one was present. Each Ref/observer also has a unique ID. The data is imported via excel and the MatchID is an autonumber based on where the assignment is on the spreadsheet when imported.

I would like to be able to list the GameNumbers that had both a referee and an observer.

In my mind it seems so easy, but everything I have tried isn't working.

Example of the data in the table:

MatchID RefID League Match Position GameNumber GameDate GameTime
1984 611 SL BU8 Referee 621 4/27/2013 2:15 PM
258 32 SL BU8 Referee 621 4/27/2013 2:15 PM
1141 791 SL BU8 Observer 621 4/27/2013 2:15 PM

Any ideas as to how I can create a query to list:

GameNumber GameDate GameTime RefID LastName Oberseved_By
621 4/27/2013 2:15 PM 611 Jones LASTNAME (from ref table that links to # 791 in above example)

I may add a field to the master data table that holds has an "Observed by" field and manually alter the import file to put it in the correct field at import, but for now I would like to get what I can out of the current set of data.



Thanks,

Mike

[June7]

What have you tried? Post the query you attempted.

Criteria that depends on values in other records of same table is tricky. Often requiring subquery or domain aggregate function.

Only one observer for each game?

One way to try:

SELECT GameNumber, GameDate, GameTime, RefID, DLookup("RefID", "tablename", "GameNumber=" & [GameNumber] & "Position='Observer'") AS ObsID FROM tablename;

The resulting dataset will have the 3 records from your example but each will have the ObsID value, if the game had an observer.

Now join that query to the Ref table to retrieve names. Join the Ref table twice, to RefID and ObsID fields.

Can apply filter criteria to select only those records that are Referee position. That will eliminate the Observer record. Also apply criteria where there is a RefID and ObsID.