update 3 fields based on 3 joins

[msmcnewsxp]

i need to update 3 fields based on whether or not a record exists in 3
different tables.
this doesn't work but what will?

UPDATE ((tblDIRECTVIRUS INNER JOIN tblSpecimenProfiles ON
tblDIRECTVIRUS.CASE_NUMBER = tblSpecimenProfiles.CASE_NUMBER) INNER JOIN
tblSerology ON tblSpecimenProfiles.CASE_NUMBER = tblSerology.CASE_NUMBER)
INNER JOIN tblIDPA ON tblSpecimenProfiles.CASE_NUMBER = tblIDPA.CASE_NUMBER
SET tblSpecimenProfiles.DIRECT_VIRUS = True
WHERE (((tblSpecimenProfiles.CASE_NUMBER)=[tblDIRECTVIRUS].[case_number]))
tblSpecimenProfiles.SEROLOGY = True
WHERE (((tblSpecimenProfiles.CASE_NUMBER)=[tblSEROLOGY].[case_number])),
tblSpecimenProfiles.IDPA = True
WHERE (((tblSpecimenProfiles.CASE_NUMBER)=[tblIDPA].[case_number]));

thanks,
mcnews

 

[msmcnewsxp]

the conditions are
there are 3 yes/no (bit) fields that should be checked true if there exists
a record in the joined table.
for example DIRECT_VIRUS should be checked true in the Specimen table if the
is a matching case_number found in the DirectVirus table.

 

[John Vinson]

the conditions are
there are 3 yes/no (bit) fields that should be checked true if there exists
a record in the joined table.
for example DIRECT_VIRUS should be checked true in the Specimen table if the
is a matching case_number found in the DirectVirus table.

Ouch.

That's storing data redundantly, but I guess it might be useful...

One problem is that with INNER JOINS you won't even see any records
that don't have values in the linked tables; and you can't put three
Outer Joins into the query because you'll get an Ambiguous Outer Joins
message. I'd suggest either running three update queries, one for each
join:

UPDATE tblDIRECTVIRUS INNER JOIN tblSpecimenProfiles ON
tblDIRECTVIRUS.CASE_NUMBER = tblSpecimenProfiles.CASE_NUMBER
SET tblSpecimenProfiles.DIRECT_VIRUS = True;

and the same for the other two tables;

or if you really need to do this all in one query, use DLookUp:

UPDATE tblSpecimenProfiles
SET tblSpecimenProfiles.DIRECT_VIRUS = (Not
IsNull(DLookUp("[case_number]", "[tblDIRECTVIRUS]", "[case_number] = "
& [case_number])),
tblSpeciminProfiles.SEROLOGY = (Not IsNull(DLookUp("[case_number]",
"[tblSEROLOGY]", "[case_number] = " & [case_number])),
tblSpeciminProfiles.IDPA = (Not IsNull(DLookUp("[case_number]",
"[tblIDPA]", "[case_number] = " & [case_number]));

 

[John Vinson]

i already had the 3 query thing going. just wondering if it could be done
with one.
the users want to see which check boxes have been checked.
when they are checked they click a button that creates a record in one of
the 3 tables that match the checkbox.

<confusion>

The query will set the checkbox to True *if there is already a record
in the table*. Now you say you want to look at the checkbox and create
a record *if the checkbox is checked*!

Which comes first? The record, or the checkbox?