Calculating number of items based on dropdown

[arstueck]

I am trying to develop a database for a local clinic that is operating as a not-for-profit. I have a table that has the following fields



AdmID - Autonumber
PatientID - Number
AdmType - Dropdown (based on another table) (Values are ER Visit, Admission, Readmission,... etc)
Date - Date/Time
Note - Memo

The goal is to take the values from the AdmType field, group them, and then perform calculations based on those values. These are to be sorted by two user defined dates.

I had originally had two fields "ParmValue:[Start_Date]" & "ParmValue2:[End_Date]" which would define the two dates needed.

I'm open to Ideas I'm totally at a loss.

Tables:
Patients
Admissions

[burrina]

A Crosstab query should do the trick along with your criteria.

HTH

[arstueck]

Alright I managed to create the crosstab as you suggested and now I have returned the values that I need, however I am unable to access the totals created by the crosstab to perform other calculations.

The simplest of these calculations would be the (# of Admissions/Total patients) when I pull up the query wizard (or design view) I see no fields in the crosstab query.

Thanks

[burrina]

Are you using 2 tables and if so, are they correctly linked? Is there data in the tables?