Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    27

    Question Unanswered: Update query - update a field with a value from another table

    I have two tables (well ones actually a query but thats not important)

    query/Table1
    CNSCurrentMonthTerritoryUnits with fields TerrID, CurrentMonthUnits

    table2
    CNSTargetsBonusActuals with fields TerrID, Month, ..., AvanzaActualUnits

    I want to run an update query to take the value from currentmonthunits and put it is avanzaactualunits where the month is the month I pick and the TerrID in each table is the same.

    ARggghhhh... Help

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    this should do the trick:

    UPDATE Table1 INNER JOIN Table2 ON Table1.TerrID = TABLE2.TerrID
    SET Table2.AvanzaActualUnits = Table1.CurrentMonthUnits
    WHERE Table2.Month=[MyMonth];

    only if Table1/query is updatable of course.

  3. #3
    Join Date
    Jul 2002
    Posts
    27
    and if table 1/query isn't updatable?

    exact SQL i have entered is:

    UPDATE CNSCurrentMonthTerritoryUnits
    INNER JOIN incentivetargetsactuals ON CNSCurrentMonthTerritoryUnits.TerrID = incentivetargetsactuals.[Territory ID]
    SET incentivetargetsactuals.AvanzaActual = [CNSCurrentMonthTerritoryUnits].[SumOfAvanzaMTHUN]
    WHERE (((incentivetargetsactuals.Month)=[month]));

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Well, if you need to update Query1 it needs to be updatable (d'uh). Total and distinct queries are not. So are any joining one or using one as subquery, you can hower look up values from one.
    If your query isn't updatable you need to rewrite _that_ query, or try to update the table directly instead of doing so via the query.

  5. #5
    Join Date
    Jul 2002
    Posts
    27
    all sorted out now

    thanks

Posting Permissions

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