Results 1 to 3 of 3
  1. #1
    Uniden is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011

    Update query advise

    Hello all.

    Could I have an update query to fill in the fields of a table from another one, although the number of records does not match?

    I mean, I have the Operators table:

    And the Rooms table:
    OperName Room Schedule
    405 5:00
    23 2:00
    234 7:00
    123 12:00
    423 10:00
    345 10:30

    How could I have ALL the OperName field at the Rooms table fill in with the operators at the Operators table ONLY and EVENLY?

    My Operators table reflects the staff available, and the must be assignated to the rooms on that day.

    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    No, a query could not accomplish that. There is no criteria for the query to consider. It can't 'know' which operator goes with which room/schedule and an UPDATE query cannot create records.

    If you want every operator associated with every/room schedule, that could be done in SELECT query that does not have a JOIN clause - a Cartesian join, like

    SELECT OperatorName, Room, Schedule FROM Operators, Rooms;

    Then you can use the SELECT query as a source for an INSERT seql action to create new records in a table.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Uniden is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Thank you for the tip, June.


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

Similar Threads

  1. Replies: 2
    Last Post: 01-15-2013, 08:14 AM
  2. Need Advise and Help
    By sabrish72 in forum Reports
    Replies: 3
    Last Post: 06-02-2011, 06:01 AM
  3. Newbie Need Guide and advise on Query
    By sabrish72 in forum Access
    Replies: 3
    Last Post: 05-24-2011, 08:44 PM
  4. Can anyone advise?
    By doceo in forum Access
    Replies: 0
    Last Post: 10-18-2009, 02:44 PM
  5. Some advise for a complete novice...!
    By 450nick in forum Access
    Replies: 1
    Last Post: 09-11-2009, 02:23 AM

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