Combining queries in one report

[PeterBorroloola]

I’ve written a VB6 program that’s a front-end for an Access 97 database of patients, all with a particular disease.

I’m trying to put together a report, using Crystal Reports 4.6, to show when patients need to have a particular test. The trouble is, patients need the test at different frequencies, depending on their ‘priority’ (how sick they are) and on their age.

Child Priority 1, Adult Priority 1, Child Priority 2 – Every year
Child Priority 3, Adult Priority 2 – Every 2 years
Adult Priority 3 – Every 5 years

All the data is in one table (Main), including a date field for when they had their last test (‘LastTest’). I can put together three different queries for the three different periods, like this:

SELECT FirstName, LastName, (LastTest+365) AS NextTest1Yr FROM Main
WHERE (Priority=1)
OR ((Priority=2) AND (DateDiff("m",DateOfBirth,Now()))<192);

SELECT FirstName, LastName, (LastTest+730) AS NextTest2Yr FROM Main
WHERE ((Priority=3) AND (DateDiff("m",DateOfBirth,Now()))<192)
OR ((Priority=2) AND (DateDiff("m",DateOfBirth,Now()))>=192);

SELECT FirstName, LastName, (LastTest+1826) AS NextTest5Yr FROM Main
WHERE ((Priority=3) AND (DateDiff("m",DateOfBirth,Now()))>=192);

… but for the report it doesn’t matter about their priorities or ages, I just want one list of all the patients who need the test, and when.

Can anyone suggest:
(iv) a single query that will show all the patients needing a test, or
(v) a way of reporting that combines the three queries (and preferably in a way I can group them all and sort them in name order), or
(vi) a completely different way of doing this

I’ve used subreports in Access ’97 before, but I can’t work out how to do that with CR 4.6.

Also, if there is nothing in the field ‘LastTest’ (i.e. they’ve never had the test) or the date for ‘NextTest’ is in the past, I would like the report to show 'ASAP’. I can’t work out a way to do that with SQL, or with CR.

TIA, Peter

[PeterBorroloola]

Just if anyone's wondering ... the UNION SQL keyword does it - I wrote a new query in the Access db, thus:

SELECT * FROM Test1Yr UNION SELECT * FROM Test2Yr UNION SELECT * FROM Test5Yr;

That shows all the patients that need tests, regardless of the other criteria. Yippee!

I'm not sure this would work if the queries weren't mutually exclusive (i.e. each patient only shows up in one query.

Cheers, Peter