Results 1 to 6 of 6
  1. #1
    aelder is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Location
    Mitchell, SD
    Posts
    3

    Talking Using a lookup field in Table1 to automatically update Table2

    I'm working on an inventory database. I have two tables...Equipment and Deployed.



    Equipment has two fields: Serial Number (text field) and Deployment Status (lookup field).
    Deployed has one field: Serial Number (text field)

    I want to be able to select "Deployed" in the lookup field on the Equipment table and have it automatically update the Deployed table with the Serial Number from the Equipment table.

    Does this make sense, and is it possible?

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    Honestly, no - it does not make sense. I don't mean to be snarky. But from a database analyst perspective it is not needed. When you make a record in the equipment table have the status of 'deployed' - then you have no need of a deployed table. It is redundant. Any time you want a 'record set' of everything deployed you simply do a query of the equipment table for records that show as being deployed.

  3. #3
    aelder is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Location
    Mitchell, SD
    Posts
    3
    No offense taken. The idea is that the first table is for basic information, the second, the Deployment table is for machine/user specific information once the machine has been deployed. The idea was to simplify the size of the tables in question, keeping them linked by serial number.

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    If the deployment table will have multiple records - to an individual equipment record.... then this is the classical 1:many and you need to have a cross referencing field on both sides; which may indeed be the serial number.

    Your orignal post made it sound like it was 1:1 - and in that case it really isn't needed.

  5. #5
    aelder is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Location
    Mitchell, SD
    Posts
    3
    Having reread it I see that. I was simply trying to keep it simple in the explanation. Basically all I want to know is if I can set it up so that if I select a specific option in a lookup field (or drop down menu, however you look at it) in Table1 it will automatically create a record in Table2. I'm sure with a little play I could get it done through a query but I would really prefer that it be automatic. I'm just not sure how to do it or if its even possible.

  6. #6
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    The way to approach this is a classical form/subform way.

    You make a query of equip table that results equipment deployed.

    You make your main form sourced on this query - call this EquipDeployInfo lets say.

    You then insert the deployed table into this form as a subform - the wizard will prompt for the correct cross referencing fields.

    One doesn't auto make a record in the subform just by making an equip record into deployed - it can be done but really isn't needed.....a blank record in the subform will be there ready and waiting for info....so when you have info for the subform - then you enter it and it will be linked automatically to the correct equipment.

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

Similar Threads

  1. Automatically update datasheet
    By tammiep in forum Forms
    Replies: 1
    Last Post: 08-17-2010, 04:43 PM
  2. Help Automatically running and Update Query
    By JohnRandolphSTL in forum Queries
    Replies: 8
    Last Post: 04-13-2010, 02:08 PM
  3. Automatically Reduce Field Size
    By Rebecca in forum Access
    Replies: 4
    Last Post: 01-06-2010, 10:14 PM
  4. Use Table1 to update Table2? Urg Help needed
    By munkifisht in forum Queries
    Replies: 1
    Last Post: 07-24-2009, 08:00 AM
  5. Replies: 4
    Last Post: 05-19-2009, 04:15 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