Phil-
Yes, dumping the result into a table and then using it in an update query may be the best way.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Phil Knowles
Sent: Friday, November 29, 2013 10:41 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Help with query
Hi John,
Thank you once more for pointing me in the right direction.
I have today learnt yet more about the wonderful world of Access.
I have never simply written a query in SQL, I always use the query design mode, pull in my tables and join them together and then do the rest of the stuff to knock my query into shape.
Following your advice, I simply typed in the SQL and when I went to design mode, I saw that the 2 tables were not joined at all !
Minor revelation !!!
I haven't tried but have noted that you say this query cannot be changed into an update query to automatically do the update I am actually wanting to do.
I have had this before when it says "this is a non updateable query" and I think, in those circumstances I have been able to achieve my goal by creating a temporary table, converting the query into an append query to create relevant records and then writing an update query to use the temp table to update the main records. Should this work in this particular case?
many thanks
Phil
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, 26 November 2013, 18:14
Subject: RE: [MS_AccessPros] Help with query
Phil-
OK. Try this:
SELECT Matches.*, Derbies.*
FROM Matches, Derbies
WHERE ((Matches.hometeamID = Derbies.Team1) OR (Matches.hometeamID = Derbies.Team2))
AND ((Matches.awayteamID = Derbies.Team1) OR (Matches.awayTeamID = Derbies.Team2))
That should show you all the Matches that have a Derbies record where the two teams show up. Sadly, the query won't be updatable, but you will be able to see the ratings data you want to update your matches.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Phil Knowles
Sent: Tuesday, November 26, 2013 6:24 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Help with query
Hi John
No, the field derby in the matches table is to be updated with the appropriate rating from the derbies table (ie either rating1 or rating2 depending on whether team1 or team2 from the derbies record is the home team of the matches record).
Phil
On Tuesday, 26 November 2013, 14:24, John Viescas <JohnV@msn.com> wrote:
Phil-
You note a field called "derby" in the Matches table. Is this the same as the ID in the Derbies table? Or, are you looking for any previous Derby that has these two teams as either Home or Away?
If you can answer my questions, I may be able to suggest a solution.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of pdk444444@yahoo.co.uk
Sent: Tuesday, November 26, 2013 2:34 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Help with query
Hi
I am having trouble working out how best to create this query and I wondered if someone could give me some pointers.
I have the following tables (relevant data only)
Teams
id
team name
Matches
id
date
hometeamid (looks up team name from teams.id)
awayteam id (looks up team name from teams.id)
derby
Derbies
id
team1 (looks up team name from teams.id)
team2 (looks up team name from teams.id)
rating1 (this is a rating for this derby fixture when team1 is the home team)
rating2 (this is a rating for this derby fixture when team2 is the home team)
when a match record has been created (by bulk external import) I want to be able to update the record if applicable with the correct derby rating.
A match has a home team and an away team and I want to find (if it exists) the derby record for this match and then pick up the appropriate rating figure depending on which team is at home.
So from the match record I need to establish if there is a derby record for the 2 teams involved and then apply a rating depending on whether hometeam id = team1 or team2
I suspect I will need at least one level of 'prequery' but as I say I can't get my head around it at the moment
Help please!!
Phil
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (12) |
Tidak ada komentar:
Posting Komentar