Results 1 to 4 of 4
  1. #1
    nkhashab is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    2

    Update Query from multiple Tables

    I have five tables where I want all true values to update to .5, but there is one table in particular where I want the value to stay at 1. Then the calculated totals would sum to another table.



    I am trying to make an update query that pulls attendance points from a specific table and multiplies the Boolean value of -1 in half. I want all other points from all other tables to be a value of 1; I'm not sure how to do this.

    My Build expression is:
    IIf([tblNoLogin]![Attendance]>0,[Attendance]*-0.05,[Attendance]*-1)

    But when I run it, an input box appears with "[tblNoLogin]![Attendance]".

    Perhaps you have a better suggestion for how I can accomplish this?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    An action SQL can edit only one table. You will have to run 5 UPDATE actions.

    Is tblNoLogin included in the query design? Post the entire SQL statement.

    What data type is Attendance?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    nkhashab is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    2
    Hi June7,

    I was worried that might be true; I suppose I may need to restructure my queries. Currently, there are 5 separate tables that house the data, then I run an append query that summarizes all of the true Booleans and converts them into a value of .5. Then those points are updated into a 6th table where I house the attendance points for each employee. The problem is that when I try to update, since I can't update another table, how do I get all of the point totals into one table?

    Here is the SQL statements for my update query:

    UPDATE tblPointTotals SET tblPointTotals.[Attendance Points] = IIf([Attendance Points]<0,[Attendance Points]*-0.5,[Attendance Points]), tblPointTotals.[Performance Points] = IIf([Performance Points]<0,[Performance Points]*-1,[Performance Points]);

    Attendance is a True/False data type.

    Thank you,
    Nkhashab

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Why do you have 5 separate tables? Are they identical field structure? Why are you saving calculated data?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-17-2014, 06:14 PM
  2. Replies: 3
    Last Post: 04-08-2014, 07:08 AM
  3. Update Query for multiple tables VBA
    By WickidWe in forum Queries
    Replies: 3
    Last Post: 12-18-2013, 05:50 PM
  4. Update multiple tables
    By DADAZHU in forum Access
    Replies: 8
    Last Post: 12-07-2011, 09:05 AM
  5. Replies: 10
    Last Post: 07-12-2011, 11:09 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums