Lab report 1

[Klo]

My boss has decided he want to change our lab reports to show the last 5 series of tests. There are about 10 tests per date. I know I can run a query that orders the tests by date. My questions are:
1. How do I populate the report in columns by date?
2. What if there is less than 5 test series or more than 5?
Thank for your help.

 

[dhookom]

Do you have a table structure and sample records that you would like to share? We don't have a clue if your tables are properly normalized or set up like a spreadsheet.

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]

 

[Klo]

The query will return data like this:



Date Patient ID Test_full_name Concentration Units
6/15/2004 9995.1 T. Billirubin 0.54 mg/dL
6/15/2004 9995.1 Creatinine 1.05 mg/dL
6/15/2004 9995.1 Albumin 4.12 g/dL
6/15/2004 9995.1 Potassium 4.32 mmol/L
6/15/2004 9995.1 Total Protein 6.90 g/dL
6/15/2004 9995.1 BUN 9.51 mg/dL
6/15/2004 9995.1 Calcium 10.00 mg/dL
6/15/2004 9995.1 AST 23.08 U/L
6/15/2004 9995.1 ALT 27.75 U/L
6/15/2004 9995.1 CO2 31.20 mmol/L
6/15/2004 9995.1 HDL 65.03 mg/dL
6/15/2004 9995.1 Alkaline Phosphate 73.10 U/L
6/15/2004 9995.1 Glucose 98.51 mg/dL
6/15/2004 9995.1 Triglycerides 106.96 mg/dL
6/15/2004 9995.1 Chloride 107.05 mmol/L
6/15/2004 9995.1 LDL (calc) 113.95 mg/dL
6/15/2004 9995.1 Sodium 145.99 mmol/L
6/15/2004 9995.1 Cholesterol 200.37 mg/dL
12/23/2004 9995.1 ALT 18.19 U/L
12/23/2004 9995.1 AST 19.49 U/L
12/23/2004 9995.1 HDL 70.18 mg/dL
12/23/2004 9995.1 LDL (calc) 89.11 mg/dL
12/23/2004 9995.1 Triglycerides 124.60 mg/dL
12/23/2004 9995.1 Cholesterol 184.21 mg/dL
6/16/2005 9995.1 Cholesterol 198.04 mg/dL
6/16/2005 9995.1 AST 17.91 U/L
6/16/2005 9995.1 ALT 17.58 U/L
6/16/2005 9995.1 HDL 60.50 mg/dL
6/16/2005 9995.1 Triglycerides 101.42 mg/dL
6/16/2005 9995.1 LDL (calc) 117.26 mg/dL
12/14/2005 9995.1 T. Billirubin 0.64 mg/dL
12/14/2005 9995.1 Creatinine 1.02 mg/dL
12/14/2005 9995.1 Potassium 4.32 mmol/L
12/14/2005 9995.1 Albumin 4.93 g/dL
12/14/2005 9995.1 Total Protein 7.12 g/dL
12/14/2005 9995.1 Calcium 10.30 mg/dL
12/14/2005 9995.1 BUN 10.81 mg/dL
12/14/2005 9995.1 ALT 21.52 U/L
12/14/2005 9995.1 AST 23.63 U/L
12/14/2005 9995.1 CO2 34.99 mmol/L
12/14/2005 9995.1 HDL 77.22 mg/dL
12/14/2005 9995.1 Alkaline Phosphate 83.36 U/L
12/14/2005 9995.1 Glucose 88.90 mg/dL
12/14/2005 9995.1 Triglycerides 99.62 mg/dL
12/14/2005 9995.1 Chloride 106.87 mmol/L
12/14/2005 9995.1 LDL (calc) 129.25 mg/dL
12/14/2005 9995.1 Sodium 146.79 mmol/L
12/14/2005 9995.1 Cholesterol 226.39 mg/dL

What I need is a report that will list dates across the top and the results in rows. The test dates change as well as the number of dates.

 

[dhookom]

I would create a 5 column report based on just the dates sorted descending. Add a subreport of the tests and results in the detail section of the main report and join on the date field.

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]

 

[Klo]

Is there an example of that I could look at?

 

[dhookom]

I just created a sample with your data doubled and doubled again as another patient. You can download your sample at

http://www.access.hookom.net/samples/labs.zip.

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]

 

[Klo]

Thanks so much Duane! I'm not sure I understand how you did it. I have never used a subreport before. I will do some reading today on the subject. A couple more quick questions if you have the time ...
Is it possible to have the name of the test only appear in the first column? Also is it possible to have the results a particular color based on whether the result is above or below a certain value? ie: red for high, black for in range, and maybe blue if low?
Thanks again for your time.

 

[dhookom]

I'm not sure how you would set all the names in the first column when you don't run the same tests each date. If you want to display every possible test for each date, then you will need to use a cartesian query of Patients, Dates, and Tests. Then join this to your lab results table with a join that includes all records from the cartesian query.

You can format the results if you have a table that identifies the value to compare against.

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]

 

[Klo]

I think the cartesian query might work for what I want.
Now if I create a table for say Calcium with column headings of High,and Low and a row of 10.5 and 8.9. I want any value for Calcium >= 10.5 to show up as red and anything <= 8.9 to show up as blue. Is this set on the report?

 

[dhookom]

I placed a new file for you (and possibly others) at

http://www.access.hookom.net/samples/labs.zip.

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]

 

[Klo]

Beautiful! I think I am begining to understand how you are using the subreports. One last thing ... the colors are based on ranges not a single value. Could tbltargets have a high and low or maybe a high,normal,low?

 

[Klo]

Also where are the color properties set? I can't seem to find them ... I think I understand less than I understood.

 

[dhookom]

You can create another field in the table of all tests. The formatting is accomplished with conditional formatting of the text box. This is in the deepest subreport.

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]

 

[Klo]

Thanks again Duane. I think I'm getting the hang of it. I couldn't have done it without your help. Cheers