My tables represent, warranty data, problems at the factory and more failure
data. Unforunately there are multiple occurances of serial numbers in all 3
tables so the relationships are many-to-many...I know this isn't a good
thing, but I didn't know what else to do. Essentially what I want is to be
able to see all cases of a serial number being involved in all three because
it should help us improve quality if we can identify a reocurring problem
over all 3 tables. I also found out that in one of my tables the the first
digit and the last 4 digits of some serial numbers were not entered. I think
that means I need to change to a like condition, but when I wrote it up I
return no results.

SELECT QIT_LWH_IMP_tbl.PIN, QIT_LWH_IMP_tbl.[Build Date],
QIT_LWH_IMP_tbl.[Claim Number], QIT_LWH_IMP_tbl.[Part Number],
QIT_LWH_IMP_tbl.[Failure Mode Code], QIT_LWH_IMP_tbl.[Failure Reason Code]
FROM QIT_LWH_IMP_tbl, QIT_DTAC_IMP_tbl, QIT_Z3_QNOTE_tbl
WHERE (((QIT_LWH_IMP_tbl.PIN) Like '*QIT_DTAC_IMP_tbl.PIN*' And
(QIT_LWH_IMP_tbl.PIN) Like '*QIT_Z3_QNOTE_tbl.[Serial Number]*'));


"Dirk Goldgar" wrote:

"LFC" wrote in message
...
This is my sql statement so far:

SELECT QIT_LWH_IMP_tbl.PIN, QIT_DTAC_IMP_tbl.Created,
QIT_LWH_IMP_tbl.[Build
Date], QIT_LWH_IMP_tbl.[Claim Number], QIT_LWH_IMP_tbl.[Part Number],
QIT_LWH_IMP_tbl.[Failure Mode Code], QIT_Z3_QNOTE_tbl.[Where Found],
QIT_Z3_QNOTE_tbl.Reviewed, QIT_Z3_QNOTE_tbl.Comments,
QIT_Z3_QNOTE_tbl.[Coorelation Confirmed]
FROM (QIT_LWH_IMP_tbl INNER JOIN QIT_DTAC_IMP_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_DTAC_IMP_tbl.PIN) INNER JOIN QIT_Z3_QNOTE_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_Z3_QNOTE_tbl.[Serial Number]
WHERE (((QIT_LWH_IMP_tbl.PIN) Is Not Null) AND ((QIT_Z3_QNOTE_tbl.[Serial
number]) Is Not Null) AND ((QIT_DTAC_IMP_tbl.PIN) Is Not Null));

I feel like I'm just one step away because if I include group by on the
QIT_LWH_IMP_tbl.PIN then I get exactly what I'm looking for.
Unfortunately I
can't figure out a way around it. I tried doing
DISTINCT(QIT_LWH_IMP_tbl.PIN) and it cut the results down significantly,
but
not as much as I would like.


You don't need your WHERE clause (in the above SQL), because the inner joins
on those fields will automatically exclude any records where the joined
fields are Null.

But the problem is that your joins will naturally create duplicate output
records if there are more than one record in any table with the same PIN or
[Serial Number]. From your description, I think that must be at the root of
the problem you're facing. So long as your query must output fields from
all three tables, as you have it defined now, there is no way to reduce
these apparent duplicate records to a single record (whether by GROUP BY or
DISTINCT) and have that record be wholly updatable. That flows naturally
from the fact that any record that is collapsed from multiple records can't
have its data tracked back to a single source record to be updated.

We need to step back and look at what you're trying to do from a broader
perspective. Since one-to-many relationships seem to be involved, maybe a
form/subform arrangement would suit your needs. Or, it *would* be possible
to write a query to extract all records in one table for which there are
matches in the other tables, and have that query be updatable -- so long as
the query doesn't need to return any fields from the other tables.

Could you explain in more detail ...

1. What your tables represent,
2. What the relationships between the tables are, and
3. What you are really trying to do here?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)