Results 1 to 11 of 11
  1. #1
    SeaTigr is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Location
    U.S.A.
    Posts
    38

    Foreign Key Query Issue - Foreign Key Table Fields Being Modified By Query


    Good morning all,

    For my latest conundrum:

    I am querying a main table which contains a foreign key linking information to a foreign table. The results of the query are outputted to a form. I have the form set up with a combo box for this particular field. Users can choose from the combo box, and then run an update query to change the information in the table.

    The problem is, the foreign table is also being changed.

    Let's say the foreign table looks like this:
    1 A
    2 B
    3 C

    If the current record has '2' stored in it, to represent 'B', and a user changes the selection to 'C' and runs the update query, 'B' is replaced with '3' in the foreign table, and '3' is stored in the main table.

    Obviously, I want '2' to be replaced with '3' in the main table, but not the foreign table.

    How do I fix this?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    SeaTigr is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Location
    U.S.A.
    Posts
    38
    UPDATE [XXX Equipment Table] SET [XXX Equipment Table].[System Name] = [Forms]![Query Results]![Cbo_QueryResults_SystemName]
    WHERE ((([XXX Equipment Table].[Unit Serial #])=[Forms]![Queries & Reports]![TxtSerial#]));


    "XXX" is where I replaced an identifying piece of information. All three XXX's are the same word.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I guess you better show us your table structures as well.

    What is the query you are using--- not the UPDATE, but the Select.?

  5. #5
    SeaTigr is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Location
    U.S.A.
    Posts
    38
    So, here is the relationships for the tables/lists. I've removed anything potentially identifying - not because of any worries about you kind souls, just trying to avoid running afoul of company policy. The table I'm querying is the Equipment Table on the left side.

    So, for the moment, here is how the query works:
    1) A user uses one form to enter the serial # of the equipment they're looking for. The press a button to run a Select query.
    2) The results of the Select query are displayed on another form. That form is designed to display the System Name as a combo box. If a user wishes to change the System Name (which is the name of the group that administratively owns the equipment), they use the combo box to choose the new System Name and then click a button to run an Update query. When the Update Query is run, the Equipment Table is updated, but the Systems List (in the center of the picture) is also changed.
    Attached Thumbnails Attached Thumbnails Inventory Database Relationships.jpg  

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is stored in System name in Equipment Table?
    What is stored in System Name of Systems table?
    What is the purpose of Systems List?

    Could you rewrite parts of Post#1 using your real table names?

  7. #7
    SeaTigr is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Location
    U.S.A.
    Posts
    38
    System Name of the Equipment Table and the Systems Table is a number field. It is the foreign key for the Systems List. The purpose of the Systems List is to contain a list, with proper spelling and capitalization, of all the systems that a piece of equipment can be a part of. I went with a list because this needs to be a searchable category and I didn't want to rely on users spelling the names correctly.

    A rewrite of post #1:
    A Select query is set up for the Equipment Table. Utilizing a form, a user enters a serial # (or whatever individually identifying information, but I'm working a serial # query at the moment) and clicks on a button to run the Select query. The results of the Select query are displayed on another form. On the results form, the System Name field is displayed in a combo box. The idea is for people to use the query results form to modify the record for an individual piece of equipment rather than mucking around in the table. So, if a user wishes to change the System Name associated with a piece of equipment (for example, a laptop was moved from system A to system B), the user uses the combo box to change system A to system B, and clicks a button to run an Update query.

    Currently, the Equipment Table updates the System Name field with the foreign key associated with System B - which is what I want, obviously. The problem arises with the Systems List table.

    Let's say this is the Systems List table:
    1 A
    2 B

    After running the Update query, the Systems List table will now look like this
    1 2
    2 B

    The 'A' has been replaced with the key number for 'B'.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It looks to me that you have
    System Name in Equipment and Systems tables pointing to the ID in the Systems List table.

    Without seeing your Select query I think you are assigning the Id to the Equipment.[System Name]???????????????????????

    I would strongly advise a rethink of your naming convention --no embedded space or special characters in field or object names.

  9. #9
    SeaTigr is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Location
    U.S.A.
    Posts
    38
    I truly appreciate your help on this, particularly as this hasn't been a simple answer-and-fixed issue.

    Here's the SQL for the Select query:

    SELECT [XXX Systems List].System, [Unit Type List].[Computer Type], [XXX Equipment Table].[Unit Brand], [XXX Equipment Table].[Unit Model], [Operating Systems List].[Operating System], [XXX Equipment Table].[Unit Serial #], [XXX Equipment Table].[Unit AAA #], [XXX Equipment Table].[Service Tag #], [XXX Equipment Table].[Software Version], [XXX Equipment Table].[Firmware Version], [XXX Equipment Table].[Date Arrived], [XXX Equipment Table].[Date Departed], [XXX Equipment Table].[Unit Cost], [XXX Equipment Table].[Unit Name], [Unit Amperage List].Amperage, [XXX Equipment Table].[Shipped To], [XXX Equipment Table].[Address (1)], [XXX Equipment Table].[Address (2)], [XXX Equipment Table].City, [US States & Territories List].[State/Territory], [XXX Equipment Table].[Zip Code], [XXX Equipment Table].[Phone Number (1)], [XXX Equipment Table].[Phone Number (2)], [XXX Equipment Table].[E-mail Address], [Electrical Plug List].[Plug Type], [Equipment Owner List].EquipmentOwner, [XXX Equipment Table].[BBB Number], [XXX Equipment Table].CCC, [XXX Equipment Table].Framework, [XXX Equipment Table].DDD, [XXX Equipment Table].[Date Destroyed]
    FROM [Equipment Owner List] INNER JOIN ([Electrical Plug List] INNER JOIN ([US States & Territories List] INNER JOIN ([Unit Amperage List] INNER JOIN ([Unit Type List] INNER JOIN ([XXX Systems List] INNER JOIN ([Operating Systems List] INNER JOIN [XXX Equipment Table] ON [Operating Systems List].ID = [XXX Equipment Table].[Unit OS]) ON [XXX Systems List].ID = [XXX Equipment Table].[System Name]) ON [Unit Type List].ID = [XXX Equipment Table].[Unit Type]) ON [Unit Amperage List].ID = [XXX Equipment Table].[Unit Amperage]) ON [US States & Territories List].ID = [XXX Equipment Table].State) ON [Electrical Plug List].PlugID = [XXX Equipment Table].[Plug type]) ON [Equipment Owner List].ID = [XXX Equipment Table].[EEE Equipment]
    WHERE ((([XXX Equipment Table].[Unit Serial #])=[Forms]![Queries & Reports]![TxtSerial#]));

    And here's the image of the query:
    Click image for larger version. 

Name:	Inventory Database Select Query.JPG 
Views:	6 
Size:	75.0 KB 
ID:	7494

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are asking, in the UPdate query to

    SET [XXX Equipment Table].[System Name] = [Forms]![Query Results]![Cbo_QueryResults_SystemName]

    How did you assign values to [Forms]![Query Results]![Cbo_QueryResults_SystemName] ?

    Seems to me that this must have value of 2.

    There is nothing in your UPDATE query (that you show in post #3) to update the Systems List table.

  11. #11
    SeaTigr is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Nov 2011
    Location
    U.S.A.
    Posts
    38
    Cbo_QueryResults_SystemName is the name of the combo box where the system name is displayed.

    When the Select query is run, the results are displayed on a form. The system name is displayed in a combo box, Cbo_QueryResults_SystemName. A user can use the combo box to select another system name, then click the button to run the Update query. So, my understanding is that values are assigned by the user through choosing a system name.

    The combo box is set to reference the Systems List table, with 2 columns (the key and the text containing the system name), and the first column width (the key) set to zero.

    I'm putting in some pics to show the Equipment Table and Systems List pre- and post-update query.
    Click image for larger version. 

Name:	Equipment Table Before Update.JPG 
Views:	2 
Size:	20.3 KB 
ID:	7507 Click image for larger version. 

Name:	Systems List Before Update Query.JPG 
Views:	2 
Size:	16.6 KB 
ID:	7508 This is the Equipment Table (Left) and Systems List (Right) pre-Update query.
    Click image for larger version. 

Name:	Equipment Table After Update.JPG 
Views:	2 
Size:	11.5 KB 
ID:	7509 Click image for larger version. 

Name:	Systems List After Update Query.JPG 
Views:	3 
Size:	16.9 KB 
ID:	7510 This is the Equipment Table (Left) and Systems List (Right) post-Update query. As you can see, the system name in row 1 has been changed from "AA" to "9". "9" is the key # of the system name I used the Update query to change.

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

Similar Threads

  1. advantage of foreign key in query
    By learning_graccess in forum Queries
    Replies: 1
    Last Post: 04-20-2012, 03:00 AM
  2. Replies: 2
    Last Post: 04-19-2012, 11:29 AM
  3. Replies: 3
    Last Post: 03-15-2012, 02:11 PM
  4. Replies: 1
    Last Post: 08-07-2011, 03:22 PM
  5. How to query using a foreign-keyed name
    By david28 in forum Queries
    Replies: 0
    Last Post: 10-17-2009, 12:57 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