Data mining question 1

[timsayles1]

I am working with an access data base for measurements. It has multiple patients on the same day, multiple measurements also. I need to build a report where it will take each patient's multiple tests for each day and average them. There are sometimes 2-4 tests for each patient and there can be more than one patient each day. Data is logged by patient ID number test date and time and the test data. Any idea how to do that? Thanks in advance.

 

[Remou]

It is possible to use the report wizard to do this, I think. If you choose Date and Patient for the Group By option, you will then be able to choose Average as the Summary Option for each of the tests you wish to include. Choosing Summary Report Only will give the averages by patient by day, without the detail records.

 

[timsayles1]

I think this will work, except that on my date field, it has the date and time next to it. How can I get rid of the times??

 

[Remou]

You can either use a query to format the date:
[tt]Format(TestDate,"dd/mm/yy")[/tt]
Or you can choose Grouping Options when you select the date field as a Group By. This will allow you to choose an interval to group by. Day is one of the options.

 

[dhookom]

To get rid of the time portion of a datetime, use:
=DateValue([TestDateTimeField])
If you format your field you may lose some sorting and other functionality.

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]

 

[timsayles1]

Great that works like a champ.

now to chart the patient data by date! wow. This is so much fun!