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