Query to Count Duplicate Values in a Given Date Range

K

Karl Burrows

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!
 
A

Allen Browne

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.
 
K

Karl Burrows

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!

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

Reply to group, rather than allenbrowne at mvps dot org.

Karl Burrows said:
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!
Click to expand...
 
K

Karl Burrows

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!

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!

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

Reply to group, rather than allenbrowne at mvps dot org.

Karl Burrows said:
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!
Click to expand...
 
A

Allen Browne

Under the Yes/no field, use Where in the Total row, and False (without
quotes) in the Criteria row.

Post the SQL statement if that doesn't solve it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Karl Burrows said:
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!

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!

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.

Karl Burrows said:
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;
Click to expand...
Click to expand...
 
K

Karl Burrows

Yes, figured it out finally! Thanks for the help!

Under the Yes/no field, use Where in the Total row, and False (without
quotes) in the Criteria row.

Post the SQL statement if that doesn't solve it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Karl Burrows said:
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!

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!

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.

Karl Burrows said:
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;
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dcount between Date Range 2
Summing days that match a criteria and date 3
Query to count time range 4
Crosstab query by date range 7
DCount-ing from a table - unduplicated 2
Count text values in a Access Field 0
Need Help with Totals Query 24
Using Date Fields in a Table as Criteria for a Count Query 1

Top