Calculated data required in additional column in report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hope this makes sense.

I have a report with the following columns: -

Customer ID Service Start date of service End date of
service

Report criteria for report is set up so that Service only displays Service
A, Service B, and Service C, but not Service Z, so we have the following: -

Customer ID Service Start date of service End date of
service
01 Service A 01/01/07
03/01/07
02 Service B 13/02/07
27/02/07
03 Service B 14/04/07
21/05/07
04 Service C 19/03/07
23/03/07

Although records with Service Z are not displayed in the report, any record
with Service Z does have have a start date and end date.

I would like to create an additional column in the report that indicates
(Y/N) if the following applies: -

Did customer have an instance of Service Z occuring before an instance of
Service A, Service B, or Service C, the end date of Service Z occuring 28
days or less before the start of this instance of Service A, Service B, or
Service C.

The result would be along the lines of: -

Customer ID Service Start date of service End date of
service Y/N
01 Service A 01/01/07
03/01/07 Y
02 Service B 13/02/07
27/02/07 N
03 Service B 14/04/07
21/05/07 Y
04 Service C 19/03/07
23/03/07 Y

In anticipation, thank you for your help.

David
 
The said:
Hope this makes sense.

I have a report with the following columns: -

Customer ID Service Start date of service End date of
service

Report criteria for report is set up so that Service only displays Service
A, Service B, and Service C, but not Service Z, so we have the following: -

Customer ID Service Start date of service End date of
service
01 Service A 01/01/07
03/01/07
02 Service B 13/02/07
27/02/07
03 Service B 14/04/07
21/05/07
04 Service C 19/03/07
23/03/07

Although records with Service Z are not displayed in the report, any record
with Service Z does have have a start date and end date.

I would like to create an additional column in the report that indicates
(Y/N) if the following applies: -

Did customer have an instance of Service Z occuring before an instance of
Service A, Service B, or Service C, the end date of Service Z occuring 28
days or less before the start of this instance of Service A, Service B, or
Service C.

The result would be along the lines of: -

Customer ID Service Start date of service End date of
service Y/N
01 Service A 01/01/07
03/01/07 Y
02 Service B 13/02/07
27/02/07 N
03 Service B 14/04/07
21/05/07 Y
04 Service C 19/03/07
23/03/07 Y
Click to expand...


If I followed all that, I think you can get away with using
an expression in the Y/N text box:

=IIf(IsNull(DLookup("1", "thetable", "enddate>" &
Format(startdate-28, "\#m\/d\/yyyy\#") & " And [Customer
ID]=" & [Customer ID] & " And Service = 'Z'")

A possible quicker way would be to modify the report's
record souurce query to use a complicated outer join to a
subquery that selects just the Z recrods.
 
Back
Top