Results 1 to 4 of 4
  1. #1
    alleurokyle is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    2

    Fill fields from other data entries

    I have a database query that I need to fix. It has an entry for all of the auto parts in our warehouse that have two or more brands (lines). I need to fill in the weights for these parts. Say I have part # 123 in brand (line) ABC and XYZ. ABC has a weight of 2, but XYZ doesnt have a weight.


    Line Part Number Weight
    ABC 123 2
    XYZ 123 0
    DEF 789 8
    XYZ 456 6

    I need to take all 20,000+ rows in my query and fill in the weights across lines. The part numbers are the individual entries but aren't the primary key (access generated one)

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Two steps are likely required without using subqueries (assuming that's even doable in this case).
    A totals query grouped by part number (if I understand the issue) that returns the Max(Weight) will provide the weights for a part.
    An update query can update WHERE [Part Number] = 0 using the results of the first query. You'd create an equal join between the target table and the first query. I suggested the Max function on the premise that you might have varying weight entries for a part amongst the 20K records.
    Test the update on a copy of your table!
    Last edited by Micron; 02-04-2016 at 11:48 AM. Reason: join comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    alleurokyle is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    2
    Quote Originally Posted by Micron View Post
    Two steps are likely required without using subqueries (assuming that's even doable in this case).
    A totals query grouped by part number (if I understand the issue) that returns the Max(Weight) will provide the weights for a part.
    An update query can update WHERE [Part Number] = 0 using the results of the first query. You'd create an equal join between the target table and the first query. I suggested the Max function on the premise that you might have varying weight entries for a part amongst the 20K records.
    Test the update on a copy of your table!
    I got the totals query completed, but I am a little confused as to how I need to run the update query. Could you please guide me on how to complete it?

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Am away. No phone plan Google update query.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-07-2014, 10:39 AM
  2. Replies: 1
    Last Post: 12-21-2011, 02:11 PM
  3. Replies: 1
    Last Post: 11-13-2011, 08:01 AM
  4. Using a combo box to fill in other fields
    By mccluein in forum Access
    Replies: 1
    Last Post: 03-14-2011, 05:40 PM
  5. Replies: 1
    Last Post: 12-09-2010, 08:29 AM

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