Ensure a non-key field uniquely matches one field of compound key

[JohnM]

New job, new database, new problems.

There is a table with a compound key on PatientNo and AdmissionNo.
There is an additional field, StudyCode, which is dependent on
PatientNo. Each unique PatientNo should match a unique StudyCode.
However, this is not the case.

How do you suggest I ensure that this rule is enforced? Currently I
am finding individual PatientNo's with two StudyCodes. This seems to
be the result of patients transferring between hospital sites, each
with their own copy of the database, and inadvertently being assigned
different StudyNo's at each site.

I would be grateful for any help, particularly if you are familiar
with this sort of scenario and can point me in the direction of
resources I might draw upon. I guess this is ultimately about
ensuring correct work practices, negotiated between the sites, but
wonder if there is a temporary fix I can apply by way of, for example,
an index (although I can't figure out how - I think I'm barking up the
wrong tree with that).

Kind regards,
John McTigue

 

[John Spencer]

You can identify the instances of PatientNo that have 2 or more StudyCodes
with a query like the following:

SELECT PatientNo, StudyCodes
FROM [SomeTable]
WHERE PatientNo in
(SELECT PatientNo
FROM [SomeTable]
GROUP BY PatientNo
HAVING Max(StudyCode) <> Min(StudyCode))

To check during data entry at the same site, you could use some a query like

SELECT Count(*)
FROM [SomeTable]
WHERE PatientNo = [Specify Patient Number]
HAVING Max(StudyCode) <> Min(StudyCode)

But if you have two (or more) separate sites entering into separate databases
then there is no way to control the entry other than administrative practices.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JohnM]

You can identify the instances of PatientNo that have 2 or more StudyCodes
with a query like the following:

SELECT PatientNo, StudyCodes
FROM [SomeTable]
WHERE PatientNo in
    (SELECT PatientNo
     FROM [SomeTable]
     GROUP BY PatientNo
     HAVING Max(StudyCode) <> Min(StudyCode))

To check during data entry at the same site, you could use some a query like

     SELECT Count(*)
     FROM [SomeTable]
     WHERE PatientNo = [Specify Patient Number]
     HAVING Max(StudyCode) <> Min(StudyCode)

But if you have two (or more) separate sites entering into separate databases
then there is no way to control the entry other than administrative practices.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

New job, new database, new problems.

There is a table with a compound key on PatientNo and AdmissionNo.
There is an additional field, StudyCode, which is dependent on
PatientNo.  Each unique PatientNo should match a unique StudyCode.
However, this is not the case.

How do you suggest I ensure that this rule is enforced?  Currently I
am finding individual PatientNo's with two StudyCodes.  This seems to
be the result of patients transferring between hospital sites, each
with their own copy of the database, and inadvertently being assigned
different StudyNo's at each site.

I would be grateful for any help, particularly if you are familiar
with this sort of scenario and can point me in the direction of
resources I might draw upon.  I guess this is ultimately about
ensuring correct work practices, negotiated between the sites, but
wonder if there is a temporary fix I can apply by way of, for example,
an index (although I can't figure out how - I think I'm barking up the
wrong tree with that).

Kind regards,
John McTigue- Hide quoted text -

- Show quoted text -

John,

Thank you for your reply. Your query for checking data entry looks
like just what I need. However, I think you are correct in that
ultimately this is an administrative problem that requires changed
work practices.

Kind regards,
JohnM