Using Unique Values and Unique Records

P

Peter Hallett

A combo box is used to read surnames from a table. Unsurprisingly, a
significant number of the people featured in the table share surnames but
setting Unique Values to Yes, in the SQL, ensures that the names displayed in
the drop-down box are not duplicated. A secondary mechanism is then invoked
to select the desired individual in cases where a shared surname is chosen.
This all works well.

Each person listed in the table has a unique ID, which also needs to be
read. It appeared a good idea to include this field in the SQL and allocate
the value to another column in the combo box. The ID could then be read
directly as Name.Column(1), where the required surname appeared in
Name.Column(0) (or just Name). Immediately a second column was added,
however, the Unique Value setting failed to filter out duplicate surnames,
presumably for the reason that, with two fields involved, a difference in one
of them is sufficient to render the two records unique. However, name
duplication also occurs when Unique Records is set to Yes, which I find
rather puzzling.

It seems that I will be forced to change all the coding back to the original
version, where the combo box was used to select the name and this was then
used, rather inefficiently, to find the matching ID with a DLookup on the
same table. Apart from that, changing all the code is going to prove a bit
of a nuisance. Is there away of retaining the present arrangement?
 
D

Douglas J. Steele

"Unique" sets the SQL for the query to SELECT DISTINCT. If you've got two
fields you're selecting, the combination of the two fields have to be
unique.

What you could do is use two separate combo boxes. Put the existing query
that returns only the unique names as the RowSource for the first combo box.
Once you've selected the name, populate the second combo box with the
details of each person with that name:

Private Sub cboSurname_AfterUpdate()

