Lookup Field Trouble

[George Reamy]

Hi, everyone. I've got another beginner's problem that I
figure you will be able to tell me how to fix easily.

I have a "Members and Friends" table with a one to many
relationship to a much smaller "Contributions" table.
The Contributions table has a lookup field with the
Member Number, Last Name, and First Name selected from
the big "Members and Friends" table. The Member Number,
an autonumber field, is the primary key in Members and
Friends table and, of course, is used for the one-to-many
relationship between the two tables. I've set the column
width for the Member Number to zero so it doesn't show,
and only the last name shows in the field in the
Contributons table.

Now for the problem: When I go to enter the amount of a
contribution, the names in the lookup field are in Member
Number order rather than alphabetical. It is HARD to
find people. How can I change things so that, when I
click on the Lookup drop down button in the Contributions
table, I get an alphabetized listing of people's last
names rather than a listing in numerical order? I've
tried sorting the big table first, but that doesn't have
any effect on the Contributions Table's drop down box.

Thanks!

--George

 

[Ken Snell]

Most of us will encourage you to not use lookup fields in your tables; also,
you are strongly encouraged to use forms for entering/manipulating data, not
directly into the tables.

That noted, open your table in design view, click on the field in question,
and then click on Lookup tab. You'll see the Row Source box and it has a
SELECT type statement in it. Go into that box, go to the end of the
statement.

Then, just to the left of the ";" character, type the following (note that
the first character below is a blank space!):

ORDER BY [Last Name], [First Name]

This should sort your dropdown list the way you seek.

 

[Aziz]

George,

I've quickly read your message and so may on the wrong
path here.

If you go to design view of your table that contains the
lookup field, press the elipsis button (it will have 3
dots on it). You can sort on the field you want from
there. HTH.

Aziz