mcshaz
Technical User
- Aug 5, 2008
- 1
Thanks in advance for any help you may be able to provide - this one has really been stumping me. Apologies if my searching skills mean that I am duplicating a previous question:
I am working with 2 tables:
RACHS Diagnostic codes contains the fields
ANZPICS code - A diagnostic code
Jenkins number - the associated risk stratification, between 1 and 6
[Diagnostic Code Mismatch] - A union query containing the fields:
ICU_NO - a primary key from a list of all patients
Suggest - Referring to a more appropriate diagnostic code to improve risk stratification.
other fields
I am attempting to write an append query to a Table named Error list.
For each ICU_NO in [Diagnostic Code Mismatch], I would like to append 1 record with
a) ICU_NO
b) a [suggest] code that represents the highest [Jenkins number]. If a single ICU_NO is associated with multiple [suggest] codes that correlate to the highest [Jenkins number] then any one will do (say the first record that corresponds)
c) some of the other fields corresponding to that record in [Diagnostic Code Mismatch]
I do not actually need to append the [Jenkins number], just use it to select the appropriate records.
obviously I can get the highest number with the select query:
but this does not help me extract the [suggest] field associated with this highest [Jenkins number].
Any help in making this next leap in logic would be very much appreciated.
I am working with 2 tables:
RACHS Diagnostic codes contains the fields
ANZPICS code - A diagnostic code
Jenkins number - the associated risk stratification, between 1 and 6
[Diagnostic Code Mismatch] - A union query containing the fields:
ICU_NO - a primary key from a list of all patients
Suggest - Referring to a more appropriate diagnostic code to improve risk stratification.
other fields
I am attempting to write an append query to a Table named Error list.
For each ICU_NO in [Diagnostic Code Mismatch], I would like to append 1 record with
a) ICU_NO
b) a [suggest] code that represents the highest [Jenkins number]. If a single ICU_NO is associated with multiple [suggest] codes that correlate to the highest [Jenkins number] then any one will do (say the first record that corresponds)
c) some of the other fields corresponding to that record in [Diagnostic Code Mismatch]
I do not actually need to append the [Jenkins number], just use it to select the appropriate records.
obviously I can get the highest number with the select query:
Code:
SELECT [Diagnostic Code Mismatch].ICU_NO, Max([RACHS Diagnostic Codes].[Jenkins number]) AS [MaxOfJenkins number]
FROM [Diagnostic Code Mismatch] INNER JOIN [RACHS Diagnostic Codes] ON [Diagnostic Code Mismatch].Suggest = [RACHS Diagnostic Codes].[ANZPICS Codes]
GROUP BY [Diagnostic Code Mismatch].ICU_NO;
but this does not help me extract the [suggest] field associated with this highest [Jenkins number].
Any help in making this next leap in logic would be very much appreciated.