Results 1 to 12 of 12
  1. #1
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98

    Query from multiple tables with NO relations

    Greetings all,

    As the title indicates, I'm trying to create a query, and eventually a report, that will pull information from multiple tables in my DB. However, the tables do NOT have a relation.
    For instance, let's say I have 4 tables, each consisting of separate information.
    Each table has the following info: Description, Qty, Serial Number, Part Number

    The tables are NOT l inked or in a relation in any sort of way. They all consist of similar fields but are basically inventory lists of different types of parts. So Table1 will have listings for parts for Item Type A, Table2 parts for Item Type B, etc. etc.

    I'm having difficulty getting the query to 'probe' the information correctly. It will sometimes give me a few lines of correct data, and other times I either get a few blank lines, or mixed information (Table1's serial number in Table2's listing)

    Is there a way to do this without creating relations? The way that the tables are setup is very simplistic. Each table is setup for a specific type of equipment (inventory management) so using refIDs won't work because each table has its own IDs.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If there are no relationships between the tables, then I would recommend creating a Subreport for each table (via a query), and then put all those Subreports into a single Report.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Tables have identical structure then they should be 1 table with another field for PartType.

    How are you querying these tables?

    Really would have to build a UNION query to mimic the 1 table model then apply filter criteria to that query.
    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.

  4. #4
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Thanks for the replies.
    I've attempted to do the subreport option, however it doesn't quite come out the right way. It keeps duplicating the data to the form.

    @June7: I thought about building another table with part types as you have suggested, I just haven't gotten around to it. I guess that might be the way to go, because then everything can be linked up accordingly.

    I will give that a shot this weekend and see how it goes.

    Regards.

  5. #5
    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
    Can you tell us in simple terms what you expect from the report?
    If each table has the same structure and the data values in each table are specific to a particular product/product type, then creating a new table with a field for Product type seems a reasonable structure. But the devil, as always, is in the details. What info do you have to report? How often? What sort(s) of criteria is involved in constraining reports?
    Work out what you need on paper before working with physical database--- it will save you time and frustration.
    Good luck.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Possibly you misunderstood me. Yes, a lookup table for part type codes could be useful. However, I am suggesting your 4 tables should be 1 table with another field for PartType.
    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.

  7. #7
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Originally, this DB started out as just a way to keep track of the inventory. Many different types of equipment and parts. Some parts have a serial number, some don't. Some have a part number, some don't. Some have a quantity, some don't.
    As time goes by, people ask for things to be added, such as this modification:
    They want to be able to generate a report that show ALL inventory items in the DB. This particular feature most likely will not be used often, the it will be used.
    So that report will consist of every single item which will have to be pulled from multiple tables.

    I suppose the best course of action is to redesign it accordingly, because at this rate I'm sure it will eventually need more features added which will consist of more advanced query tactics as well.
    So much for K.I.S.S.

    I will have to think about it for a bit.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As advised, use a UNION query to merge the 4 tables for purposes of the report output.

    SELECT Description, Qty, [Serial Number], [Part Number], "table1" AS Category FROM table1
    UNION SELECT Description, Qty, [Serial Number], [Part Number], "table2" FROM table2
    UNION SELECT Description, Qty, [Serial Number], [Part Number], "table3" FROM table3
    UNION SELECT Description, Qty, [Serial Number], [Part Number], "table4" FROM table4;
    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.

  9. #9
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Hello again guys,

    Ok, I've been contemplating the way to go about doing this keeping in mind future changes/upgrade requirements.
    Currently, the way my tables are laid out, is a bit tricky when it comes to doing Unions and Relations between tables due to the different types of fields in each table.

    For Example:
    first table
    ID Date Description Volume Serial Number Part Number Manufacturer Consignee Status Location Notes
    2 1/7/2019 G-Gun 250 CID 8887
    Sercel CGG, Inc Good/Used
    Not Complete; missing solenoid, sensor & air fitting
    3 1/7/2019 G-Gun 150 CID 773409
    Sercel CGG, Inc Good/Used
    Not Complete; missing air fitting
    4 1/16/2019 G-Gun 150 CID 4272201
    Sercel CGG, Inc Good/Used Crate #23 NC; missing air fitting
    5 1/16/2019 G-Gun 150 CID 451129
    Sercel CGG, Inc Good/Used Crate #23 NC; missing solenoid, timebreak, air fitting
    Second table
    ID Date Part Number Qty Description Serial Number Manufacturer Consignee Location Status Notes
    11 3/12/2019 2500-241-2 51 Eccentric Pin
    I/O CGG TBD USED
    12 3/12/2019 2700-031 14 Wing Module Gasket
    I/O CGG TBD USED
    13 3/12/2019 2500-667 79 Wing Shaft Bushing Carrier Metal Insert
    I/O CGG TBD USED
    14 3/12/2019 2501-710 37 Wing Shaft Bushing - Plastic
    I/O CGG TBD USED
    15 3/12/2019 8000-K2441 1 Motor Module Replacement Kit - Enhanced CC 77477 I/O CGG TBD USED
    16 3/12/2019 8000-K2441 1 Motor Module Replacement Kit - Enhanced CC 77493 I/O CGG TBD USED
    17 3/12/2019 8000-K2441 1 Motor Module Replacement Kit - Enhanced CC 77479 I/O CGG TBD USED
    18 3/12/2019 8000-K2441 1 Motor Module Replacement Kit - Enhanced CC 77502 I/O CGG TBD USED
    Third table
    ID Date Part Number Qty Description Serial Number Manufacturer Consignee Location Status Notes
    106 1/11/2019 1961-513SB5-70 2 Fire Chamber



    Used 1900
    107 1/11/2019 1961-80-2DC 1 Fire Chamber



    Used 1900
    108 1/11/2019 1961-120-1DC 2 Fire Chamber



    Used 1900
    109 1/11/2019 1961-135-1DC 1 Fire Chamber



    Used 1900
    110 1/11/2019 1961-150-2DC 1 Fire Chamber



    Used 1900
    111 1/11/2019 1958-531 16 Main Housing 6732
    CGG Shelf #10 New 1900 LLX
    112 1/11/2019 1958-531
    Main Housing 6732
    CGG Shelf #10 New 1900 LLX
    113 1/11/2019 1958-531
    Main Housing 6716
    CGG Shelf #10 New 1900 LLX
    114 1/11/2019 1958-531
    Main Housing 6714
    CGG Shelf #10 New 1900 LLX
    115 1/11/2019 1958-531
    Main Housing 5896
    CGG Shelf #10 New 1900 LLX
    Fourth table
    ID Date Description Qty Model Number Serial Number Manufacturer Status Notes Consignee
    1 1/23/2019 Fairlead Block 6

    Kongsberg Good 10T SWL CGG, Inc
    2 1/23/2019 Fairlead Block 9

    Kongsberg Good 40T SWL CGG, Inc

    As you can see, some tables reflect the same information, such as the GUN tables, however other tables have different fields.
    Some have serial numbers, some don't. Some have part numbers, some don't. Some have a Quantity and some don't.

    And there are several other tables covering the other types of equipment we have.

    So my question now is: What would be the best way to restructure the tables in order for advanced Query forms to function easier/better?
    I've seen a few examples of Tables that have sub-categories but I'm not entirely certain how to go about doing that.

    If anyone has an example of how they think I should restructure these tables, please feel free to enlighten me.
    I would greatly appreciate it.

    Regards.

  10. #10
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Looking at the samples provided I would simply make a new table with all the fields need to cover all eventualities and 1 extra field to identify whatever the different tables are currently identifying.
    However as a caveat - A lot depends on how many fields this would result in, if there are only a few redundant fields across the board, then you aren't going to cause yourself an issue.

    if this all holds true then one by one use an append query to match and add the relevant fields/data to the new master table.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I would combine to one table and tolerate empty cells.

    "Normalize until it hurts, denormalize until it works."
    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.

  12. #12
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    OK, (RESOLVED)
    Well, I've decided to go with 1 table housing all of the parts as mentioned previously.
    In turn, I had to basically edit/redesign all of my Queries, Forms and Reports accordingly.

    I've used default criteria in the searches for the Queries. This way if someone is searching for a particular part or piece of equipment, each type of part/equipment has it's own search Form, and in turn it's own Query which uses the default criteria so that a Gun part will not come up when looking for a Bird part, etc.

    Kinda simple, but time consuming.

    Thanks for the replies and help. Much appreciated.
    Regards.

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

Similar Threads

  1. Relations between tables disappear
    By BrightSoftware in forum Database Design
    Replies: 6
    Last Post: 04-03-2018, 07:06 AM
  2. Tables and relations and PK;s and FK''s and FU's
    By Karaline in forum Database Design
    Replies: 4
    Last Post: 02-28-2017, 08:35 AM
  3. Relations between tables
    By pbs in forum Database Design
    Replies: 2
    Last Post: 01-27-2017, 07:33 AM
  4. Designing the tables and relations
    By Enzym in forum Database Design
    Replies: 5
    Last Post: 12-29-2014, 08:54 AM
  5. Relations tables
    By azhar2006 in forum Access
    Replies: 6
    Last Post: 08-03-2014, 02:29 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