Re: Create field from table with no join

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
..

"Andy" <Andy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DBB5D627-6B71-4B0B-8387-83D68B075DED@xxxxxxxxxxxxxxxx
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 Spencer" wrote:

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" <Andy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A70B20CE-2935-4928-8AF4-A326577CC42A@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • RE: Relationships, Default Numbers, Queries
    ... FROM tblAgency INNER JOIN (tblContactInfo INNER JOIN (tblLOC INNER JOIN ... If you post your query SQL folks can suggest how to make it work. ... I need a query that tells me about the census events for a given census date ... rather than the 100 records with all the zeros. ...
    (microsoft.public.access.tablesdbdesign)
  • List of Occupations - 1901 census
    ... Is there a web-site I can access to try to identify occupations on the 1901 ... census. ... I googled but couldn't find anything that answered my query. ...
    (soc.genealogy.britain)
  • Re: RM formalism supporting partial information
    ... projection there may be so much missing information (eg all ... If the user formulates a query then this will now ... Consider a query to find all the 27 year old pilots from a census ... Whether the suggested computation gives you all possible ...
    (comp.databases.theory)
  • Re: Report QueryDef
    ... >I have been modifying the SQL statement for the query in code, ... >SQL statement (vice pointing to the saved Query object) as the RecordSource ... >in my report, and altered its value in code. ...
    (microsoft.public.access.reports)
  • Re: Nested MSAccess Union-Queries in asp
    ... I'm surprised it runs in Access without an alias for the subquery. ... I would put the whole thing into a saved query and execute the ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)