Create field from table with no join

[Andy]

I have a table that holds a master census date. I need to add this census
date to be generated in a field in another query, but obviously cannot
provide any linkage. Short of hard-coding this date (which changes every
week) I cannot get the date to be generated within the query,

HELP!

Ta

 

[John W. Vinson]

I have a table that holds a master census date. I need to add this census
date to be generated in a field in another query, but obviously cannot
provide any linkage. Short of hard-coding this date (which changes every
week) I cannot get the date to be generated within the query,

HELP!

Ta

Is this "master census date" table a one-row table? If so just include it in
the query with NO join line. Its single record will be paired with every
record in the query.

If it's a multirow table then you'll need to explain the logic; how can you
determine WHICH row in the table goes with WHICH row in the query?

 

[John Spencer]

Does the table with the master census date hold only one record or does it
have many records?

If it is one record then just add the table to the query with NO join and
the value of that one record will be available.

If there are multiple records, how do you identify which records in the
table you want? If you can do so in a query, you can build a query to pull
out the ONE record and then include the query in your other query. AGAIN
with no join set. This type of join (cartesian join) simply pairs every
record in one table (or query) with every record in the other table (or
query).

This means that your single value is going to be available for every record.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Andy]

No, it is just one value that I want to generate in all rows of the query.
However when putitng it in onjoined, I get the 'ambiguous outer joins' error
message.

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],
[Census Date Table]![Census Date] AS [Census Date]
FROM [Census Date Table], ([PTL Appointments] LEFT JOIN [qPrevious Clock
Stops] ON [PTL Appointments].[Unique ID Episode] = [qPrevious Clock
Stops].[Unique ID Episode]) INNER JOIN [qMax Last OP Contact] ON ([PTL
Appointments].[Appointment Date] = [qMax Last OP Contact].[MaxOfAppointment
Date]) 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") AND (([PTL Appointments].Outcome)<>"Discharge") AND
(([PTL Appointments].[Date Of Death]) Is Null) AND (([qPrevious Clock
Stops].[Patient ID Key]) Is Null));

 

[John W. Vinson]

No, it is just one value that I want to generate in all rows of the query.
However when putitng it in onjoined, I get the 'ambiguous outer joins' error
message.

You'll probably need to save the query without the census year table, and
create a second query using the Cartesian join - add the saved query and the
census year table (and the field from the census year table).

Alternatively you could just put a DLookUp() to look up the census year as a
calculated field. With only one row to look up it shouldn't be an expensive
operation.

 

[John Spencer]

You might have to learn to do the two-step.

Save your query without the Census Date Table.

Now use the saved query and the Census Date Table
Select *
FROM [Census Date Table], {Name of the saved query)
..
There are other options -
(A) Use the DLookup Function to get the value
(B) Display the value on an open form and reference the form

(A)
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]

, DLOOKUP ("[Census Date","[Census Date Table]") as [Census Date]

FROM ([PTL Appointments] LEFT JOIN [qPrevious Clock
Stops] ON [PTL Appointments].[Unique ID Episode] = [qPrevious Clock
Stops].[Unique ID Episode]) INNER JOIN [qMax Last OP Contact] ON ([PTL
Appointments].[Appointment Date] = [qMax Last OP Contact].[MaxOfAppointment
Date]) 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") AND (([PTL Appointments].Outcome)<>"Discharge") AND
(([PTL Appointments].[Date Of Death]) Is Null) AND (([qPrevious Clock
Stops].[Patient ID Key]) Is Null));

(B)
SELECT [qMax Last OP Contact].[Patient ID Key] AS [Pt ID]
....
, [Forms]![Name of your Form]![Name of Control with Census Date] as [Census
Date]

FROM ([PTL Appointments] LEFT JOIN [qPrevious Clock
Stops] ON [PTL Appointments].[Unique ID Episode] = [qPrevious Clock
Stops].[Unique ID Episode]) INNER JOIN [qMax Last OP Contact] ON ([PTL
Appointments].[Appointment Date] = [qMax Last OP Contact].[MaxOfAppointment
Date]) 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") AND (([PTL Appointments].Outcome)<>"Discharge") AND
(([PTL Appointments].[Date Of Death]) Is Null) AND (([qPrevious Clock
Stops].[Patient ID Key]) Is Null));

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

No, it is just one value that I want to generate in all rows of the query.
However when putitng it in onjoined, I get the 'ambiguous outer joins'
error
message.

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],
[Census Date Table]![Census Date] AS [Census Date]
FROM [Census Date Table], ([PTL Appointments] LEFT JOIN [qPrevious Clock
Stops] ON [PTL Appointments].[Unique ID Episode] = [qPrevious Clock
Stops].[Unique ID Episode]) INNER JOIN [qMax Last OP Contact] ON ([PTL
Appointments].[Appointment Date] = [qMax Last OP
Contact].[MaxOfAppointment
Date]) 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") AND (([PTL Appointments].Outcome)<>"Discharge") AND
(([PTL Appointments].[Date Of Death]) Is Null) AND (([qPrevious Clock
Stops].[Patient ID Key]) Is Null));

Does the table with the master census date hold only one record or does
it
have many records?

If it is one record then just add the table to the query with NO join and
the value of that one record will be available.

If there are multiple records, how do you identify which records in the
table you want? If you can do so in a query, you can build a query to
pull
out the ONE record and then include the query in your other query. AGAIN
with no join set. This type of join (cartesian join) simply pairs every
record in one table (or query) with every record in the other table (or
query).

This means that your single value is going to be available for every
record.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..