Results 1 to 5 of 5
  1. #1
    schettinald is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    2

    Question Trying to create a query to "Auto-Fill" columns based on similar item in referencing tables

    Hi - I am new the forum and appreciate any help someone might be able to give. I am a self-taught Access user who is newish. I know all of the basics (I read through Microsoft Access 2013 in the Shelly Cashman Series). But I struggle with more complex maneuvering sometimes.

    Currently I am trying to create a query that will auto populate data based on table relations:

    In the most basic form...

    I have two tables:
    Location ID Location Name Price Family ID
    560100 BLOOMINGTON IN PARTS SUPPLY R6200
    560200 CENTRAL INDIANA PARTS SUPPLY R6200
    560300 DALEVILLE PARTS SUPPLY R6200
    560400 FORT WAYNE PARTS SUPPLY R6200
    560600 PLAINFIELD PARTS SUPPLY R6200
    560700 SOUTH BEND PARTS SUPPLY R6200



    Item ID Item Description Location ID
    001260! VENT, 8IN WALL BRACKET 400300
    #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY 560100
    001260! VENT, 8IN WALL BRACKET 560200


    I want Access to see if Location ID can be found in the second table (we will call it Indianapolis district) and referenced in the first table (called Test). Then, I would like it to apply the Item ID and Item Description being applied to 560100 in Test to every other site in the Indianapolis district table.

    Currently all I have been able to come up with is:

    SELECT [Indianapolis District].[Location ID], Test.[Item ID], Test.[Item Description]
    FROM Test RIGHT JOIN [Indianapolis District] ON Test.[Location ID] = [Indianapolis District].[Location ID];

    Location ID Item ID Item Description
    560100 #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY
    560200 001260! VENT, 8IN WALL BRACKET
    560300

    560400

    560600

    560700


    But what I want is:

    Location ID Item ID Item Description
    560100 #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY
    560200 #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY
    560300 #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY
    560400 #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY
    560600 #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY
    560700 #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY
    560100 001260! VENT, 8IN WALL BRACKET
    560200 001260! VENT, 8IN WALL BRACKET
    560300 001260! VENT, 8IN WALL BRACKET
    560400 001260! VENT, 8IN WALL BRACKET
    560600 001260! VENT, 8IN WALL BRACKET
    560700 001260! VENT, 8IN WALL BRACKET


    Let me know if you need any other info.

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Maybe:

    Query1:
    SELECT [Indianapolis District].[Location ID], Test.[Item ID], Test.[Item Description]
    FROM Test RIGHT JOIN [Indianapolis District] ON Test.[Location ID] = [Indianapolis District].[Location ID]
    WHERE NOT [Item ID] IS Null;

    Query2:
    SELECT Query1.[Item ID], Query1.[Item Description], Test.[Location ID] FROM Query1, Test;

    Query2 syntax is called a Cartesian query. Without JOIN clause every record will associated with each record of other table. This type of query can perform very slowly with large datasets.


    Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention. Better would be ItemID or Item_ID.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    schettinald is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    2
    That is closer but not quite what I am looking for. It returned the below:
    Item ID Item Description Location ID
    #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY 560100
    #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY 400300
    #12DAMPER14! DISPLAY; 10-UNIT LOCKING COUNTER DISPLAY 560200
    001260! VENT, 8IN WALL BRACKET 560100
    001260! VENT, 8IN WALL BRACKET 400300
    001260! VENT, 8IN WALL BRACKET 560200

    I am starting to think maybe Access is the wrong program for this

    What I want the program to think through are these steps:

    Does Location ID in Test Table match any of the Location ID's in Indianapolis District Table?
    If Yes, add all rows of data from Test Table to all Location ID's in Indianapolis District.
    If No, ignore that line of data from the Test Table.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sadly, computer programs do not do your thinking for you. You need to tell it what you want to accomplish. Take these steps one at a time and write them down as commands to Access instead of questions that you think Access (or any program) will answer for you.

    Such as (1) find all location ID's that match in both tables (2) add those to second table (3) find non-matching ID's (4) ignore - ?? etc

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I did a test with the posted data sample.

    SELECT Query1.ItemID, IndianapolisDistrict.LocationID, Query1.ItemDesc
    FROM IndianapolisDistrict, Query1
    ORDER BY Query1.ItemID, IndianapolisDistrict.LocationID;

    Result:
    ItemID LocationID ItemDesc
    #12Damper14! 560100 Display
    #12Damper14! 560200 Display
    #12Damper14! 560300 Display
    #12Damper14! 560400 Display
    #12Damper14! 560600 Display
    #12Damper14! 560700 Display
    001260! 560100 Vent
    001260! 560200 Vent
    001260! 560300 Vent
    001260! 560400 Vent
    001260! 560600 Vent
    001260! 560700 Vent
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 10-12-2017, 12:12 PM
  2. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  3. Replies: 4
    Last Post: 09-10-2015, 08:22 AM
  4. Replies: 1
    Last Post: 02-17-2013, 04:46 PM
  5. Replies: 5
    Last Post: 02-05-2013, 10:57 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