Me!cboIndividual.RowSource = "SELECT DISTINCT Id, Surname " & _
"FROM MyTable WHERE Surname = """ & Me!cboSurname & """"

End Sub
 
J

Jeff Boyce

Peter

It all starts with the data ... and I can't quite envision the underlying
tables.

A common approach to using a combobox to select a (person, country, piece of
equipment, ...) is to use the first column to hold the ID of the record and
the second to hold the looked-up value. Then, by setting the column width
of the first column to zero, and making sure that first one is the 'bound'
column, your combobox only shows the second column, but saves the first.

By the way, if you are forcing the user to pick a surname first, then
present a way to resolve the duplicates, you and your users may be working
harder than you need to. Another common approach is to use a 'calculated'
field in a query to concatenate both FName and LName in a single field (the
'second' column), and keep the ID in the first.

Now, the user can select the individual immediately.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

Peter Hallett

Thanks Doug,

I get a bit confused over Unique Values and Unique Records. To my simple
mind there appears to be some redundancy. The first is easy enough to
understand and certainly prevents the display of duplicate values in a combo
box, when the latter is bound to a single field in the source table. If
other fields are included then the definition of Unique Record can be a bit
confusing. I was, perhaps, hoping that two records would be treated as
unique if ALL the constituent fields were different rather than ANY field, as
seems to be the case. In the mean time, I am having some difficulty in
appreciating the difference. In the example I quoted, for example, the
result was exactly the same whether Unique Values or Unique Records was
selected.

I note your solution but since most surnames in the table are unique a
single combo box selection is generally appropriate. Only where duplication
is detected is second stage discrimination called for. In that case, in a
similar approach to your own, I display a form, showing all the identical
surname holders together with their first names and addresses. The record
selector is then used to choose the appropriate person.

My main intention in raising the issue of unique values and records was to
see if the rather clumsy double querying requirement could be avoided. There
appears to be little choice but to restrict the first query to a single
parameter (e.g. surname) if duplicates are to be excluded and then to use a
second, closely similar, query on the same table in order to determine the
ID. I have chosen DLookup for the latter because that helps to create the
illusion that, in the majority of cases, the process is single-staged. As it
happens, no additional run-time is observable but the need to requery the
same table for two values which could as easily have been determined with a
single visit is hardly elegant.

In the mean time, I’ll add an ‘Exclusively Unique’ setting to my Access wish
list.
 
P

Peter Hallett

Thanks for your response, Jeff,

I am a little puzzled over the details so I hope you won’t mind if I ask for
clarification.

“It all starts with the data,†as you say. The structure is very simple. A
single table lists names and IDs. The IDs are AutoNumbered and therefore
always unique. Surnames, by their nature, are not. The requirement is to
find the ID associated with a given surname entered via a combo box. Only
the surname is known to the operator. The ID is generally irrelevant. Where
the surname is unique, the operation is to be completed in a single step.
Where the surname is shared, its input triggers the display of a secondary
selection form, listing further details enabling the required individual to
be chosen.

So far so good. If the combo box is bound to the surname field in the
table, then setting Unique Values to Yes ensures that duplicate names are
excluded from the drop-down list, as required.

Having chosen a name, the associated ID has then to be determined.
Originally a DLookup was used but it was realised that this essentially
duplicated the combo box’s SQL, requiring a second interrogation of the
table. Better, it seemed, to read both surname and ID in a single operation,
and then to retrieve the latter directly from the appropriate column of the
combo box. Immediately a second column was added, however, the required
‘Unique’ property was lost and all surnames were listed, including the
duplicates. This is the point at which I lose you, I am afraid.

Surely the SQL statement will only provide a value for a combo box column if
the corresponding SQL ‘Show’ check box is ticked? In the case of a
two-column combo box, featuring surnames and IDs, this results in associated
records which, as Doug points out, will be regarded as unique if either of
the constituent fields differs from those of other records. All instances of
a given surname are therefore listed because the associated IDs are
different. The required exclusion of duplicate surnames is lost. Have I
missed something?
 
J

Jeff Boyce

Peter

See comments in-line below...

Peter Hallett said:
Thanks for your response, Jeff,

I am a little puzzled over the details so I hope you won't mind if I ask
for
clarification.

"It all starts with the data," as you say. The structure is very simple.
A
single table lists names and IDs.

"names" ... do you mean "John", or "John L." or "Smith" or "John L. Smith"
or "Cher" or "Jean Claude van Damm" or ...? "name" is ambiguous, and "how"
depends on "what".
The IDs are AutoNumbered and therefore
always unique. Surnames, by their nature, are not. The requirement is to
find the ID associated with a given surname entered via a combo box. Only
the surname is known to the operator. The ID is generally irrelevant.
Where
the surname is unique, the operation is to be completed in a single step.
Where the surname is shared, its input triggers the display of a secondary
selection form, listing further details enabling the required individual
to
be chosen.

If the "secondary information" is available from the start, why not display
that along with the surnames? That way the user has the opportunity to
select the "right" one in one step.
So far so good. If the combo box is bound to the surname field in the
table, then setting Unique Values to Yes ensures that duplicate names are
excluded from the drop-down list, as required.

Having chosen a name, the associated ID has then to be determined.
Originally a DLookup was used but it was realised that this essentially
duplicated the combo box's SQL, requiring a second interrogation of the
table. Better, it seemed, to read both surname and ID in a single
operation,
and then to retrieve the latter directly from the appropriate column of
the
combo box. Immediately a second column was added, however, the required
'Unique' property was lost and all surnames were listed, including the
duplicates. This is the point at which I lose you, I am afraid.

Yes, if you must force two steps, then the first step (unique names) removes
the IDs (or vice versa), because you could have 30 "John"s -- which ID is
associated with "it"?
Surely the SQL statement will only provide a value for a combo box column
if
the corresponding SQL 'Show' check box is ticked? In the case of a
two-column combo box, featuring surnames and IDs, this results in
associated
records which, as Doug points out, will be regarded as unique if either of
the constituent fields differs from those of other records. All instances
of
a given surname are therefore listed because the associated IDs are
different. The required exclusion of duplicate surnames is lost. Have I
missed something?

It may be that I'm missing something. It seems like you've decided on the
"how" already -- the two-step process. If so, you may have already
uncovered the best (given that constraint). I was asking if you were
'welded' to keeping that constraint?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

Peter Hallett

Thanks, again, Jeff for your response. I will address your points in order.
1. Names and addresses are stored in the familiar format, namely ‘First
Name’, ‘Last Name’, ‘Address – Line 1’, ‘Address – Line 2’, all allocated to
their own separate fields.
2. Interrogation is by surname, via a combo box, the drop-down list of which
displays ‘Last Names’ in ascending alphabetic order. With unique values set
to Yes, no surnames are duplicated, as required.
3. Perhaps 8, or even 9, surnames out of every 10 are unique. It is known
that the user would object to being forced to make a secondary selection in
cases where only a single choice exists. I have to say that it is a view
with which I concur.
4. No. Actually I was not ‘welded’ (nor even wedded) to any particular path
when I posed my initial question. I had two solutions, both of which had
weaknesses. The first gave me the required list of unique surnames but only
at the cost of making two almost identical sequential queries on the members’
table for every surname entered. The code looked neither well-designed nor
efficient, irrespective of the fact that it did not introduce a detectable
additional run-time overhead.
The second solution initially looked distinctly more attractive – a one-step
query reading both surname and ID simultaneously, the latter then being
directly retrieved from a hidden additional column in the combo box. It was
not until all the code had been changed to use this preferred solution (there
are about 15 combo boxes that use the technique) that the Achilles heal was
revealed. The surnames displayed in the drop-down combo box list, included
all the unwanted duplicates. This would have been wholly unacceptable to the
user and left me with a dilemma – accept the unwanted task of changing all
the coding back to the original, less elegant, version or see if someone in
the forum could suggest a way of retaining the preferred multi-column combo
box solution while eliminating the duplicate surnames? The answer, it seems,
is ‘bite the bullet and restore the original code’. It is not the one I had
hoped for but at least, as is usual with Access, there is always a way, even
if, on occasions, it is not an entirely satisfactory one.
 
J

Jeff Boyce

Peter

Perhaps I haven't seen the big picture clearly enough yet.

If you have ID and LastName and FirstName and ..., what about the idea of
using a query to get ID & LastName and FirstName to "fill" the combobox?

That way, although there could (rarely, based on your stats) be more than
one "Smith", the FirstName field would allow the user to differentiate which
Smith was selected.

I guess I'm not understanding what is being duplicated?

Also, you originally mentioned "in the table". Is there a chance that,
although you posted in a "forms"-related newsgroup, you are trying to do
this directly in your table? If so, STOP! Access tables store data, Access
forms display it. It would explain a lot of the difficulty you are seeing
if you happen to be trying to do this directly in the table.

(just a little) more information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

Peter Hallett

Jeff,

I do appreciate your willingness to stick with this one but I wonder whether
we are not drifting off course somewhat.

The purpose of my original question was to elucidate the properties of the
query settings ‘Unique Values’ and ‘Unique Records’ particularly in order to
understand the precise differences between them. The intention was to see if
the unwelcome side effect, namely that the elimination of duplicates feature
is generally lost if more than one field is included in the query, could be
circumvented. The hope was to enlighten me and anyone else reading the
thread. I am not sure that that goal has yet been achieved. We are in
danger of discussing the alternative question, “Is there another way?†which
could quickly lead us into a cul-de-sac.

There are details which I deliberately ignored when framing the original
question, simply because they confused rather than enlightened. Most were
irrelevant but since you raise related issues perhaps I can close off that
route?

The ‘table’ referred to consists of nearly 300 personal records, each of
which comprises no less than 45 fields. The specification calls for the
display of a form bound to a selected record, leaving open the question of
how that selection is to be made. At one extreme, as you have hinted, all
possible forms could be displayed and chosen by the use of navigation
buttons. With almost 300 potential forms involved that is clearly
impractical. The next option is to display only those forms which relate to
records featuring a particular value of a given parameter. Choice by surname
is seen as most appropriate. This calls for a pre-selection mechanism – e.g.
a combo box – but, as I earlier pointed out, only about 80-90% of surnames
are unique. In the remaining cases the resulting direct form display would
still require the use of navigation buttons to make the appropriate choice,
revealing two further major difficulties. Fathers often pass their first
names on to their sons. Even more inconvenient, many of those sons continue
to live in the family home. Therefore the inclusion of first name and even
address does not necessarily provide a fool-proof method of choosing the
desired individual or, hence, the right form. Add to this perhaps the most
lethal ingredient of all – the user – and the stage is set for chaos. I am
still astounded at the willingness of operators to select forms which bear
only the vaguest relationship to the information available to them, let alone
mis-selection when the names and addresses are all the same. This is largely
why I avoided discussion of such issues when posing my original question. It
takes us into the realms of human behavioural and cognitive psychology and,
frankly, we would best not go there.

One trick that appears to work better than a number of others is to offer a
multi-layered selection process, where each layer looks entirely different
from its successor or predecessor. It somehow helps to retain the user’s
attention, whilst minimizing the number of selection steps involved and the
complexity of each. First he sees a small form featuring a combo box. In 80
or 90% of cases, supplying the required surname yields the required member’s
data entry form directly. In the remaining cases, a very different
continuous form is displayed showing the full name and address of members
sharing the proffered surname. In the majority of the remaining 10 to 20% of
instances this allows the intended nominee to be chosen but, of course, in
the cases mentioned above, where individuals share not only their surnames
but also their first names and addresses, such additional information is not
sufficient. Further controls based on selected fields from the table are
therefore added to continuous form. Clearly only a small subset of the 45
fields contained in each record can be displayed so these are carefully
chosen to provide the necessary discrimination. Even so, the secondary
selection form still stretches across the screen, but it seems to do its job.
Mis-selection now occurs far less frequently than when multiple forms
resulting from the initial surname selection were displayed, in form view,
effectively stacked on top of one another to be chosen by means of the
navigation buttons. Not infrequently the user simply failed to notice the
navigation bar or to appreciate that he had a choice of forms.

I trust that this helps to clarify the issues and that you will appreciate
why I chose not to follow this route. In the end, it comes down to personal
preferences and an adoption of techniques which work. There is as much of
the subjective as the objective in this and I doubt that we would add much to
the discussion by pursuing such issues.
 
J

Jeff Boyce

Peter

Based on using F1 for HELP in Access, Unique Values displays unique sets of
data/records. Unique Records doesn't return duplicate records ... but isn't
applied when you only have one table in the query.

Good luck (and sorry for the divergence...)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Douglas J. Steele

I'm not sure about that, Jeff.

If your table has, say, three fields:

Field1 Field2 Field3
1 1 1
1 2 1
2 1 1
2 2 1

SELECT DISTINCT Field1, Field3FROM MyTable will return

Field1 Field3
1 1
2 1

SELECT DISTINCTROW Field1, Field3FROM MyTable will return

Field1 Field3
1 1
1 1
2 1
2 1
 
D

Douglas J. Steele

Hmm. You're right: Help explicitly states "DISTINCTROW has an effect only
when you select fields from some, but not all, of the tables used in the
query. DISTINCTROW is ignored if your query includes only one table, or if
you output fields from all tables."

Help is wrong in this case. I actually ran those queries before posting.
 
P

Peter Hallett

Well, that was an interesting canter around the block and Doug’s very
pertinent example got us there in the end. I think I understand things a lot
better now but if MS Help is wrong, even confusing MVPs, what hope is there
for us mere mortals?

What now appears obvious is that, in my case, a two-stage, double-querying
process is unavoidable – but, then, hindsight has always been a powerful
tool. Simplifying Doug’s example somewhat to a two-field table we have:-

F1 F2
A 1
B 2
B 3
C 4
D 5
D 6
D 7
…

Where A…D… represent surnames and 1…7… represent the corresponding IDs. If
the latter are all to be queried, or read, in a single step, then there is no
option but to accept duplicate surnames. If, however, the latter are to be
eliminated then so to must be the corresponding IDs, which will then have to
be retrieved by a separate process. I don’t see any amount of fancy SQL
footwork getting around that.

Getting to grips with Unique Values and Unique Records has certainly been
worthwhile. It is just a pity that, in programming terms, like the Grand Old
Duke of York, I marched my troops to the top of the hill only to have to
march them all the way down again. Maybe I should have remembered the
carpenter’s maxim – “Measure twice. Cut once.â€

In the mean time, Doug & Jeff, thanks again. That was very useful.
 

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

Top