Ignore Duplicate Entries

[sixdeuce62]

Hello,

I am using Access 2002.

I have read through 20 plus posts on here and I am still having
trouble.

My table consists of the following fields:

Date Client ID

I need to search by a date range to determine the total number of
clients served. Some clients come in more than once a day. So when I
do a count it returns more clients than actually served.

I need to know how many clients were served by date range.

Example

Date Client ID
1/1/07 001
1/1/07 001
1/4/07 002

The return I get for the date range of 1/1/07-1/31/07 is 3, but I only
served 2 clients.

Any help would be greatly appreciated! I am able to use either a
report or query for this information, whichever is easier to create.

Thank-you

[storrboy]

On Mar 10, 2:39 pm, "sixdeuce62" <[e-mail]:

Hello,

I am using Access 2002.

I have read through 20 plus posts on here and I am still having
trouble.

My table consists of the following fields:

Date Client ID

I need to search by a date range to determine the total number of
clients served. Some clients come in more than once a day. So when I
do a count it returns more clients than actually served.

I need to know how many clients were served by date range.

Example

Date Client ID
1/1/07 001
1/1/07 001
1/4/07 002

The return I get for the date range of 1/1/07-1/31/07 is 3, but I only
served 2 clients.

Any help would be greatly appreciated! I am able to use either a
report or query for this information, whichever is easier to create.

Thank-you

It probably depends on how much info you're really after as to how
easy the query will be. If you group on Client ID, set Date criteria
as Between x And Y and use the DISTINCT predicate - you should get 2
records returned. A report could be made based on the query.

P.S. - Date is a really bad name for a database object (field, table
form etc.). There is a function by that name and the DB could get
confused as to which you mean.

