DB design complexity for report set up

Hi, All--

I have a report request that would show results of different medical lab tests over the course of different clinic visits over the year. The requested design is to have the test type as the row heading and the appointment date as a column heading, in a crosstab-type configuration. Unfortunately, the DB isn't designed that way. The different lab test types are individual fields in the table along with a date field. With this design, a reqular crosstab approach will not work (at least as I have been able to figure out).

Right now all I can seen to have is a regular report with the the date field and the multiple lab test fields be the column headers, but I'm not able to have the different lab tests as a row header with outcomes for the different dates across columns which is what is requested.

Does anyone have any ideas on how I could get the requested report design using the current table design?

Much abliged!

Paul

One thought is to use a UNION query to get the data into a normalized structure, and then run a crosstab query against that. Does that sound feasible?

pbaldy said:

One thought is to use a UNION query to get the data into a normalized structure, and then run a crosstab query against that. Does that sound feasible?

Click to expand...

Hi, Paul. Thanks for your response and suggestion.

I'm not SQL savvy enough to do that, but your suggestion did prompt another possible solution. Maybe I can use Visual Basic to restructure the data into another table using different querydefs. A bit convoluted, perhaps, but I'll give it a shot.

Thanks for your help.

Paul

You probably can, but the UNION would be simpler. It sounds like there's a date field for each test type?

SELECT TestType1, TestType1Date
FROM TableName
UNION ALL
SELECT TestType2, TestType2Date
FROM TableName
UNION ALL
...

Perhaps adding a WHERE clause to each if the test type could be Null.

Use a query for each field one at a time, switch to SQL view, just copy the text from there and paste it into the first one that you've already switched to SQL view. Just remember to put in the Union statements between each statement

Intriguing--

Thanks to both of you. I'll give it a shot.

Have a great weekend.

Paul

Worked like a charm--

Interesting thing, these union queries.

Thanks again, guys.

Happy to help. They are a handy tool to pull non-normalized data into an easier to work with structure.