Need help with writing update Query

[penndro]

I am creating a access 2007 database that will allow me to track my league's football games and results.

I have made all my data tables and now I need to create an update query to evaluate data in my GAMES table and then update to a table called GAMERESULTS.

I want to create a form button that when selected will run the update query.

The update query should:

1 - Add any new game results for games that are in the GAMES table. (New result meaning that those game IDs were not previously entered because the scores were not available yet.) So the query is looking for only games where {Completed}field is true.

2 - Query will create 2 records for each GAMEID (where {Completed} field is true). One record will be for HOMETEAM and other will be for AWAYTEAM. Understand that in the GAMES table there is a HOMETEAM, AWAYTEAM, HOMESCORE, and AWAYSCORE. So Homescore and Awayscore coupled with gameid will create the record.

3 - In each of the two records to be created the query will evaluate if game resulted in a Win, Loss, or Tie. If HomeScore>AwayScore = W (win); If HomeScore<AwayScore = L(loss); If Homescore=Awayscore = T (tie).

 

[PHV]

And what have you tried so far ?
You talked about an update query, but my understanding is that you'll use an append query based on an union query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886