Lookup for a field using only unique values.

[Neutralizer]

Hi,

My question is, how can I set a field in a table as a lookup, but only get the unique values from the table that is being looked up?

For instance say I have a table that consists of three fields, A, B and C as below.

Table 1:

A B C
1 2 3
1 1 1
1 2 2

Now, say I create a new table with fields called X and Y, as below.

Table 2:
X Y

What I want to know is this:
If I make the X field in Table 2 a lookup of the B field in Table 1, how can I make the lookup return only the unique records in this field?
(i.e. The lookup in the X field of Table 2 would only show (2,1) and not (2,1,2).

Cheers

 

[boblarson]

Just needed to give you this info. Do NOT create a lookup at table level. You can use a combo box or list box on a FORM to pull the values but do NOT set them up at table level. See here for why:
http://access.mvps.org/access/lookupfields.htm

Second, please use a real example. The "simplified" example makes it difficult to give you real advice on how you should have things set up since there is no information for us to glean from your example. Why are you storing the same data twice? That is the question. There may be a good reason for it, but it is hard to tell and you may need a better way to set up your structure or usage and, if we knew more about it, we could give some advice.

 

[boblarson]

Okay, I found your other post where you described things a little better. You would have a table which has the values for the lookup (used on a FORM, not in a table) and you would have only unique values there for lookup. You would then store the ID from the record from that table in the other table (I'm assuming the junction table) along with the ID of the other table which is being joined by the junction table.