Difficult query?

[Leslie Isaacs]

I have a table [Entry] with text fields [PATIENT_ID], [read_code] and
[START_DATE] (plus others).
[START_DATE] is a text field in the format yyyymmdd.
In a module I have created the following:

Function mydate(START_DATE As String)
mydate = Right([START_DATE], 2) & "/" & mid([START_DATE], 5, 2) & "/" &
Left([START_DATE], 4)
End Function

Originally I had
CDate (Right([START_DATE], 2) & "/" & mid([START_DATE], 5, 2) & "/" &
Left([START_DATE], 4))
in the middle line, but for some reason this still resulted in a text field.
By using Cdate(mydate(.....)) in a query, though, I get what I want...
almost!

In the table, [read_code] represents the type of event, and [START_DATE]
represents the date of the event.

I need to extract the time gap, in weeks, between each patient's <notes
received> event (represented by a [read_code] value of "9311." and their
<[notes summarised]> event (represented by a [read_code] value of "9125."

I am only interested in records with a [notes received] value after 1 April
04.
For some [PATIENT_ID]s there will be no record with a [read_code] value of
"9125."

The SQL of my attempted query is below, but I keep getting the message "You
tried to execute a query that doesn't include the specified function
'IIf([read_code]="9311.",CDate(mydate([START_DATE])))' as part of an
aggregate fuunction"

I've tried all sorts and am getting nowhere.
Hope someone can help

Many thanks
Leslie Isaacs

My query:

SELECT ENTRY.PATIENT_ID, DateDiff("w",[notes received],[notes summarised])
AS weeks, IIf([read_code]="9311.",CDate(mydate([START_DATE]))) AS [notes
summarised] INTO [reg sum delay]
FROM ENTRY
WHERE (((ENTRY.READ_CODE)="9125." Or (ENTRY.READ_CODE)="9311.") AND
((IIf([read_code]="9125.",CDate(mydate([START_DATE]))))>#4/1/2004#))
GROUP BY ENTRY.PATIENT_ID;

 

[Leslie Isaacs]

Sorry - my latest attempted query is:

SELECT ENTRY.PATIENT_ID, DateDiff("w",[notes received],[notes summarised])
AS weeks, IIf([read_code]="9311.",CDate(mydate([START_DATE]))) AS [notes
summarised], IIf([read_code]="9125.",CDate(mydate([START_DATE]))) AS [notes
received] INTO [reg sum delay]
FROM ENTRY
WHERE (((ENTRY.READ_CODE)="9125." Or (ENTRY.READ_CODE)="9311."))
GROUP BY ENTRY.PATIENT_ID
HAVING
(((IIf([read_code]="9125.",CDate(mydate([START_DATE]))))>#4/1/2004#));

Les

 

[Guest]

Hi Les,

Without looking at all of what you are trying to do in detail, it looks like
the error that you are getting is resulting from the fact that you are
grouping by the Patient_ID, but your various functions do not specify any
type of aggregate function.

For instance, take your first calculation:

DateDiff("w",[notes received],[notes summarised])

Lets say that you have 5 records for Patient_ID = 1 in your Entry table.
The query is only going to output one record for this Patient_ID because you
are grouping by that field. So, Access doesn't know what values to use for
[notes received] and [notes summarised] (record 1, 2, 3, 4, 5).

But, if you change your statement to something like:

DateDiff("w",max([notes received]),max([notes summarised]))

Then Access will know to use the max value for each field within those 5
records in your DateDiff() function.

Other aggregates you could use include Min(), First(), Last(), Count(),
Sum(), and I'm sure there are others that don't come to mind right now.
These are all listed in the expression builder under aggregate functions.
One note though, the First() and Last() functions are almost useless, because
there is really no way to know what value will be returned. Well, actually
the First() function can be useful in some cases if you just want to bring
back the first match for speed, and it really doesn't make a difference what
record the value comes from.

So, I would say that you should go through all of your calculations and
adjust them as above. Or, you could get rid of the Group By clause and then
everything will work fine. If you do that, you could create another query to
group the results, in which case all you would have to do would be to specify
whether you want the Min, Max, etc for each column (other than the GroupBy
column(s)).

Hopefully that will help. Post back if you have other questions.

-Ted Allen

Sorry - my latest attempted query is:

SELECT ENTRY.PATIENT_ID, DateDiff("w",[notes received],[notes summarised])
AS weeks, IIf([read_code]="9311.",CDate(mydate([START_DATE]))) AS [notes
summarised], IIf([read_code]="9125.",CDate(mydate([START_DATE]))) AS [notes
received] INTO [reg sum delay]
FROM ENTRY
WHERE (((ENTRY.READ_CODE)="9125." Or (ENTRY.READ_CODE)="9311."))
GROUP BY ENTRY.PATIENT_ID
HAVING
(((IIf([read_code]="9125.",CDate(mydate([START_DATE]))))>#4/1/2004#));

Les

I have a table [Entry] with text fields [PATIENT_ID], [read_code] and
[START_DATE] (plus others).
[START_DATE] is a text field in the format yyyymmdd.
In a module I have created the following:

Function mydate(START_DATE As String)
mydate = Right([START_DATE], 2) & "/" & mid([START_DATE], 5, 2) & "/" &
Left([START_DATE], 4)
End Function

Originally I had
CDate (Right([START_DATE], 2) & "/" & mid([START_DATE], 5, 2) & "/" &
Left([START_DATE], 4))
in the middle line, but for some reason this still resulted in a text field.
By using Cdate(mydate(.....)) in a query, though, I get what I want...
almost!

In the table, [read_code] represents the type of event, and [START_DATE]
represents the date of the event.

I need to extract the time gap, in weeks, between each patient's <notes
received> event (represented by a [read_code] value of "9311." and their
<[notes summarised]> event (represented by a [read_code] value of "9125."

I am only interested in records with a [notes received] value after 1 April
04.
For some [PATIENT_ID]s there will be no record with a [read_code] value of
"9125."

The SQL of my attempted query is below, but I keep getting the message "You
tried to execute a query that doesn't include the specified function
'IIf([read_code]="9311.",CDate(mydate([START_DATE])))' as part of an
aggregate fuunction"

I've tried all sorts and am getting nowhere.
Hope someone can help

Many thanks
Leslie Isaacs

My query:

SELECT ENTRY.PATIENT_ID, DateDiff("w",[notes received],[notes summarised])
AS weeks, IIf([read_code]="9311.",CDate(mydate([START_DATE]))) AS [notes
summarised] INTO [reg sum delay]
FROM ENTRY
WHERE (((ENTRY.READ_CODE)="9125." Or (ENTRY.READ_CODE)="9311.") AND
((IIf([read_code]="9125.",CDate(mydate([START_DATE]))))>#4/1/2004#))
GROUP BY ENTRY.PATIENT_ID;