A
Andy
I have a query which utilises three other queries. There are occassions when
i go into design view and I get the 'Can't represent the join expression'
message and the link has gone between ([qMax Last OP
Contact].[MaxOfAppointment Date] = [PTL Appointments].[Appointment Date]) . I
can manually recreate the link and sometimes it stays for a few runs of the
query and then it goes again.
I'm worried that at times I am running a result from the product of this
query and not all of the joins are there. Detect and repair has been run to
no avail.
Anyway i can ensure that this link remains?
SELECT [qMax Last OP Contact].[Patient ID Key] AS [Pt ID], [qMax Last OP
Contact]![MaxOfAppointment Date] AS [Last Appt], [qMax Last OP
Contact].[Unique ID Episode], [PTL Appointments]![PCT Code at Appt date] AS
PCT, [PTL Appointments].[Appointment Type], [PTL Appointments].Consultant,
[PTL Appointments].[Consultant Code], [PTL Appointments].[Booking Type Code],
[PTL Appointments].[Booking Type], [PTL Appointments].[New / Review], [PTL
Appointments].[List Code], [PTL Appointments].[Clinic Name], [PTL
Appointments].[Treatment Status Code], [PTL Appointments].[Treatment Status],
[PTL Appointments].[Treatment Status Date], [PTL Appointments].[Request
Received Date], [PTL Appointments].[Referral Method Code], [PTL
Appointments].[Referral Method], [PTL Appointments].[Referral Source], [PTL
Appointments].[Referral Source Code], [PTL Appointments].[Decision To Refer
Date], [PTL Appointments].[Attendance Indicator], [PTL
Appointments].[Attendance Indicator Code], [PTL Appointments].[Specialty
National Code], [PTL Appointments].Specialty, [PTL Appointments].Outcome,
[PTL Appointments].[Outcome Code], [PTL Appointments].[Operation 1], [PTL
Appointments].[Operation 1 Code], [PTL Appointments].[Hospital Code], [PTL
Appointments].[Outcome Nat Code], [PTL Appointments].[Date Of Death], [PTL
Appointments].[Specialty Code], [PTL Appointments].[Clinic Purpose], [PTL
Appointments].[Patient Surname], [PTL Appointments].[Patient Forename 1],
#2/3/2008# AS [Census Date]
FROM ([PTL Appointments] LEFT JOIN [qPrevious Clock Stops] ON ([PTL
Appointments].[Patient ID Key] = [qPrevious Clock Stops].[Patient ID Key])
AND ([PTL Appointments].[Unique ID Episode] = [qPrevious Clock Stops].[Unique
ID Episode])) INNER JOIN [qMax Last OP Contact] ON ([qMax Last OP
Contact].[MaxOfAppointment Date] = [PTL Appointments].[Appointment Date]) AND
([PTL Appointments].[Patient ID Key] = [qMax Last OP Contact].[Patient ID
Key]) AND ([PTL Appointments].[Unique ID Episode] = [qMax Last OP
Contact].[Unique ID Episode])
WHERE ((([PTL Appointments].[Treatment Status Code])="8" Or ([PTL
Appointments].[Treatment Status Code])="11" Or ([PTL Appointments].[Treatment
Status Code])="15" Or ([PTL Appointments].[Treatment Status Code]) Is Null)
AND (([PTL Appointments].Outcome)<>"Discharge") AND (([PTL
Appointments].[Date Of Death]) Is Null) AND (([qPrevious Clock
Stops].[Patient ID Key]) Is Null));
i go into design view and I get the 'Can't represent the join expression'
message and the link has gone between ([qMax Last OP
Contact].[MaxOfAppointment Date] = [PTL Appointments].[Appointment Date]) . I
can manually recreate the link and sometimes it stays for a few runs of the
query and then it goes again.
I'm worried that at times I am running a result from the product of this
query and not all of the joins are there. Detect and repair has been run to
no avail.
Anyway i can ensure that this link remains?
SELECT [qMax Last OP Contact].[Patient ID Key] AS [Pt ID], [qMax Last OP
Contact]![MaxOfAppointment Date] AS [Last Appt], [qMax Last OP
Contact].[Unique ID Episode], [PTL Appointments]![PCT Code at Appt date] AS
PCT, [PTL Appointments].[Appointment Type], [PTL Appointments].Consultant,
[PTL Appointments].[Consultant Code], [PTL Appointments].[Booking Type Code],
[PTL Appointments].[Booking Type], [PTL Appointments].[New / Review], [PTL
Appointments].[List Code], [PTL Appointments].[Clinic Name], [PTL
Appointments].[Treatment Status Code], [PTL Appointments].[Treatment Status],
[PTL Appointments].[Treatment Status Date], [PTL Appointments].[Request
Received Date], [PTL Appointments].[Referral Method Code], [PTL
Appointments].[Referral Method], [PTL Appointments].[Referral Source], [PTL
Appointments].[Referral Source Code], [PTL Appointments].[Decision To Refer
Date], [PTL Appointments].[Attendance Indicator], [PTL
Appointments].[Attendance Indicator Code], [PTL Appointments].[Specialty
National Code], [PTL Appointments].Specialty, [PTL Appointments].Outcome,
[PTL Appointments].[Outcome Code], [PTL Appointments].[Operation 1], [PTL
Appointments].[Operation 1 Code], [PTL Appointments].[Hospital Code], [PTL
Appointments].[Outcome Nat Code], [PTL Appointments].[Date Of Death], [PTL
Appointments].[Specialty Code], [PTL Appointments].[Clinic Purpose], [PTL
Appointments].[Patient Surname], [PTL Appointments].[Patient Forename 1],
#2/3/2008# AS [Census Date]
FROM ([PTL Appointments] LEFT JOIN [qPrevious Clock Stops] ON ([PTL
Appointments].[Patient ID Key] = [qPrevious Clock Stops].[Patient ID Key])
AND ([PTL Appointments].[Unique ID Episode] = [qPrevious Clock Stops].[Unique
ID Episode])) INNER JOIN [qMax Last OP Contact] ON ([qMax Last OP
Contact].[MaxOfAppointment Date] = [PTL Appointments].[Appointment Date]) AND
([PTL Appointments].[Patient ID Key] = [qMax Last OP Contact].[Patient ID
Key]) AND ([PTL Appointments].[Unique ID Episode] = [qMax Last OP
Contact].[Unique ID Episode])
WHERE ((([PTL Appointments].[Treatment Status Code])="8" Or ([PTL
Appointments].[Treatment Status Code])="11" Or ([PTL Appointments].[Treatment
Status Code])="15" Or ([PTL Appointments].[Treatment Status Code]) Is Null)
AND (([PTL Appointments].Outcome)<>"Discharge") AND (([PTL
Appointments].[Date Of Death]) Is Null) AND (([qPrevious Clock
Stops].[Patient ID Key]) Is Null));