472,372 Members | 2,302 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,372 software developers and data experts.

Help with counting distinct 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(“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…
Nov 2 '07 #1
1 3595
14,534 Expert Mod 8TB
I think you are heading down an unnecessary road with this. You can resolve it in the query as far as I can see.

Expand|Select|Wrap|Line Numbers
  1. SELECT ServicesLink, ServiceType, Count(ServicesLink) As NumUsed
  2. FROM Services
  3. WHERE ServiceDate >=[Form]![Statistics Information]![txtStartDate] 
  4. AND ServiceDate <=[Form]![Statistics Information]![txtEndDate]
  5. GROUP BY ServicesLink, ServiceType;
Does this not give you the information you need?
Nov 5 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Ralph Freshour | last post by:
I have a question about the following PHP script - I got it off a web site tutorial on how to count users logged into your site - my question is the $PHP_SELF variable - it writes the name of the...
by: dixie | last post by:
I have a report which has records of students in various courses. It would normally show say 400 entries for 80 students doing an average of 5 courses each. I want to say on the report how many...
by: Mr. x | last post by:
Hello, I need a script for counting the no. of enters on my site, please. I need a check that for the same user in the same day - if the user enter several times, it is count once (or something...
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
by: hahahardididi | last post by:
Hi Forums, I have a frustrating problem on my Stored Procedure. It can only proccess about 100 records in 10 minutes. I have 2 million initial records that need to processed. Meaning that with...
by: Ladysniper | last post by:
DESPERATE doesn't begin to describe it. Can someone PLEASE tell me what is WRONG with this code? Now..a bit of background. It is a search results page for http://www.azsoma.info/directory.htm....
by: mscurto | last post by:
What is the syntax for an sql command to get the following. I want to pull in a handful of fields from a table but one of the fields needs to be unique. For example, if I have a customer table...
by: Webstorm | last post by:
Hi, I hope someone can help me sort this out a bit, Im completely lost. Here is the page I am working on: http://www.knzbusinessbrokers.com/default.asp I have 3 search critera that I need to...
by: troy_lee | last post by:
I am trying to count the total number of units for a given part number that have a Priority rating of 1. Based upon some research, this is what I came up with for my query. Access says that I have...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

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.