473,699 Members | 2,397 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 4445
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
2637
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 = 1,000,000 There is bit field in the customer table that denotes whether a
3
14126
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 getuser() command to get the User's > Identity, I would like a GetRecord() command.
16
20506
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 of this file, the auto number field serves no useful purpose except as an
1
1639
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 pieces. Ideally, it would be Lot 1000
1
5416
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 gaps in the records. The record number in Access no longer matches my record number that I...
11
2240
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 runs a datareader over the same database it appears that the data has now gone. I wondered whether...
4
6011
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 f_pageviews table. Nothing has changed on the server in the last couple of months. I upgraded...
12
1757
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 the next page. How do I retreive the ID? Any hints are very much appreciated. Thanks
12
5568
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
8700
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9184
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9048
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8931
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8892
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6539
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4386
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4635
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2359
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.