Creating a new column in a query 1

[Delboy14]

(Access XP)
I have 2 queries on a group of patients, one lists those that are not well controlled after their first consultation and the other lists those that are not well controlled after their last consultation.
The query on both selects those patients who are not well controlled.

I would like to create one large query, which gives each patient a value of 0-3; if they are not well controlled first consultation then 0, well controlled first consult 1, not well controlled last consult 2, well controlled last consult 3.

Is there a way to do this?

I think it may be something like if query is true then assign the value in the column to be 0, then perform a UNION with the next query for the next value.

Hopefully the table I will end up with will look like this
PatientIndex Control
1 0
2 0
3 2
4 3
5 1
6 1
7 3
8 2

 

[tlbroadbent]

Without more info about the structure of your tables and queries, I'm only able to guess at a solution. However, this may be close enough to give you some ideas. I've guessed at table and query names. Please adjust as needed.

Select PatientIndex, 0 As Control
From qryNotControlledFirstConsult
Union
Select PatientIndex, 1 As Control
From Patients Where PatientIndex Not In (Select PatientIndex From qryNotControlledFirstConsult)
Union
Select PatientIndex, 2 As Control
From qryNotControlledLastConsult
Union
Select PatientIndex, 3 As Control
From Patients Where PatientIndex Not In (Select PatientIndex From qryNotControlledLastConsult) Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin