How do I count total # of records between dates?

[Joe]

I have a medical database containing (amongst others) a history of injury
field (text), body region and attending dates. I have the following query
that returns records based on user criteria...

SELECT [Patient Treatments Table].history, [Patient Treatments
Table].body_part, [Patient Treatments Table].last_attendance
FROM [Patient Treatments Table]
WHERE ((([Patient Treatments Table].history) Like "*" & [Search For
Mechanism:] & "*") AND (([Patient Treatments Table].body_part) Like "*" &
[Body Part:] & "*") AND (([Patient Treatments Table].last_attendance) Between
[Enter Start Date:] And [Enter End Date:]));

In attempting to use COUNT fx I get a single count for each unique record
but not totalled as I would like. Also I have not figured out how to get a
total of the whole record pool contained between the date criteria. I would
really like to be able to say that between certain dates x number of clients
presented for care following a 'fall' which represented y% of the total
presentations for the month.

Please could someone point me in the right direction?

 

[Duane Hookom]

Try something like:

SELECT Sum(Abs( [some field]="fall")) as NumFalls,
Count(*) as NumTotal,
Sum(Abs( [some field]="fall"))/Count(*) as PctFalls
FROM [Patient Treatments Table]
WHERE [Patient Treatments Table].history
Like "*" & [Search For Mechanism:] & "*" AND
[Patient Treatments Table].body_part
Like "*" & [Body Part:] & "*" AND
[Patient Treatments Table].last_attendance
Between [Enter Start Date:] And [Enter End Date:];

Remove all fields from the SELECT that you don't want summed, grouped by, or
counted.

 

[Joe]

Thanks Duane for your time and expertise. I get the total count for returned
records within the 'fall' data set using the SQL provided - in this instance
39 records for the period requested. However in doing a quick query for all
attendance records in the same period I get a return of 1385 records. Is
there a way I can access this total within the same query so that the 'fall'
data can be expressed as a percentage of the total attendances.

Thanks,
Joe

Try something like:

SELECT Sum(Abs( [some field]="fall")) as NumFalls,
Count(*) as NumTotal,
Sum(Abs( [some field]="fall"))/Count(*) as PctFalls
FROM [Patient Treatments Table]
WHERE [Patient Treatments Table].history
Like "*" & [Search For Mechanism:] & "*" AND
[Patient Treatments Table].body_part
Like "*" & [Body Part:] & "*" AND
[Patient Treatments Table].last_attendance
Between [Enter Start Date:] And [Enter End Date:];

Remove all fields from the SELECT that you don't want summed, grouped by, or
counted.

--
Duane Hookom
MS Access MVP

I have a medical database containing (amongst others) a history of injury
field (text), body region and attending dates. I have the following query
that returns records based on user criteria...

SELECT [Patient Treatments Table].history, [Patient Treatments
Table].body_part, [Patient Treatments Table].last_attendance
FROM [Patient Treatments Table]
WHERE ((([Patient Treatments Table].history) Like "*" & [Search For
Mechanism:] & "*") AND (([Patient Treatments Table].body_part) Like "*" &
[Body Part:] & "*") AND (([Patient Treatments Table].last_attendance)
Between
[Enter Start Date:] And [Enter End Date:]));

In attempting to use COUNT fx I get a single count for each unique record
but not totalled as I would like. Also I have not figured out how to get
a
total of the whole record pool contained between the date criteria. I
would
really like to be able to say that between certain dates x number of
clients
presented for care following a 'fall' which represented y% of the total
presentations for the month.

Please could someone point me in the right direction?

 

[Duane Hookom]

Sure remove anything from your query criteria that limits the query to fall
data only.

--
Duane Hookom
MS Access MVP

Thanks Duane for your time and expertise. I get the total count for
returned
records within the 'fall' data set using the SQL provided - in this
instance
39 records for the period requested. However in doing a quick query for
all
attendance records in the same period I get a return of 1385 records. Is
there a way I can access this total within the same query so that the
'fall'
data can be expressed as a percentage of the total attendances.

Thanks,
Joe

Try something like:

SELECT Sum(Abs( [some field]="fall")) as NumFalls,
Count(*) as NumTotal,
Sum(Abs( [some field]="fall"))/Count(*) as PctFalls
FROM [Patient Treatments Table]
WHERE [Patient Treatments Table].history
Like "*" & [Search For Mechanism:] & "*" AND
[Patient Treatments Table].body_part
Like "*" & [Body Part:] & "*" AND
[Patient Treatments Table].last_attendance
Between [Enter Start Date:] And [Enter End Date:];

Remove all fields from the SELECT that you don't want summed, grouped by,
or
counted.

--
Duane Hookom
MS Access MVP

I have a medical database containing (amongst others) a history of
injury
field (text), body region and attending dates. I have the following
query
that returns records based on user criteria...

SELECT [Patient Treatments Table].history, [Patient Treatments
Table].body_part, [Patient Treatments Table].last_attendance
FROM [Patient Treatments Table]
WHERE ((([Patient Treatments Table].history) Like "*" & [Search For
Mechanism:] & "*") AND (([Patient Treatments Table].body_part) Like "*"
&
[Body Part:] & "*") AND (([Patient Treatments Table].last_attendance)
Between
[Enter Start Date:] And [Enter End Date:]));

In attempting to use COUNT fx I get a single count for each unique
record
but not totalled as I would like. Also I have not figured out how to
get
a
total of the whole record pool contained between the date criteria. I
would
really like to be able to say that between certain dates x number of
clients
presented for care following a 'fall' which represented y% of the total
presentations for the month.

Please could someone point me in the right direction?