473,554 Members | 3,299 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Getting Number of Unique Records

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("Service sLink","Service s","ServiceD ate 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("Service sLink","Service s","ServiceD ate "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...

Nov 2 '07 #1
2 4439
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("Service sLink","Service s","ServiceD ate "Between #" & [Form]!
[Statistics Information]![txtStartDate] & "# and #" & [Form]!
[Statistics Information]![txtEndDate] & "#" and ServiceType="""
&ServiceType&"" "","True")

That should be:

Ecount("Service sLink","Service s","ServiceD ate Between #" & [Form]!
[Statistics Information]![txtStartDate] & "# and #" & [Form]!
[Statistics Information]![txtEndDate] & "# and ServiceType="""
&ServiceType&"" "","True")

Notice that two quote marks were removed.

<sr*********@gm ail.comwrote in message
news:11******** **************@ o3g2000hsb.goog legroups.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("Service sLink","Service s","ServiceD ate 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("Service sLink","Service s","ServiceD ate "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...

Nov 3 '07 #2
sr*********@gma il.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("Service sLink","Service s","ServiceD ate 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("Service sLink","Service s","ServiceD ate "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

Nov 6 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2631
by: Bryan Zash | last post by:
When querying a bit field, I am encountering a problem with MS SQL Server returning a larger number of records for a table than the actual number of records that exist within that table. For example, my customer table has 1 million unique records, so the results of the following query are as such: select count(customer_nbr) from customer...
3
14116
by: John Ortt | last post by:
> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record (and store it in field 2). > > I would like to run a query that returns all the data in the table plus the > record number, in a similar sense to the...
16
20457
by: John Baker | last post by:
Hi: I know this is a strange question, but I have inherited a system where files are copied and records re auto numbered (as an index field) )frequently, and I am wondering how high the number can go without the system crashing. An ancillary question is how one resets an auto number so that the sequence starts again at 1. In the case...
1
1636
by: Crimsonwingz | last post by:
I am setting up a database to track incoming shipments by Lot. In one shipment (lot) there will be several items that need to be associated to that lot, but need a unique number to reship by. My goal is to use Lot number - record count, but do not understand how to get a record set from within the subform. Essentially, lot 1000 has 10...
1
5405
by: Scott269 | last post by:
So I've got an old MS Works database I imported into Access. I needed a primary key so I created a record number field that was just the record number I manually inserted when I entered it in the database so I could sort the records by the date at which they were entered. Well now I've deleted some of those records so its of course causing...
11
2232
by: Siv | last post by:
Hi, I seem to be having a problem with a DataAdapter against an Access database. My app deletes 3 records runs a da.update(dt) where dt is a data.Datatable. I then proceed to update a list to reflect that the 3 items have been deleted only to discover that the 3 items appear, however when I click on them to display their information which...
4
5974
by: Sean Shanny | last post by:
To all, Running into an out of memory error on our data warehouse server. This occurs only with our data from the 'September' section of a large fact table. The exact same query running over data from August or any prior month for that matter works fine which is why this is so weird. Note that June 2004 through today is stored in the same...
12
1734
by: Lennart Anderson | last post by:
I'm having a MySQl table wih a lot of information. I want to present some main fields in a table on one page. Each record do, of course, have a unique ID. The presnted table will have one field as a linked field. I want to be able to click this link, retreive the ID information for that record and then present detailed data for that record on...
12
5562
by: magmike | last post by:
Accidentally deleted a record. Anyway to get it back? If not, I know the ID number - which is an autonumber field. Because of the related data from other tables, would I be able to create a new record and make its ID number, that of the deleted record? Thanks! magmike
0
7519
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8039
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7887
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6140
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5431
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5152
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3556
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2015
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1130
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.