Query to Count Duplicate Values in a Given Date Range

  • Thread starter Thread starter Karl Burrows
  • Start date Start date
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!
 
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.
 
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...
 
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...
 
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...
 
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...
 
Back
Top