Results 1 to 3 of 3
  1. #1
    tsp59 is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    1

    updating two linked tables

    Wish I could remember the stuff I forgot.



    I have a small DB that tracks the movement of serial-numbered things between sites.
    The event table is essentially a transaction log of movement events.

    [ event table]
    eventid auto PrimaryKey
    date date/time
    serial_num unique item identifier
    from location moved from
    to location moved to


    I'd like to be able to
    1.) determine the last place a thing was sent (last_location)
    2.) count items at each site by type (item_type)

    so i created another table containing some attributes of each item by it's serial number.

    [attribute table]
    serial_num PrimaryKey
    item_mfgr
    item_type
    last_location

    I then foolishly tried to update the two linked tables by query using the serial_num field relationship. It choked trying to enter anything in the attribute_table...
    Likely this is because there is no entry in the attribute table for this serial number yet...
    It doesn't exist in "attribute_table" yet and i can't prepopulate this table with the serial number of every item.

    Perhaps the best thing would be to not try to use a query where the tables are linked...
    I suppose I could update the event table and then on the same form and copy the "To" field using a macro to into the attribute table as items are moved.


    I swear I did something akin to this long ago...



    [event table]
    eventid auto PK
    date date/time [attribute table]
    serial_num unique item identifier --------> serial_num PK
    from location moved from item_mfgr
    to location moved to item_type
    last_location


    What am i doing wrong?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of your database-- just enough data to show the intent?

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum...


    While waiting to see if you are going to post your dB, I played around with your description of what you think the tables should be. This is where I would start:

    Code:
    tblEquipment
    -----------------
    EquipmentID_PK        Autonumber    (PrimaryKey)
    EquipTypeID_FK        Long Integer  (Link to tblEquipTypes)
    Equip_MFGR            Text           (<<-- could be a FK field to tblMFGR)
    Equip_Serial_Num      Text
    
    
    
    
    tblEquipTypes
    -----------------
    EquipTypeID_PK        Autonumber      (PrimaryKey)
    EquipTypeDesc         Text
    
    
    
    tblSites
    -----------------
    SiteID_PK              Autonumber       (PrimaryKey)
    SiteName               Text
    SiteLocation           Text
    
    
    
    tblEvents
    -----------------
    EventID_PK             Autonumber    (PrimaryKey)
    EquipmentID_FK         Long Integer    (Link to tblEquipment)
    MoveFrom_SiteID_FK     Long Integer    (Link to tblSites)
    MoveTo_SiteID_FK       Long Integer    (Link to tblSites)
    EventDate              Date/time
    
    


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

Similar Threads

  1. Replies: 2
    Last Post: 11-27-2018, 06:03 AM
  2. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  3. Linked tables not updating
    By ABQcook in forum Access
    Replies: 1
    Last Post: 01-04-2012, 01:27 PM
  4. Replies: 2
    Last Post: 09-13-2011, 04:31 AM
  5. Auto-updating fields in linked tables?
    By Leelers in forum Database Design
    Replies: 27
    Last Post: 01-08-2010, 06:23 PM

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