SELECT DISTINCT Table1.[Client ID]
FROM Table1
GROUP BY Table1.[Client ID], Table1.[Date]
HAVING (((Table1.[Date]) Between #1/1/2007# And #1/4/2007#));

[sixdeuce62]

Hello,

Thank you for the quick reply. My table is called Program Information

This is what I exactly entered from your suggestion.

SELECT DISTINCT Program Information.[Client ID]
FROM Program Information
GROUP BYProgram Information.[Client ID], Program Information.[Date]
HAVING (((Program Information.[Date]) Between #1/1/2007# And
#1/4/2007#));
This is what I got when I tried to save the query:

Synax Error (missing operator) in query expression 'Program
Information. [Client ID]'.
Would it be easier to do this in a report? I just need a total number
of clients served per month, any way I can get it. (Besides manually
counting them)

I gave a query that gives me the date and client id my date range. It
just gives me every time someone came in, which duplicates clients
id's on the same day. Is there a way to count the user id field in a
report that does not duplicate user id's in the total?

Thanks


On Mar 10, 3:59 pm, "storrboy" <[e-mail]:

On Mar 10, 2:39 pm, "sixdeuce62" <[e-mail]:

Hello,

I am using Access 2002.

I have read through 20 plus posts on here and I am still having
trouble.

My table consists of the following fields:

Date Client ID

I need to search by a date range to determine the total number of
clients served. Some clients come in more than once a day. So when I
do a count it returns more clients than actually served.

I need to know how many clients were served by date range.

Example

Date Client ID
1/1/07 001
1/1/07 001
1/4/07 002

The return I get for the date range of 1/1/07-1/31/07 is 3, but I only
served 2 clients.

Any help would be greatly appreciated! I am able to use either a
report or query for this information, whichever is easier to create.

Thank-you

It probably depends on how much info you're really after as to how
easy the query will be. If you group on Client ID, set Date criteria
as Between x And Y and use the DISTINCT predicate - you should get 2
records returned. A report could be made based on the query.

P.S. - Date is a really bad name for a database object (field, table
form etc.). There is a function by that name and the DB could get
confused as to which you mean.

SELECT DISTINCT Table1.[Client ID]
FROM Table1
GROUP BY Table1.[Client ID], Table1.[Date]
HAVING (((Table1.[Date]) Between #1/1/2007# And #1/4/2007#));- Hide quoted text -

- Show quoted text -

[storrboy]

A report needs a query to display what you want, so either way you
need to make one or know how to get the report do what you want.
I see a few things wrong with your attempt.

1) Fields with spaces, non-alphanumeric characters or potentially
named like a system reserved word require [ ] around them
2) There is no space between GROUP BY and Program Information

Try this revised query. You may need to play with spacing between
words if you are cutting and pasting. Trailing or starting spaces may
get removed.

SELECT DISTINCT [Program Information].[Client ID]
FROM [Program Information]
GROUP BY [Program Information].[Client ID], [Program Information].
[Date]
HAVING ((([Program Information].[Date]) Between #1/1/2007# And
#1/4/2007#));

[sixdeuce62]

Hello,

Thanks again. Well we are getting closer. Now the query returns the
unique client id's. I do have one issue though. When I run the query I
get a box that pops up.

The box says:

Enter Parameter Value
Program.Information.Date
I have to enter the last date of the month to get the query to finish
running.

I would like to have an Enter Parameter Value box come up and prompt
me for Beginning Date and Ending Date.

Thanks for all the help so far, you're a lifesaver!
This is what I entered from your recommendations:

SELECT DISTINCT [Program Information].[Client ID]
FROM [Program Information]
GROUP BY [Program Information].[Client ID], [Program Information].Date
HAVING ((([Program Information].Date) Between #3/1/2007# And
#3/31/2007#));
The records I am using are actually from this month, 3/2007.

On Mar 10, 6:50 pm, "storrboy" <[e-mail]:

A report needs a query to display what you want, so either way you
need to make one or know how to get the report do what you want.
I see a few things wrong with your attempt.

1) Fields with spaces, non-alphanumeric characters or potentially
named like a system reserved word require [ ] around them
2) There is no space between GROUP BY and Program Information

Try this revised query. You may need to play with spacing between
words if you are cutting and pasting. Trailing or starting spaces may
get removed.

SELECT DISTINCT [Program Information].[Client ID]
FROM [Program Information]
GROUP BY [Program Information].[Client ID], [Program Information].
[Date]
HAVING ((([Program Information].[Date]) Between #1/1/2007# And
#1/4/2007#));

[storrboy]

Remember this snippet?

>P.S. - Date is a really bad name for a database object (field, table
form etc.). There is a function by that name and the DB could get
confused as to which you mean.

The compiler is expecting to find an object called

Program.Information.Date

The dots indicate it expects an object or a function. It's confused.
"Do you mean the function or a field? Hell I've got a query to run so
I'll just ask where the blinking value of the function is. Let them
figure it out...."

If you can afford to, I'd reccomend renaming the Date field to
somethig else like pfDate or Client_Date or something.
Otherwise you can try to replace the [ ] around the word Date in your
query, but it may still be flakey.

[sixdeuce62]

Hello,

Would Date In work? Do I just change the Date to Date In within the
expression?

Thanks again
On Mar 10, 9:50 pm, "storrboy" <[e-mail]:

Remember this snippet?

P.S. - Date is a really bad name for a database object (field, table
form etc.). There is a function by that name and the DB could get
confused as to which you mean.

The compiler is expecting to find an object called

Program.Information.Date

The dots indicate it expects an object or a function. It's confused.
"Do you mean the function or a field? Hell I've got a query to run so
I'll just ask where the blinking value of the function is. Let them
figure it out...."

If you can afford to, I'd reccomend renaming the Date field to
somethig else like pfDate or Client_Date or something.
Otherwise you can try to replace the [ ] around the word Date in your
query, but it may still be flakey.

[storrboy]

On Mar 10, 9:50 pm, "sixdeuce62" <[e-mail]:

Hello,

Would Date In work? Do I just change the Date to Date In within the
expression?

Thanks again

[Date In] should work. But not if the field itself is still named
[Date]. These are the options. (remember the Date field name needs to
look the same in all occurances of it, not just this line...

As is...
GROUP BY [Program Information].[Client ID], [Program Information].
[Date]

Rename the field in table Date In
GROUP BY [Program Information].[Client ID], [Program Information].
[Date In]
BE CAREFUL. Other forms, queries, reports, other front-ends or
connecting DBs may rely on this field being named Date. Unless you
know changing it will NOT affect anything else, do not change it
without carefully examining the effects and always make a back-up.

[sixdeuce62]

storrboy,

Thank you so much. That did it! It works now! I really appreciate all
your help. Thanks for sharing your knowledge.

sixdeuce62

On Mar 10, 11:11 pm, "storrboy" <[e-mail]:

On Mar 10, 9:50 pm, "sixdeuce62" <[e-mail]:

Hello,

Would Date In work? Do I just change the Date to Date In within the
expression?

Thanks again

[Date In] should work. But not if the field itself is still named
[Date]. These are the options. (remember the Date field name needs to
look the same in all occurances of it, not just this line...

As is...
GROUP BY [Program Information].[Client ID], [Program Information].
[Date]

Rename the field in table Date In
GROUP BY [Program Information].[Client ID], [Program Information].
[Date In]

BE CAREFUL. Other forms, queries, reports, other front-ends or
connecting DBs may rely on this field being named Date. Unless you
know changing it will NOT affect anything else, do not change it
without carefully examining the effects and always make a back-up.