DCount

[Ray67]

Good Afternoon,

I need some help with an Access report. I am using Access 2003 with a database . I am also pretty new to coding and have no idea what I’m doing there, so I’m trying to keep it simple if possible. I am trying to create a report that counts the number of ICD9 by multiple criteria. The user would enter multiple criteria and than get a count off ICD9 codes.

This is how the table looks like:

Provider	ICD-9 (1)	ICD-9 (2)	ICD-9 (3)	ICD-9 (4)
Doe1	719.40	719.46	719.40	719.40
Doe1	719.46	719.46	719.46	719.46
Doe2	710.10	719.60	719.40
Doe2	710.10	710.10

The user would enter the ICD9 Codes 719.4 and 719.46. The report would look like this.

Provider	719.4	719.46	Grand Total
Doe1	3	5	8
Doe2	1		1
Grand Total	4	5	9

Thank you

[JoeM]

Part of the problem that is making it harder than it has to be is that your table is not normalized. You should not have mutliple or repeating ICD-9 values in each record (going across). You should have a separate record for each, i.e.

Provider    ICD-9 Number     ICD-9 Value
Doe1               1            719.40
Doe1               2            719.40
Doe1               3            719.40
Doe1               4            719.40
Doe1               1            719.46
...

See here for rules of database normalization: http://www.deeptraining.com/litwin/d...aseDesign.aspx

Thenm you can use an Aggregate Query to count and group all your data. Then you may be able to use a Cross-Tab Query to get it in the format you desire.

[Ray67]

Joem

Thank you very much. I will try what you suggested.

Thank you