Display results as quarterly lists 1

[Sylvia589943]

I have an Access Database and need to display results as quarterly lists on one report - e.g.:

Last 3 months 6 months 9 month 12 months
Jim John Sam Richard
Lilly Robert Jenny
Bill
Judy


I'm not sure if I would do this at the query stage (if so how) or at the Report stage (again - how?)

Many thanks for any help you can offer.

 

[dhookom]

Do you have a table structure and sample records you could share with us? Your answer might be a crosstab query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sylvia589943]

The Table shows:-
TestID (autonumber)
PatientNo:
ECG
Angiogram
Echo
LVSD

With the exception of the PatientNo: field, the remainder are dates. I have a query using "maxof.." to give me the latest date for each test for each patient (is this the best way to do this??)

We would like the Report to show the results over 3, 6, 9 months across the page as you might see a debtors list.

Many thanks for you help and iterest.

 

[dhookom]

Your table is not normalized. You have field names that should be data values. If you add another test, you shouldn't have to modify tables, queries, forms, reports,...

However, is your final result based on the max date from any particular field? Is the test significant in your required display?

I would have created a test table like:
[tt]
tblTestDates
============
PatientNo
TestType
TestDate
[/tt]
Each individual test for each patient would create a new record in the table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sylvia589943]

Thank you - we need to know the last date for each test for each patient - eg: last echo; last ECG; last angiogram - etc for each patient not just the latest test date overall - does that make sense??

 

[dhookom]

Your request makes sense but your first posting didn't have any mention of the test. Could you provide a sample output based on test?

Is it possible to change your table structure as suggested? If not, you will need to create a union query:

==quniTestDates========
SELECT TestID, PatientNo, "ECG" as TestType, ECG As TestDate
FROM tblYouDidNotTellUs
WHERE ECG Is Not Null
UNION ALL
SELECT TestID, PatientNo, "Angiogram", Angiogram
FROM tblYouDidNotTellUs
WHERE Angiogram Is Not Null
UNION ALL
SELECT TestID, PatientNo, "Echo", Echo
FROM tblYouDidNotTellUs
WHERE Echo Is Not Null
UNION ALL
SELECT TestID, PatientNo, "LVSD", LVSD
FROM tblYouDidNotTellUs
WHERE LVSD Is Not Null;



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GreggyBoy]

This is a few weeks later, but you create a new table consisting of the latest records of each patient. The create a report on that table (directly or via a query).

In the Report wizard select the fields you want. Then, under Groupings, select the date field again, and press the Grouping Options buttons and then under Grouping Intervals you can select Quarterly.