Results 1 to 3 of 3
  1. #1
    SoleAris is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    2

    Two tables with a similar field that need to be accessed simultaneously

    Hi all, thanks for the time to answer my question.

    I am designing a new report that will allow customer service to easily list and fill out the contents of our various size packaging boxes and cartons.

    In our back-end database, we have a table for our finished goods, and then a separate table for all individual parts (we have chosen to keep these tables separate since we have an application that allows users to look up multiple and shared parts for each finished good via a Linker table).

    However, both tables have an "Item Number" field which needs to be accessed on this new report. For example, in a typical order we may have a finished good with an item number of 5 but also additional parts ordered of item numbers 11, 12, and 13 that all will be shipped in another carton.

    Obviously, this presents a problem, as throwing in both tables will make Access require that any given item number will be found in both the finished good and parts tables, which is not what I'm looking for. Rather, I'd like it to look in either table and upon a match give me information about that particular Item Number.

    Any suggestions? Thanks in advance!
    Last edited by SoleAris; 11-14-2011 at 03:18 PM.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    UNION strikes again, maybe!

    Do a UNION query of the two tables. There is no wizard or designer for UNION, must type in SQL View window of designer.

    SELECT "Finished" As Source, [Item Number], Description FROM Finished
    UNION ALL SELECT "Parts", [Item Number], Description FROM Parts;

    Description is suggested but use whatever names are in your tables. If the names are the same in both tables no alias is needed, but if the similar fields have different names, include alias name on first line.

    Now include this query in the Report's RecordSource by joining on the item number fields.
    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
    SoleAris is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    2
    Knew I was having one of those Mondays..I completely spaced having UNION as an option..working with Access sometimes makes me space all that is available through SQL lol.

    Worked like a charm, thank you!!

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

Similar Threads

  1. Replies: 2
    Last Post: 09-29-2011, 12:50 PM
  2. Finding similar names between two tables.
    By Laurie B. in forum Access
    Replies: 1
    Last Post: 07-15-2011, 03:21 PM
  3. Replies: 1
    Last Post: 07-18-2010, 03:35 PM
  4. Replies: 1
    Last Post: 07-07-2009, 01:00 PM
  5. Users on Database Simultaneously
    By rtemple in forum Access
    Replies: 2
    Last Post: 03-26-2009, 08:38 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