urgent help please.

[Guest]

hi everyone
i am using db to enter patients info and expenses.now my boss asked me to
create a report based on ages like this( for diagnosis and ages)

Diagnosis <1yr 1to9yr 10to19yr 20to29yr 30to39 40to49 50to59

60

and last column is total cost ( cost calculation is not required of each
colum only total cost is req.)

i want to calculate number of patients for each category of age. i have one
field "number of patients" as sometimes one record is for more than one
patient.

is this possible in access qry or crosstab qry.(there is one field Dob)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes use a crosstab query like this:

TRANSFORM Count(*) As theCount
SELECT Diagnosis, Sum(costs) As TotalCosts
FROM table_name
WHERE <criteria>
GROUP BY Diagnosis
PIVOT
Switch(
Year(Date())
Year(dob)+(DateSerial(Year(Date()),Month(dob),Day(dob))>date())
<1,"<1yr",
Year(Date())
Year(dob)+(DateSerial(Year(Date()),Month(dob),Day(dob))>date())
Between 1 and 9, "1to9yrs",
Year(Date())
Year(dob)+(DateSerial(Year(Date()),Month(dob),Day(dob))>date())
Between 10 and 19, "10to19yr",
Year(Date())
Year(dob)+(DateSerial(Year(Date()),Month(dob),Day(dob))>date())
Between 20 and 29, "20to29yr",
Year(Date())
Year(dob)+(DateSerial(Year(Date()),Month(dob),Day(dob))>date())
Between 30 and 39, "30to39yr",
Year(Date())
Year(dob)+(DateSerial(Year(Date()),Month(dob),Day(dob))>date())
Between 40 and 49, "40to49yr",
Year(Date())
Year(dob)+(DateSerial(Year(Date()),Month(dob),Day(dob))>date())
Between 50 and 59, "50to59yr",
True, ">60yr"
)

The last line 'True, ">60yr"' means any other value that doesn't fit the
preceeding criteria will be deemed to be > 60 yrs.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRASzqYechKqOuFEgEQKasgCeOSJwELh9I+TFmptBP9pvZgYYN+YAn349
GEz1tbdODlNUUlOaJBzaMufw
=r3n4
-----END PGP SIGNATURE-----