Query 2 tables with non-shared fields

G

Guest

Hello-
I've got a database to track the histories of individual tools, and want to
generate a report to show the lifetime history of each individual tool,
including all the times it’s been used and all the times it’s failed. Here's
how it's set up (note that the first column in each table is the primary key;
I have other fields with additional details, but this shows you the basic
structure):

Tools table (tracks the production of each tool):
tool_id production_date
tool1 production_date1
tool2 production_date2
tool3 production_date3
tool4 production_date4

usage table (tracks each time each tool is used):
use_id tool_id
use1 tool1
use2 tool2
use3 tool2
use4 tool4
use5 tool4

failures table (tracks failures for each tool):
failure_id tool_id
failure1 tool1
failure2 tool1
failure3 tool2

Note that any given tool can have zero or multiple use incidents with no
upper bound, as well as having zero or multiple failures with no upper bound.


What I need is a lifetime history of every individual tool, with production
date, every time it’s been used, and every time it’s failed. So the results
of my query should look like this, so that I can then easily generate a
report sorted by tool:
tool_id use_id failure_id production_date
tool1 (null) (null) production_date1
tool1 use1 (null) (null)
tool1 (null) failure1 (null)
tool1 (null) failure2 (null)
tool2 (null) (null) production_date2
tool2 use2 (null) (null)
tool2 use3 (null) (null)
tool2 (null) failure3 (null)
tool3 (null) (null) production_date3
tool4 (null) (null) production_date4
tool4 use4 (null) (null)
tool4 use5 (null) (null)

This is something that would be trivial in a spreadsheet, by simply copying
and pasting the 3 tables’ rows below each other, then moving the columns the
right, and finally sorting by tool_id. I need to do this in Access, though,
so that other people can generate the same query, and am having a hard time
of it. In particular, all the queries I try end up combining use_id and
failure_id on the same row in different combinations, when in reality they
are two completely separate incidents on separate dates.

Sorry about the long long-winded explanation, and thanks for your help.

Thanks,
David
 
G

Guest

This might do you if you have the report to Hide Duplicates --
SELECT production.tool_id, usage.use_id, failure.failure_id,
production.production_date
FROM (production LEFT JOIN [usage] ON production.tool_id = usage.tool_id)
LEFT JOIN failure ON production.tool_id = failure.tool_id;
 
J

John Vinson

What I need is a lifetime history of every individual tool, with production
date, every time it’s been used, and every time it’s failed. So the results
of my query should look like this, so that I can then easily generate a
report sorted by tool:
tool_id use_id failure_id production_date
tool1 (null) (null) production_date1
tool1 use1 (null) (null)
tool1 (null) failure1 (null)
tool1 (null) failure2 (null)
tool2 (null) (null) production_date2
Click to expand...

A UNION query will do what you want here. As you see, a Join matches a
given Use_ID up with a (completely unrelated) FailureID.

Essentially, you want one record per unique combination of tool and
(any one of) the three dates... right?

Try

SELECT Tool_ID, IIF(True, Date(), Null) As Use_Date, IIF(True, Date(),
Null) As Failure_Date, Production_Date FROM Tools
UNION ALL
SELECT Tool_ID, Use_Date, Null, Null FROM Usage
UNION ALL
SELECT Tool_ID, Null, Failure_Date, Null FROM Failures
ORDER BY Tool_ID;


John W. Vinson[MVP]
 
G

Guest

Karl-
Thanks for your help with this. John's suggestion of using a UNION did the
trick. I had tried using JOIN before, but the catch is that it ends up
combining, on the same row, columns that have nothing to do with each other,
for example the query you mentioned will show the following rows (among
others):
tool1 use1 failure1 production_date1
tool1 use1 failure2 production_date1
....

So it combines use1 with failure1 and failure2, even though these are
unrelated events. The UNION leaves empty fields blank. Thanks again for
your help - this has been a great opportunity to learn more about databases.

David

KARL DEWEY said:
This might do you if you have the report to Hide Duplicates --
SELECT production.tool_id, usage.use_id, failure.failure_id,
production.production_date
FROM (production LEFT JOIN [usage] ON production.tool_id = usage.tool_id)
LEFT JOIN failure ON production.tool_id = failure.tool_id;


David said:
Hello-
I've got a database to track the histories of individual tools, and want to
generate a report to show the lifetime history of each individual tool,
including all the times it’s been used and all the times it’s failed. Here's
how it's set up (note that the first column in each table is the primary key;
I have other fields with additional details, but this shows you the basic
structure):

Tools table (tracks the production of each tool):
tool_id production_date
tool1 production_date1
tool2 production_date2
tool3 production_date3
tool4 production_date4

usage table (tracks each time each tool is used):
use_id tool_id
use1 tool1
use2 tool2
use3 tool2
use4 tool4
use5 tool4

failures table (tracks failures for each tool):
failure_id tool_id
failure1 tool1
failure2 tool1
failure3 tool2

Note that any given tool can have zero or multiple use incidents with no
upper bound, as well as having zero or multiple failures with no upper bound.


What I need is a lifetime history of every individual tool, with production
date, every time it’s been used, and every time it’s failed. So the results
of my query should look like this, so that I can then easily generate a
report sorted by tool:
tool_id use_id failure_id production_date
tool1 (null) (null) production_date1
tool1 use1 (null) (null)
tool1 (null) failure1 (null)
tool1 (null) failure2 (null)
tool2 (null) (null) production_date2
tool2 use2 (null) (null)
tool2 use3 (null) (null)
tool2 (null) failure3 (null)
tool3 (null) (null) production_date3
tool4 (null) (null) production_date4
tool4 use4 (null) (null)
tool4 use5 (null) (null)

This is something that would be trivial in a spreadsheet, by simply copying
and pasting the 3 tables’ rows below each other, then moving the columns the
right, and finally sorting by tool_id. I need to do this in Access, though,
so that other people can generate the same query, and am having a hard time
of it. In particular, all the queries I try end up combining use_id and
failure_id on the same row in different combinations, when in reality they
are two completely separate incidents on separate dates.

Sorry about the long long-winded explanation, and thanks for your help.

Thanks,
David
Click to expand...
Click to expand...
 
G

Guest

John-
This did the trick - thanks for saving me more headaches. I had tried UNION
before, but was getting stuck on the fact that the columns on each table were
not common to the others, e.g. use_id is not present in the failures table,
while UNION expects the same columns on all of the rows that are meant to be
appended to each other. Your suggestion of inserting Null columns (and
renaming them with an AS statement) allowed me to have all of the columns in
the result, only populate them when there are actual data in the original
tables, and leave them blank when there is no relevant data.

For reference, below is the query and results I finally used (I didn't use
the date functions because use_id and failure_id are not dates; dates are not
unique identifiers because you could have multiple events on the same date):

SELECT tool_id,Null AS use_id, Null AS failure_id, production_date FROM tools;
UNION ALL
SELECT tool_id, use_id, null, null FROM usage
UNION ALL SELECT tool_id, null, failure_id, null FROM failures
ORDER BY Tool_ID;


"tool_id" "use_id" "failure_id" "production_date"
"tool1" "failure2"
"tool1" "failure1"
"tool1" "use1"
"tool1" "production_date1"
"tool2" "failure3"
"tool2" "use3"
"tool2" "use2"
"tool2" "production_date2"
"tool3" "production_date3"
"tool4" "use5"
"tool4" "use4"
"tool4" "production_date4"



Thanks,
David
 
Top