array type report

[Ericnz]

Hi all, I want to produce a report with client names listed down left column
with dates treated accoss top ( 5 or 6 different dates).
Clients are not seen every time I visit the RestHomes . So the DB records
for each client only record visit dates. The final report would have dates
along the top with a tick or X in the box underneath along the relevant name
to signiofy that they were seen - or a null if not.
Ive extracted unique names of clients for the column and visit dates but
can't figure out how to go through client names and extract dates etc for the
final format .
Help please ( thoughts have been to load and array ? )
Probably something easy?
Thanks

 

[John W. Vinson]

Hi all, I want to produce a report with client names listed down left column
with dates treated accoss top ( 5 or 6 different dates).
Clients are not seen every time I visit the RestHomes . So the DB records
for each client only record visit dates. The final report would have dates
along the top with a tick or X in the box underneath along the relevant name
to signiofy that they were seen - or a null if not.
Ive extracted unique names of clients for the column and visit dates but
can't figure out how to go through client names and extract dates etc for the
final format .
Help please ( thoughts have been to load and array ? )
Probably something easy?
Thanks

Try basing the report on a Crosstab Query using the patient name as the Row
Header and the visit date as the Column Header.

 

[Ericnz]

Thanks for prompt reply John. I was right - there was an easy way!!
Yes it works .I'll just have to change the Client ID to an X and we'll be
there.
Another question though. Some treatments to a rest home are based over 3-4
days. Can we group them on weeks . Crosstab format shows quarters, date,
date-time. Any simple ideas!?

 

[John W. Vinson]

Thanks for prompt reply John. I was right - there was an easy way!!
Yes it works .I'll just have to change the Client ID to an X and we'll be
there.
Another question though. Some treatments to a rest home are based over 3-4
days. Can we group them on weeks . Crosstab format shows quarters, date,
date-time. Any simple ideas!?

Weeks are tricky because they don't necessarily line up with calendar
quarters. Is a week in the 1st quarter if Sunday is the 31st of March? Or in
the 2nd quarter?

That said, you can use the DatePart function:

WeekNo: DatePart("ww", [AdmissionDate])

This will calculate the week number in the year. See the online help for
DatePart, there are various options for specifying when the week starts, what
is week 1, etc.