Help with counting distinct records!

[RussCRM]

I work at a homeless shelter and we're putting together an Access
database to automate our keeping track of guests, services and
statistics.

Here's the situation:

I have two main tables:

Guest (stores data such as GuestID, First Name, Last Name, etc.) and

Services (stores data such as the type of service the guest used
(Shelter Bed, Lunch, Dinner, Hygiene Items, etc.), the date the
service was used, and an "alias" connector to the GuestID called
ServicesLink.)


Each month, we track how many Shelter beds we used overall and also
how many UNIQUE guests (ServicesLink) we offered beds to.

I've had success using DCount() to get the "total number of beds
filled" numbers, but this of course doesn't work with unique records.

I need to do reports with these unique totals every month and I want
it to be user-friendly to anyone else who needs to pull these stats.
So, I'd like to be able to print a report or run a query, filtered by
date, that includes these fields sorted by a form or field that allows
a user to enter a date range that the query will use to give me the
unique numbers for the date range specified.

I'd like to do this with a form "Statistics Information" where the
user can enter a date range with [txtStartDate] and [txtEndDate] and
then click a command button to run the query/report.

 

[Guest]

And what is your specific question? We cant write it for you.

 

[RussCRM]

I'm sorry! My question is how to do a query where I can identify the
unique number of guests we provided a certain service to, filtered by
date, if I have GuestID, ServiceDate and Service type as my columns.
So, essentially, I guess I want to group by date, then servicetype,
and then get a unique count of guests that have that service type.

And what is your specific question? We cant write it for you.

I work at a homeless shelter and we're putting together an Access
database to automate our keeping track of guests, services and
statistics.

Here's the situation:

I have two main tables:

Guest (stores data such as GuestID, First Name, Last Name, etc.) and

Services (stores data such as the type of service the guest used
(Shelter Bed, Lunch, Dinner, Hygiene Items, etc.), the date the
service was used, and an "alias" connector to the GuestID called
ServicesLink.)

Each month, we track how many Shelter beds we used overall and also
how many UNIQUE guests (ServicesLink) we offered beds to.

I've had success using DCount() to get the "total number of beds
filled" numbers, but this of course doesn't work with unique records.

I need to do reports with these unique totals every month and I want
it to be user-friendly to anyone else who needs to pull these stats.
So, I'd like to be able to print a report or run a query, filtered by
date, that includes these fields sorted by a form or field that allows
a user to enter a date range that the query will use to give me the
unique numbers for the date range specified.

I'd like to do this with a form "Statistics Information" where the
user can enter a date range with [txtStartDate] and [txtEndDate] and
then click a command button to run the query/report.

 

[John Spencer]

Probably something like

Query One - Get unique guests and Services for a period
SELECT DISTINCT GuestID, [Service Type]
FROM YourTable
WHERE ServiceDate Between Cdate([Enter Start Date]) and CDate([Enter End
Date])

Query Two
SELECT ServiceType, Count(GuestID)
FROM SavedQueryOne
GROUP BY ServiceType

In Access SQL, you have to use a query to return the unique (DISTINCT)
values and then do the count.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm sorry! My question is how to do a query where I can identify the
unique number of guests we provided a certain service to, filtered by
date, if I have GuestID, ServiceDate and Service type as my columns.
So, essentially, I guess I want to group by date, then servicetype,
and then get a unique count of guests that have that service type.

And what is your specific question? We cant write it for you.

I work at a homeless shelter and we're putting together an Access
database to automate our keeping track of guests, services and
statistics.

Here's the situation:

I have two main tables:

Guest (stores data such as GuestID, First Name, Last Name, etc.) and

Services (stores data such as the type of service the guest used
(Shelter Bed, Lunch, Dinner, Hygiene Items, etc.), the date the
service was used, and an "alias" connector to the GuestID called
ServicesLink.)

Each month, we track how many Shelter beds we used overall and also
how many UNIQUE guests (ServicesLink) we offered beds to.

I've had success using DCount() to get the "total number of beds
filled" numbers, but this of course doesn't work with unique records.

I need to do reports with these unique totals every month and I want
it to be user-friendly to anyone else who needs to pull these stats.
So, I'd like to be able to print a report or run a query, filtered by
date, that includes these fields sorted by a form or field that allows
a user to enter a date range that the query will use to give me the
unique numbers for the date range specified.

I'd like to do this with a form "Statistics Information" where the
user can enter a date range with [txtStartDate] and [txtEndDate] and
then click a command button to run the query/report.