Getting Number of Unique Records

[srusskinyon]

I need some help getting unique records from our database! I work for
a small non-profit homeless shelter. We keep track of guest
information as well as what services we have offered for statistical
purposes.

I've been using

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() with the overall numbers, but this of
course doesn't work with unique records.
I've been toying with Allen Browne's ECount(), which adds another
criteria to DCount() to identify distinct records.

This nearly does what I need it to do, EXCEPT that I can't get it to
let me filter for the ServiceDate without manually entering the dates
in my expression. I want to be able to use a form to enter the date
range and run the query based on that date range.

For instance, in a totals query, I have My ECount expression, and
ServicesType (Group By) as columns.

If I type the date in my Ecount expression manually, such as:
Ecount("ServicesLink","Services","ServiceDate Between #07/01/2007# and
#07/31/2007# and ServiceType=""" &ServiceType&"""","True")
I get the data as the correct unique counts in the first column,
grouped by ServicesType in the second. This is basically what I need.

Ecount Expression w/
manually entered
date range
(Correct) ServicesType
14 Program Dorm
2 Food Basket
7 Temporary ID
84 Personal Needs
99 Men's Dorm

Entering this into a field in a report where I specify the data AND
the service type manually works as well.

HOWEVER, 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 tried 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. I tried to enter the
code in my query expression as:

Ecount("ServicesLink","Services","ServiceDate "Between #" & [Form]!
[Statistics Information]![txtStartDate] & "# and #" & [Form]!
[Statistics Information]![txtEndDate] & "#" and ServiceType="""
&ServiceType&"""","True")

I still get the ServiceType in my second column, but in my expression
column, instead of distinct numbers for each of the different types, I
get the same number for all of the rows, which I am assuming is the
distinct number of records for ALL the service types combined.

Ecount Expression w/
Form parameters
date range
(Incorrect) ServicesType
489 Program Dorm
489 Food Basket
489 PN Family Pack
489 Temporary ID
489 Personal Needs
489 Men's Dorm

I've tried doing another query that filters records by a date range
and then trying to do the Ecount (without the date criteria) off that
query, but then I keep getting "Expected 2 Parameters" as an error
message.

At a loss...

[Neil]

I haven't used Allen Brown's function, so I don't know. But I can tell you
that your quote marks are off in what you posted. Maybe that was a typo when
you posted. Here's what you posted:

Ecount("ServicesLink","Services","ServiceDate "Between #" & [Form]!
[Statistics Information]![txtStartDate] & "# and #" & [Form]!
[Statistics Information]![txtEndDate] & "#" and ServiceType="""
&ServiceType&"""","True")

That should be:

Ecount("ServicesLink","Services","ServiceDate Between #" & [Form]!
[Statistics Information]![txtStartDate] & "# and #" & [Form]!
[Statistics Information]![txtEndDate] & "# and ServiceType="""
&ServiceType&"""","True")

Notice that two quote marks were removed.

<sr*********@gmail.comwrote in message
news:11**********************@o3g2000hsb.googlegro ups.com...

>I need some help getting unique records from our database! I work for
a small non-profit homeless shelter. We keep track of guest
information as well as what services we have offered for statistical
purposes.

I've been using

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() with the overall numbers, but this of
course doesn't work with unique records.
I've been toying with Allen Browne's ECount(), which adds another
criteria to DCount() to identify distinct records.

This nearly does what I need it to do, EXCEPT that I can't get it to
let me filter for the ServiceDate without manually entering the dates
in my expression. I want to be able to use a form to enter the date
range and run the query based on that date range.

For instance, in a totals query, I have My ECount expression, and
ServicesType (Group By) as columns.

If I type the date in my Ecount expression manually, such as:
Ecount("ServicesLink","Services","ServiceDate Between #07/01/2007# and
#07/31/2007# and ServiceType=""" &ServiceType&"""","True")
I get the data as the correct unique counts in the first column,
grouped by ServicesType in the second. This is basically what I need.

Ecount Expression w/
manually entered
date range
(Correct) ServicesType
14 Program Dorm
2 Food Basket
7 Temporary ID
84 Personal Needs
99 Men's Dorm

Entering this into a field in a report where I specify the data AND
the service type manually works as well.

HOWEVER, 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 tried 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. I tried to enter the
code in my query expression as:

Ecount("ServicesLink","Services","ServiceDate "Between #" & [Form]!
[Statistics Information]![txtStartDate] & "# and #" & [Form]!
[Statistics Information]![txtEndDate] & "#" and ServiceType="""
&ServiceType&"""","True")

I still get the ServiceType in my second column, but in my expression
column, instead of distinct numbers for each of the different types, I
get the same number for all of the rows, which I am assuming is the
distinct number of records for ALL the service types combined.

Ecount Expression w/
Form parameters
date range
(Incorrect) ServicesType
489 Program Dorm
489 Food Basket
489 PN Family Pack
489 Temporary ID
489 Personal Needs
489 Men's Dorm

I've tried doing another query that filters records by a date range
and then trying to do the Ecount (without the date criteria) off that
query, but then I keep getting "Expected 2 Parameters" as an error
message.

At a loss...

[Carlos Nunes-Ueno]

sr*********@gmail.com wrote:

I need some help getting unique records from our database! I work for
a small non-profit homeless shelter. We keep track of guest
information as well as what services we have offered for statistical
purposes.

I've been using

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() with the overall numbers, but this of
course doesn't work with unique records.
I've been toying with Allen Browne's ECount(), which adds another
criteria to DCount() to identify distinct records.

This nearly does what I need it to do, EXCEPT that I can't get it to
let me filter for the ServiceDate without manually entering the dates
in my expression. I want to be able to use a form to enter the date
range and run the query based on that date range.

For instance, in a totals query, I have My ECount expression, and
ServicesType (Group By) as columns.

If I type the date in my Ecount expression manually, such as:
Ecount("ServicesLink","Services","ServiceDate Between #07/01/2007# and
#07/31/2007# and ServiceType=""" &ServiceType&"""","True")
I get the data as the correct unique counts in the first column,
grouped by ServicesType in the second. This is basically what I need.

Ecount Expression w/
manually entered
date range
(Correct) ServicesType
14 Program Dorm
2 Food Basket
7 Temporary ID
84 Personal Needs
99 Men's Dorm

Entering this into a field in a report where I specify the data AND
the service type manually works as well.

HOWEVER, 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 tried 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. I tried to enter the
code in my query expression as:

Ecount("ServicesLink","Services","ServiceDate "Between #" & [Form]!
[Statistics Information]![txtStartDate] & "# and #" & [Form]!
[Statistics Information]![txtEndDate] & "#" and ServiceType="""
&ServiceType&"""","True")

I still get the ServiceType in my second column, but in my expression
column, instead of distinct numbers for each of the different types, I
get the same number for all of the rows, which I am assuming is the
distinct number of records for ALL the service types combined.

Ecount Expression w/
Form parameters
date range
(Incorrect) ServicesType
489 Program Dorm
489 Food Basket
489 PN Family Pack
489 Temporary ID
489 Personal Needs
489 Men's Dorm

I've tried doing another query that filters records by a date range
and then trying to do the Ecount (without the date criteria) off that
query, but then I keep getting "Expected 2 Parameters" as an error
message.

At a loss...

Perhaps an easier way to approach this is to break it into two queries.

The first query would be a totals query something like this (parentheses
indicate the total type):

ServicesLink (Group By)
ServiceType (Group By)
ServiceDate (Where) with Criteria something like Between
[Form]![Statistics Information]![txtStartDate] and [Form]![Statistics
Information]![txtEndDate]

This query would yield all the distinct combinations of people and
services in the time period entered on your form.

The second query would work off the first query and would be a totals
query something like this:

ServicesLink (Count)
ServiceType (Group By)

This query would do the totaling.

Hope this helps,

Carlos

--
Posted via a free Usenet account from http://www.teranews.com