Eliminating duplicates in a drop down list

[LightByrd]

I have constructed a database for a victim services organization with some
2000 clients.
(sad commentary on today's world)
One of the required reports is all the activity of an individual client.
Obviously this suggests a one-to-many arrangement, as one client may be
associated with more than on incident.
But each callout record is unique, even though the client may be the same.

I set up a popup with a text control wherein a partspec of the client's last
name is entered. That information is then sent to a drop down box on the
same form with this SQL in the rowsource property:

SELECT Callout.IncidentID, callout.ClientLastName & ", " &
Callout.ClientFirstName AS [Full] FROM Callout WHERE
(((callout.ClientLastName) Like [txtClientLastName] & "*")) ORDER BY
Callout.ClientLastName & ", " & Callout.ClientFirstName;

This works fine--it sends all incidents related to this person to the
printed report.
Let's say Jane Doe has required our services 3 times.
The report prints all 3 occurrences.

But the drop down box has her name 3 times.

Any way to eliminate the duplicates in the DD box, but still print all 3
records?
Thanks

 

[Jeanette Cunningham]

You can use group by in the query for the drop down.
This will have the effect of only showing each name once.

In the query in design view, right click in the query grid and choose
Totals.

Switch the query to datasheet view to check that this query is giving you
what you want.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Clifford Bass via AccessMonster.com]

Hi Richard,

The quick but bad answer is to remove the incident ID column and use a
"select unique.." statement for the drop down box.

SELECT unique callout.ClientLastName ...

The longer good answer starts with some questions: How do you know that
Jane Doe #1 is the same as Jane Doe #2 and the same as Jane Doe #3? Does
you database really only identify the people by name? If so, then you are
going to have a problem. You really need to have some kind of unique
person/client ID with each person being recorded in a person/client table.
And in the call out table reference the person/client ID rather than the
client names

tblClients
ClientID
LastName
FirstName
etc.

tblCallouts
IncidentID
ClientID
etc.

