Results 1 to 9 of 9
  1. #1
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21

    SQL update query in access


    I have a split form in access that pulls up what we used to make our materials and the materials that are made. I have a column for prior tag number which corresponds to what was used to make it. Is there a way that I can, by using that field, trace back to what was used to make it and then update the information accordingly to the product that was made?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    probably, but not enough information to know whether your present structure could do it. Also need a more detailed explanation of what you mean by 'what we used to make our materials and the materials that are made'.

    What are you calling a material? what is 'what we used'?

    provide a copy of your relationships and provide some example data to illustrate what you have and what you want. Make sure the example data covers all eventualities - can a '
    what we used' be used in more than one material? can a 'what we used' be substituted with a different 'what we used'. Does 'what we used' include labour or is something that is either reusable, or has a one time use, or a use before date? etc

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Agree with CJ and would also suggest a copy of your database and instructions to pinpoint the issue. Also helpful if you provide a description of the process(es) your database is intended to support.

  4. #4
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21
    We take steel coils and convert it into tubing. What we used would be a coil and what it made would be a bundle of tubes. I can't provide a copy of the database because of the information inside of it. The form I use is from one table titled tags. This is a ss of the form. I don't have anything so far because I can't think of how to get it to trace back to the coil from the prior tag number. Once it does that I just need it to take the REC#, REC, DATE RB, COATING, and PO # from the coil and fill it in on the corresponding tube.
    Attached Thumbnails Attached Thumbnails Capture111.jpg  

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If every tube is a record, the coil serial number (or perhaps better, the pk from the coil table) would have to be part of that record. If not, then the coil sn would be part of the batch record. A split form is not the right form for this because it is only a form with 2 views of the same data. You should have a form/subform design - perhaps form and 2 subforms. Can't say without knowing the relationships involved between inputs and outputs.
    The integrated steel company I worked for has a couple of tube mills but I didn't have much to do with them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    if you can't include a copy of your database, maybe you can post a screenshot of your relationships window, showing the tables and their relations?

  7. #7
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21
    The coils have a unique number as do the tubes. They are all inside the same table. PK is Tag number.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Coils should really be in one table, tubes in another. Either coil number should be pk in coil table and same number is repeated as foreign key (fk) for each tube that has it's own serial number OR coil id should be autonumber pk and that gets repeated in tube table. Most here will tell you to do the latter (includes me). Either way, tube table also gets its own pk field. My guess is that a WO can use more than one coil, so you might need a similar link between them. Don't know what to suggest about customer since the relationship between WO and customer is not clear.

    FWIW, I concur with idea of posting relationships as per posts 2 and 6. That alone may not be enough though.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If coils and tubes are in the same table, do you have a Bill Of Material (BOM) setup?

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

Similar Threads

  1. Replies: 4
    Last Post: 03-09-2022, 11:28 AM
  2. Replies: 1
    Last Post: 03-11-2018, 11:44 AM
  3. Replies: 1
    Last Post: 10-27-2016, 12:14 PM
  4. update query in access vba
    By thep in forum Access
    Replies: 1
    Last Post: 01-29-2014, 03:18 PM
  5. Access 2003: Can't update from query
    By WestofYouLB in forum Access
    Replies: 4
    Last Post: 04-23-2011, 05:07 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