Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Unanswered: Update query from multiple table sources??

    Hello,
    I'm trying to run an update query to one table. However, the value it updates is based on criteria from three tables including the table I want to update. I think that I might know how to do that now but the problem is that it also is referencing a table clone (if that is what it is called) Here are the tables that I'm referring too:
    Schedules is a real table that has every game for every school and has GameID as the primary key.
    Schools is a real table and is linked to the SchoolID in the Schedules table.
    Ratings is a real table and is linked to the SchoolID in the Schedules table and is also the table that I'm trying to update.
    Schools_1 is a clone of Schools that is made in the relationship or query wizard and is linked to the opponentID in the Schedules table.
    Ratings_1 is a clone of Ratings that is made in the relationship or query wizard and is linked to the opponentID in the Schedules table.

    Like I said above there are fields in each table listed above that is required for the formula needed to update the Ratings table. It is very complicated to me but I'm sure someone here could give me an example of how to do this in VBA using a command button.

    If any additional info is required please let me know.

    Thanks,
    Chuck
    Last edited by crarbo1; 02-01-06 at 06:55.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    How far have you got exactly ? Have you managed to produce an update query that does work ? If so, perhaps you could post the SQL and maybe we can suggest where the problem is.

    If the problem is about alias tables, don't be afraid to treat alias tables as if they were completely seperate tables in their own right (with their own name). It shouldn't cause you a problem.

    Chris

  3. #3
    Join Date
    Jan 2006
    Posts
    11
    Howey,
    Below is the SQL of the Update query from the query wizard. The formula is what I want it to do but this gives me two errors. 1: It asks for SchoolsRating.Week1 and then School.Week1. It shouldn't do this. It should already know that info from the tables. and 2: then it gives me a run time error '94', invalid use of Null. Not sure why though. I added the SQL in VBA to do what I wanted it to do and was hoping to be able to debug it better but no luck. I think I may have to use DAO recordsets to use it in VBA. When doing this the real tables don't give me an error but the cloned tables like Schools_1 does. Can't remember what error and I don't have that code anymore.

    SQL is the following:
    UPDATE Schools AS OpponentInfo INNER JOIN (Ratings AS OpponentRating INNER JOIN (Ratings AS SchoolRating INNER JOIN (Schools AS SchoolInfo INNER JOIN Schedules ON SchoolInfo.SchoolID = Schedules.SchoolID) ON SchoolRating.SchoolID = Schedules.SchoolID) ON OpponentRating.SchoolID = Schedules.OpponentID) ON OpponentInfo.SchoolID = Schedules.OpponentID SET SchoolRating.Week1 = IIf([OpponentInfo].[School]="BYE",[SchoolRating].[InitialRating],IIf(WeekNumber([Schedules].[Date])=1,NewRating([SchoolRating].[InitialRating],[OpponentRating].[InitialRating],[Schedules].[SchoolScore],[Schedules]. OpponentScore],"OpponentInfo.School","Schedules.Date"),[SchoolRating].[InitialRating])), SchoolRating.Week2 = IIf([OpponentInfo].[School]="BYE",[SchoolRating].[Week1],IIf(WeekNumber([Schedules].[Date])=2,NewRating([SchoolsRating].[Week1],[OpponentRating].[Week1],[Schedules].[SchoolScore],[Schedules].[OpponentScore],"OpponentInfo.School","Schedules.Date"),[SchoolRating].[Week1])), SchoolRating.Week3 = IIf([OpponentInfo].[School]="BYE",[SchoolRating].[Week2],IIf(WeekNumber([Schedules].[Date])=3,NewRating([SchoolRating].[Week2],[OpponentRating].[Week2],[Schedules].[SchoolScore],[Schedules].[OpponentScore],"OpponentInfo.School","Schedules.Date"),[SchoolRating].[Week2])), SchoolRating.Week4 = IIf([OpponentInfo].[School]="BYE",[SchoolRating].[Week3],IIf(WeekNumber([Schedules].[Date])=4,NewRating([SchoolRating].[Week3],[OpponentRating].[Week3],[Schedules].[SchoolScore],[Schedules].[OpponentScore],"OpponentInfo.School","Schedules.Date"),[SchoolRating].[Week3]));

    NewRating, WeekNumber are custom functions that work correctly.

    Any help is appreciated.

    Thanks,
    Chuck

  4. #4
    Join Date
    Jan 2006
    Posts
    11

    Update

    Howey,
    I fixed my first problem. I had a typo. I thought I double checked that. My second problems still exists though.
    Here it is again, 2: Once the query is ran, it gives me a run time error '94', invalid use of Null. If I hit End it appears to try to run the query anyway and then gives me the following error "Microsoft Access can't update all the records in the update query. Access didn't update 4751 fields due to a type conversion failure. .........." It says some other stuff but isn't important. I hope that you can help me with my issue. Oh, this happens if I do it in VBA or in the query by itself.

    Thanks,
    Chuck
    Last edited by crarbo1; 02-01-06 at 18:11.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •