Re: Query to Count Duplicate Values in a Given Date Range

Now, I am running into another issue. There is also a field called "No
Show" to note whether the client showed up at the pantry (Y/N field value).
Now, if I try to run the query with that field, it returns no results. The
query should only include records that return a "No" value.

Thanks again!

"Karl Burrows" <karl@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:eizROnjmJHA.824@xxxxxxxxxxxxxxxxxxxxxxx
That seems to work! Using WHERE and moving the date range parameter to the
end made the difference. I couldn't get it to get past running the date
range query first which ended up making every record unique since the date
would force duplicates apart.

Here's my final SQL code if you see anything else that may need adjusting.
I had to tell it to return all results since I have compare single visits to
multiple visits. I can separate those in the report.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT Client1.SS, Min(Client1.SS) AS min_SS, Max(Client1.SS) AS max_SS,
Count(Client1.[SS]) AS rec_count
FROM Client1
WHERE ((Not (Client1.Date) Is Null And (Client1.Date) Between [Start Date]
And [End Date]))
GROUP BY Client1.SS
ORDER BY Client1.SS;

Thanks!

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:uuQtihXmJHA.1172@xxxxxxxxxxxxxxxxxxxxxxx
It should work if you:
- include special handling for the field that is a reserved word,
- use a WHERE clause rather than HAVING for the dates,
- declare the parameters (with the right data type)
- count a specific field.

1. Date is a reserved word. In query design, make sure you have square
brackets around this name, i.e.:
[Date]
This should prevent the issue for the immediate case, but it will still
cause you grief in other contexts. For a list of the reserved words to avoid
when designing tables, see:
http://allenbrowne.com/AppIssueBadWord.html

2. In the Total row under this field, choose:
Where
The SQL view will now have the dates in the WHERE clause, and the count in
the HAVING clause. This will solve the timing issue (i.e. the HAVING clause
is executed after aggregation, which is too late as the date fields are not
present.)

3. Click Parameters on the Query menu.
Access opens a dialog.
Enter 2 rows, like this:
[Start Date] Date/Time
[End Date] Date/Time
The SQL view will now have a PARAMETERS clause at the top. This will solve
the issue of JET misinterpreting the data type and performing the wrong kind
of matching (text rather than date.)

4. Instead of:
rec_count: Count(*)
count the primary key field, e.g.:
rec_count: Count([ID])
JET's a bit odd about this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Karl Burrows" <karl@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OcRcOpWmJHA.4140@xxxxxxxxxxxxxxxxxxxxxxx
Using Access 2003 & 2007. I am working with a local food pantry and they
have changed their policy for time period between client referrals. They
want to track the effect of this change on overall food donations. I can
pull the number of times a client ID occurs, but whenever I give it a date
range, the values then go away.

Basically, they need to count total referrals and number of duplicate
referrals for a given period of time. Not a lot of data needed, just a
total, but I am having a bear of a time trying to get this to work.

Here is my SQL:

SELECT Client1.Date, Client1.SS, Min(Client1.SS) AS min_SS,
Max(Client1.SS)
AS max_SS, Count(*) AS rec_count
FROM Client1
GROUP BY Client1.Date, Client1.SS
HAVING ((Not (Client1.Date) Is Null And (Client1.Date) Between [Start
Date]
And [End Date]) AND ((Count(*))>1))
ORDER BY Client1.SS;

Can anyone offer any suggestions? Thanks!



.

Relevant Pages

  • Composite index and data distribution
    ... SQL> create table foobar ... COMPOSITE INDEX (BEST I CAN THINK OF FOR THE QUERY TO BE EXECUTED): ... Execution Plan ... 395 bytes received via SQL*Net from client ...
    (comp.databases.oracle.server)
  • Re: export form filtered data to excel
    ... ' There's no ORDER BY in the SQL. ... ' Remove the semi-colon from the end, then append the WHERE clause ... Or, better yet, look at the actual SQL for your query and see what's ... Dim qdfTemp As DAO.QueryDef ...
    (microsoft.public.access.formscoding)
  • Re: Error: Specified field could refer to more than one table
    ... WeeklyMileage) in SELECT Clause my guess is that the reference exists ... Run the query and select Remove Filter/sort from the menu and then save the ... Copy the SQL text into a blank query and try switching into design view ... FROM clause of your SQL statement. ...
    (microsoft.public.access.queries)
  • Re: Error: Specified field could refer to more than one table
    ... WeeklyMileage) in SELECT Clause my guess is that the reference exists ... Run the query and select Remove Filter/sort from the menu and then save the ... Copy the SQL text into a blank query and try switching into design view ... FROM clause of your SQL statement. ...
    (microsoft.public.access.queries)
  • Re: export form filtered data to excel
    ... ' There's no ORDER BY in the SQL. ... ' Remove the semi-colon from the end, then append the WHERE clause ... Or, better yet, look at the actual SQL for your query and see what's ...
    (microsoft.public.access.formscoding)
Loading