Results 1 to 10 of 10
  1. #1
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19

    Question Query that pulls from two different tables based on description

    I need to create a query that will result in data from three tables being used in one. The value in a certain field determines from which table it will pull the remaining data. If you've answered in my other threads in the past couple weeks this is the same project. The three tables are laid out as such:

    • Table1 (survey data) is survey data in PNEZD (Point-Northing-Easting-Elevation-Description) format plus a unique value that allows the linking of this table to the data table (Weld, bend, flange, etc.)
    • Table2 (data table) contains several fields worth of attributes that will link to the unique value in Table2 (Weld data only)
    • Table3 (sequence) is a list of points with PNEZD data in an order I want the final result table to be in (think non-sequential but unique numbers in an exact order) (Weld, bend, flange, etc.)


    So of these three tables the unique values are:
    • Point Numbers, which are present in each table
    • The unique value mentioned in table1 and table2 (its a weld number formatted like WR001)




    With this info in mind, I need to create a query that:
    • results in a table that has the order of table3
    • for every record that is a weld shot, pull the data from table2
    • for every record that isn't a weld shot, pull whatever data from table1


    There are unique values that can link each table together but the pulling data based on whether its weld or something else is tripping me up. I hate to ask, but is something like this possible with the way my data is laid out?

    I have attached an example database that has each table laid out as they will be and a few records showing how the data looks. Any help creating this query or advice would be greatly appreciated.

    ExampleDatabase.accdb

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What are you going to do with the results? Is this going to be one set of results with different field names? Show an example of the results that you expect.

  3. #3
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    This result will be exported to .xlsx. The idea is to get the weld data in order and looking good, then insert the bends, flanges, valves, etc. in between the weld records and everything will be in order based on the order the point numbers are in within the sequence table (table3). I attached what the header currently looks like in excel I am trying to mimic. Currently I am trying a select query to get each code (weld/bend/elbow/flange) in the right order then using a make table query to put everything together.

    The top row will be for the weld data mostly, the bend direction and degree will be for elbows/bends, then the serial number will refer to flanges/valves/etc. that have a serial number. The PNEZD + cover + comments will be applicable to everything.
    Click image for larger version. 

Name:	Headers.JPG 
Views:	10 
Size:	35.2 KB 
ID:	27120

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If the field names were the same you could use two queries, one for welds and one for non-welds, and join them in a union query.

  5. #5
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    I currently have two select queries: one with welds and all weld data with fields in the right order, and one with the bends/elbows with fields in the right order. Both of these select queries are in the correct order because of the sequence table. Can you give me an idea of how to write the SQL for the union query?

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    SELECT * FROM query1

    UNION SELECT * FROM query2

    ORDER BY xxxx;

  7. #7
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    Query 1 (qrySequenceWeld) has PNEZD + cover + 12 fields
    Query 2 (qrySequenceOther) has PNEZD + 2 fields

    Obviously the 2 and 12 fields aren't going to pair up so the 12 weld fields can come first followed by the 2 elbow/bend fields in the resulting table.

    How can I get a resulting table that contains the sequenced point numbers followed by the data associated with each point number that is going to come from either query 1 or query 2 depending on what its code/description is?

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a table with the output the way you want it. Then each query can be an append query. You can sort it by using a query to do that when you display/export it. A union query has to have both sets of fields the same.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How are the tables related? What field names?


    You really should fix the field names - should not have spaces in names and shouldn't have special characters in names (example - the '#' is a date delimiter).
    Only letters, numbers and the underscore should be used in object names.

  10. #10
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    ssanfu,

    The tables are related via the point number (which appears in every table) and the weld number (which appears in tables 1 and 2). As far as the field names go, I am getting this data directly from the surveyor who processes it in his own software so changing the field names will require a discussion and change of settings on his part.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  2. Form pulls info into fields based on ID number
    By arothacker in forum Forms
    Replies: 3
    Last Post: 02-27-2014, 04:13 PM
  3. Replies: 2
    Last Post: 02-20-2014, 05:54 PM
  4. Replies: 6
    Last Post: 12-28-2012, 02:54 PM
  5. Form pulls info from 2 tables.
    By Jonpro03 in forum Forms
    Replies: 6
    Last Post: 07-20-2011, 11:33 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