Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Update related records.

    I have a table named "parts" and a table named "suppliers" there is a junction table between them that lists all the parts from all the suppliers (potentially).

    I've created a query to show the lowest price for a part in our database. When a part exists in the "quote" table I want this query to update the supplier ID and price. I've tried a few different ways of achieving this and I'm stumped.

    the record set isn't updatable is the most common error I get. As always, any advice is appreciated.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's a link to an explanation as to why queries may be read only: http://allenbrowne.com/ser-61.html

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Okay, thanks.

    I am using Group by on the query to find the lowest price. This query looks at the "Quote_Part_ID" then finds the lowest value for the associated "part_ID". I just want to update with another query the price field on that "quote_Part_ID".

    I can find many examples how to find this lowest value. But none that I'm able to use in an update.

    It doesn't HAVE to be an update.. If I could show that on each part on the form as a suggestion id be happy too.

    Any further advice or links to something similar would be great.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Andy,

    Please tell us more about your tables and post the actual SQL you are using.
    In my view it would be rare to get multiple suppliers using the same PartID. Typically, suppliers have their own part identification systems, and companies/users of those suppliers often have their own cross reference tables to identify parts by description/supplier/etc. -unless you are working with HS code or similar.

    It might be helpful if you could make a copy of the are involved with some suppliers and parts so readers can see your issue in context.

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Materials.zip

    Hi Orange, Thanks for the reply.

    The Part ID is one that we assign to the part. However the suppliers part ID would be/in some cases is stored on the junction table. This table links all parts and all suppliers that supply those parts.
    So if 2 suppliers supply "x" we want it to default to the lowest value on the quote parts form.

    This database is pretty messy and unfinished. I'm aware of a lot of the problems but any feedback is always appreciated.

    On the quote_parts form I have it so the user can click the combo box for the supplier for the part listed. It also shows the prices. I would like to use the supplier field on this form as a "suggested" supplier. then the user could change any if they wanted before finalizing the quote.

    Another note: 2 tables in the database are linked tables. This shouldn't be an issue but if any problems occur I could try to copy the tables over for the purpose of sharing.

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Another thing that may be confusing. I keep mentioning "part ID" in the database its known as Mat ID on the materials database.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly do each of the tables represent?
    What are the associated rules that relate BuildsAndGroups to Group_Parts? and the others.

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    For what I need hep with, nothing. But since you asked. buildsandgroups will be a name we give to a group of parts. Say we build the same panel 100 times and it consists of 100 different parts. then we will make that into a group and similar to the suppliers junction table it will have parts that relate to that group.

    This isn't done as you can see but, the user can select an entire build rather than each part. But every part will be in the quote. (or elsewhere).

    I would have replied earlier, I seem to have missed the email update.

    Thanks for taking a look.

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Code:
    UPDATE Q_P_ID a
    INNER JOIN SupplierParts b ON a.Part_ID = b.Mat_ID
    SET a.ChosenSupplier = b.SupPrt_ID
    WHERE b.Price=(SELECT MIN(Price) FROM SupplierParts WHERE Mat_ID=b.Mat_ID);
    After some butchery of some code online and a lot of reading about how to use Min in querys this is the code I'm using. If anyone needs help with something similar this is where I got it from:

    http://stackoverflow.com/questions/1...x-in-ms-access

    Cheers.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Andy,
    Glad you have it resolved. Lots of good examples of various constructs on Google and Youtube etc.

  11. #11
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Half the battle with me is not knowing what I'm looking for. Or even how to explain. I'm working on it!

    When I did that code using the example I didn't think it would work first time either, I was happy it did! I've added some other criteria and another update to it. All seems well at the moment

    Just got some tidying up to do/some forms to make and reports and I'll soon have this running. I'm sure I'll encounter some more problems as I improve it though.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-14-2016, 02:18 PM
  2. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  3. Replies: 10
    Last Post: 10-10-2012, 11:15 PM
  4. One Form to Update 2 Related Tables?
    By zannix in forum Forms
    Replies: 3
    Last Post: 01-26-2012, 08:46 AM
  5. query won't display records with no related records
    By James Brazill in forum Queries
    Replies: 1
    Last Post: 06-28-2011, 02:10 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