Then your drop down list will use the client table as its source and it
will have both the ClientID and the names from the clients table and by
definition, they will be unique. Your use will select the appropriate
person (i.e. Jane Doe #2) and your report will use the client ID of the
selected person to pull all callouts for that person and you will still get
the three (or whatever number) records.

Hope that helps,

Clifford Bass

I have constructed a database for a victim services organization with some
2000 clients.
(sad commentary on today's world)
One of the required reports is all the activity of an individual client.
Obviously this suggests a one-to-many arrangement, as one client may be
associated with more than on incident.
But each callout record is unique, even though the client may be the same.

I set up a popup with a text control wherein a partspec of the client's last
name is entered. That information is then sent to a drop down box on the
same form with this SQL in the rowsource property:

SELECT Callout.IncidentID, callout.ClientLastName & ", " &
Callout.ClientFirstName AS [Full] FROM Callout WHERE
(((callout.ClientLastName) Like [txtClientLastName] & "*")) ORDER BY
Callout.ClientLastName & ", " & Callout.ClientFirstName;

This works fine--it sends all incidents related to this person to the
printed report.
Let's say Jane Doe has required our services 3 times.
The report prints all 3 occurrences.

But the drop down box has her name 3 times.

Any way to eliminate the duplicates in the DD box, but still print all 3
records?
Thanks

 

[LightByrd]

I have constructed a database for a victim services organization with some
2000 clients.
(sad commentary on today's world)
One of the required reports is all the activity of an individual client.
Obviously this suggests a one-to-many arrangement, as one client may be
associated with more than on incident.
But each callout record is unique, even though the client may be the same.

I set up a popup with a text control wherein a partspec of the client's
last
name is entered. That information is then sent to a drop down box on the
same form with this SQL in the rowsource property:

SELECT Callout.IncidentID, callout.ClientLastName & ", " &
Callout.ClientFirstName AS [Full] FROM Callout WHERE
(((callout.ClientLastName) Like [txtClientLastName] & "*")) ORDER BY
Callout.ClientLastName & ", " & Callout.ClientFirstName;

This works fine--it sends all incidents related to this person to the
printed report.
Let's say Jane Doe has required our services 3 times.
The report prints all 3 occurrences.

But the drop down box has her name 3 times.

Any way to eliminate the duplicates in the DD box, but still print all 3
records?
Thanks

Hi Richard,

The quick but bad answer is to remove the incident ID column and use a
"select unique.." statement for the drop down box.

SELECT unique callout.ClientLastName ...

The longer good answer starts with some questions: How do you know
that
Jane Doe #1 is the same as Jane Doe #2 and the same as Jane Doe #3? Does
you database really only identify the people by name? If so, then you are
going to have a problem. You really need to have some kind of unique
person/client ID with each person being recorded in a person/client table.
And in the call out table reference the person/client ID rather than the
client names

tblClients
ClientID
LastName
FirstName
etc.

tblCallouts
IncidentID
ClientID
etc.

Then your drop down list will use the client table as its source and
it
will have both the ClientID and the names from the clients table and by
definition, they will be unique. Your use will select the appropriate
person (i.e. Jane Doe #2) and your report will use the client ID of the
selected person to pull all callouts for that person and you will still
get
the three (or whatever number) records.

Hope that helps,
Clifford Bass

Thanks Cliff....
Exactly right!
I had thought of that myself as well!
That's how it should have been done at the design stage.
However it wasn't, reason being is that the whole database was a 2 year work
in progress with modifications here and there.
There was no way at the design stage to forecast governmental whims about
what reports they would want.

Alas, even a client date of birth field would solve the problem.
Ironic that other govt. agencies (police etc.) key off such a field.

Thanks for your help & corroboration.

 

[Clifford Bass via AccessMonster.com]

Hi Richard,

You are welcome!

Clifford Bass

Thanks Cliff....
Exactly right!
I had thought of that myself as well!
That's how it should have been done at the design stage.
However it wasn't, reason being is that the whole database was a 2 year work
in progress with modifications here and there.
There was no way at the design stage to forecast governmental whims about
what reports they would want.

Alas, even a client date of birth field would solve the problem.
Ironic that other govt. agencies (police etc.) key off such a field.

Thanks for your help & corroboration.

 

[LightByrd]

Thank you Jeanette,
I can't seem to get it to work.
The query has the ID field, the last name, the first name and an incident
type field
e.g. 456 Smith John spousal abuse
I tried what you said and I get "group bys" in all fields.
Tried various combos like count, first last, etc.
Nada...

Any other ideas?

--
Regards,
Richard Harison

You can use group by in the query for the drop down.
This will have the effect of only showing each name once.

In the query in design view, right click in the query grid and choose
Totals.

Switch the query to datasheet view to check that this query is giving you
what you want.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have constructed a database for a victim services organization with some
2000 clients.
(sad commentary on today's world)
One of the required reports is all the activity of an individual client.
Obviously this suggests a one-to-many arrangement, as one client may be
associated with more than on incident.
But each callout record is unique, even though the client may be the
same.

I set up a popup with a text control wherein a partspec of the client's
last name is entered. That information is then sent to a drop down box
on the same form with this SQL in the rowsource property:

SELECT Callout.IncidentID, callout.ClientLastName & ", " &
Callout.ClientFirstName AS [Full] FROM Callout WHERE
(((callout.ClientLastName) Like [txtClientLastName] & "*")) ORDER BY
Callout.ClientLastName & ", " & Callout.ClientFirstName;

This works fine--it sends all incidents related to this person to the
printed report.
Let's say Jane Doe has required our services 3 times.
The report prints all 3 occurrences.

But the drop down box has her name 3 times.

Any way to eliminate the duplicates in the DD box, but still print all 3
records?
Thanks

 

[LightByrd]

Hi Richard,

The quick but bad answer is to remove the incident ID column and use a
"select unique.." statement for the drop down box.

SELECT unique callout.ClientLastName ...

The longer good answer starts with some questions: How do you know
that
Jane Doe #1 is the same as Jane Doe #2 and the same as Jane Doe #3? Does
you database really only identify the people by name? If so, then you are
going to have a problem. You really need to have some kind of unique
person/client ID with each person being recorded in a person/client table.
And in the call out table reference the person/client ID rather than the
client names

tblClients
ClientID
LastName
FirstName
etc.

tblCallouts
IncidentID
ClientID
etc.

Then your drop down list will use the client table as its source and
it
will have both the ClientID and the names from the clients table and by
definition, they will be unique. Your use will select the appropriate
person (i.e. Jane Doe #2) and your report will use the client ID of the
selected person to pull all callouts for that person and you will still
get
the three (or whatever number) records.

Hope that helps,

Clifford Bass

I have constructed a database for a victim services organization with some
2000 clients.
(sad commentary on today's world)
One of the required reports is all the activity of an individual client.
Obviously this suggests a one-to-many arrangement, as one client may be
associated with more than on incident.
But each callout record is unique, even though the client may be the same.

I set up a popup with a text control wherein a partspec of the client's
last
name is entered. That information is then sent to a drop down box on the
same form with this SQL in the rowsource property:

SELECT Callout.IncidentID, callout.ClientLastName & ", " &
Callout.ClientFirstName AS [Full] FROM Callout WHERE
(((callout.ClientLastName) Like [txtClientLastName] & "*")) ORDER BY
Callout.ClientLastName & ", " & Callout.ClientFirstName;

This works fine--it sends all incidents related to this person to the
printed report.
Let's say Jane Doe has required our services 3 times.
The report prints all 3 occurrences.

But the drop down box has her name 3 times.

Any way to eliminate the duplicates in the DD box, but still print all 3
records?
Thanks

Hi again....
In the SQL statement I tried SELECT UNIQUE SELECT DISTINCTROW and SELECT
DISTINCT.
Unique gave me an error msg.
The other 2 did not solve the duplication problem

 

[Clifford Bass via AccessMonster.com]

Hi Richard,

My error :-(. Distinct is correct. Note the removal of "Callout.
IncidentID" from the select list. If you leave it in then you do not have
any way to get the names to condense.

SELECT DISTINCT callout.ClientLastName & ", " &
Callout.ClientFirstName AS [Full] FROM Callout WHERE
(((callout.ClientLastName) Like [txtClientLastName] & "*")) ORDER BY
Callout.ClientLastName & ", " & Callout.ClientFirstName;

Clifford Bass

Hi Richard,

[quoted text clipped - 67 lines]
Hi again....
In the SQL statement I tried SELECT UNIQUE SELECT DISTINCTROW and SELECT
DISTINCT.
Unique gave me an error msg.
The other 2 did not solve the duplication problem

 

[LightByrd]

Hi Richard,

My error :-(. Distinct is correct. Note the removal of "Callout.
IncidentID" from the select list. If you leave it in then you do not have
any way to get the names to condense.

SELECT DISTINCT callout.ClientLastName & ", " &
Callout.ClientFirstName AS [Full] FROM Callout WHERE
(((callout.ClientLastName) Like [txtClientLastName] & "*")) ORDER BY
Callout.ClientLastName & ", " & Callout.ClientFirstName;

Clifford Bass

Bingo!
Additionally I was able to delete all the callout. prefixes
and the parentheses as well.
Cleaner looking and works great!
Still have the Jane Doe #1 and Jane Doe #2 problem to sort out.
Don't think the sites would be too keen having to fill in 10,000 records
with a date-of-birth field!

thanks!

 

[John W. Vinson]

Still have the Jane Doe #1 and Jane Doe #2 problem to sort out.
Don't think the sites would be too keen having to fill in 10,000 records
with a date-of-birth field!

That's a REAL business-model problem.

Names are *not unique*. I once worked at a university with Prof. John W.
Vinson. He got one of my paychecks, I got a lot of his junk mail - because the
university didn't reliably distinguish people as unique people rather than as
nonunique names.

(I just wish I'd gotten one of his paychecks - he was a tenured med school
prof, I was a lowly postdoc).

 

[Clifford Bass via AccessMonster.com]

Hi Richard,

You are welcome! You might point out to the sites that correcting 10,
000 records now is much easier than 50,000 later on. That kind of work is
what data-entry clerks or temporary workers are for.

Good Luck,

Clifford Bass

 

[LightByrd]

That being said...
I think the better and less labor intensive solution was your original idea
of unique client IDs.
Splitting the main table into client info only and remainder of records and
linking it in a one-to-many relationship would not be that great a deal.
I could use a make table query.
Problem now is I don't want to do all this and then hope they are
interested.
They may not get that much of a call for that particular report. Time will
tell
Thanks again...

--
Richard

Hi Richard,

You are welcome! You might point out to the sites that correcting 10,
000 records now is much easier than 50,000 later on. That kind of work is
what data-entry clerks or temporary workers are for.

Good Luck,

Clifford Bass

Bingo!
Additionally I was able to delete all the callout. prefixes
and the parentheses as well.
Cleaner looking and works great!
Still have the Jane Doe #1 and Jane Doe #2 problem to sort out.
Don't think the sites would be too keen having to fill in 10,000 records
with a date-of-birth field!

thanks!

 

[LightByrd]

That's a REAL business-model problem.

Names are *not unique*. I once worked at a university with Prof. John W.
Vinson. He got one of my paychecks, I got a lot of his junk mail - because
the
university didn't reliably distinguish people as unique people rather than
as
nonunique names.

(I just wish I'd gotten one of his paychecks - he was a tenured med school
prof, I was a lowly postdoc).

----- Original Message -----
From: "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com>
Newsgroups: Microsoft.public.access.formscoding
Sent: Thursday, January 21, 2010 6:50 PM
Subject: Re: Eliminating duplicates in a drop down list

That's a REAL business-model problem.

Names are *not unique*. I once worked at a university with Prof. John W.
Vinson. He got one of my paychecks, I got a lot of his junk mail - because
the
university didn't reliably distinguish people as unique people rather than
as
nonunique names.

(I just wish I'd gotten one of his paychecks - he was a tenured med school
prof, I was a lowly postdoc).

Agreed!
You'da thunk they'd have wanted a Date-of-Birth field.
That would really have lengthened the odds of a misfit.
OR
that their concept of what they wanted was more thought out from the get go.
Can't blame them though...govt. kept changing the rules & moving the goal
posts.
2nd law of thermodynamics? :<)

 

[John W. Vinson]

That being said...
I think the better and less labor intensive solution was your original idea
of unique client IDs.
Splitting the main table into client info only and remainder of records and
linking it in a one-to-many relationship would not be that great a deal.
I could use a make table query.

I agree.

Problem now is I don't want to do all this and then hope they are
interested.
They may not get that much of a call for that particular report. Time will
tell

A selling point to do it is that doing so will permit a lot more flexibility
in creating *other* reports. I'm not sure what use is being made - or could be
made - of this data, but (for example) being able to track individuals over
multiple visits may be of value.

 

[LightByrd]

I agree.


A selling point to do it is that doing so will permit a lot more
flexibility
in creating *other* reports. I'm not sure what use is being made - or
could be
made - of this data, but (for example) being able to track individuals
over
multiple visits may be of value.

True...
but remember we are dealing with a govt. run agency.
The good people (mostly volunteers) who are the nuts & bolts of this program
have to justify expenses.
So far the waters have been quiet.
Another problem.....
Do I trust an organization of sites with IT people of varying abilities to
be able to create the make table query and correctly relate the tables?
The information is confidential and not all sites would go for emailing me
the data files so I could do the work.

At any rate, my situation now is that I feel I would be abusing the
newsgroup
to further the thread, since my formscoding questions have been wonderfully
answered.
Thanks to you and to all!!!

 

[John W. Vinson]

At any rate, my situation now is that I feel I would be abusing the
newsgroup
to further the thread, since my formscoding questions have been wonderfully
answered.

Best of luck... to you, to the IT people, and especially to the people who are
the subject of the database.

 

[Clifford Bass via AccessMonster.com]

Hi Richard,

You are welcome! Good luck to you on the project and hopefully you will
not be picking up the pieces later on because they would not deal with the
issues now.

Clifford Bass

 

[LightByrd]

Best of luck... to you, to the IT people, and especially to the people who
are
the subject of the database.

Will be in communication with them & explain it.
We'll see!
Thanks

 

[LightByrd]

Hi Richard,

You are welcome! Good luck to you on the project and hopefully you
will
not be picking up the pieces later on because they would not deal with the
issues now.

Clifford Bass

Will be in communication with them & explain it.
We'll see!
Thanks