Lookup field

[Mariah]

I understand that using a lookup field in a table is considered a bad thing,
and truth be told I am running into some issues and would like to get rid of
them (not able to sort alphabetically as the database is sorting based on
member's autonumber). However I am not sure what would work.

We have a members table which has names and addresses, this serves as our
base table.
We also manage a lot of different information and these have their own
tables. For instance: employment, vital statistics, mailing lists, etc.
Autonumber has been linking the members through the tables.
A query combines the surname, first and last names together from the base
table into one field called fullname.
The employment table (mailings table, vital stats table, etc) has a lookup
field that references the fullname from the query so that we can "match" the
information to the individual. While I see the name, it is really storing
the autonumber behind the name, which has been ok until now -- however new
members mean that the autonumber is not the same order as the alphabet, so we
have A showing up after T, etc.

If there is a standard way to get rid of the lookup field and reference
these people through the tables another way, what would that be?

Thanks in advance for your assistance.

 

[John W. Vinson]

I understand that using a lookup field in a table is considered a bad thing,
and truth be told I am running into some issues and would like to get rid of
them (not able to sort alphabetically as the database is sorting based on
member's autonumber). However I am not sure what would work.

First off:

You can't sort tables.
You don't NEED to sort tables.
You shouldn't ever even LOOK at tables unless you're debugging.

If you want to see records in a certain order, create a Query based on the
table and sort by the name there.

We have a members table which has names and addresses, this serves as our
base table.
We also manage a lot of different information and these have their own
tables. For instance: employment, vital statistics, mailing lists, etc.
Autonumber has been linking the members through the tables.

Surely not linking autonumber to autonumber!? the child tables should have a
Long Integer foreign key linked to the autonumber.

A query combines the surname, first and last names together from the base
table into one field called fullname.
The employment table (mailings table, vital stats table, etc) has a lookup
field that references the fullname from the query so that we can "match" the
information to the individual. While I see the name, it is really storing
the autonumber behind the name, which has been ok until now -- however new
members mean that the autonumber is not the same order as the alphabet, so we
have A showing up after T, etc.

If there is a standard way to get rid of the lookup field and reference
these people through the tables another way, what would that be?

Just join the members table to the employment table in the query; include the
LastName and FirstName fields, in that order (in addition to the fullname
field); and put Ascending on the Sort row of the query grid.

For viewing onscreen or editing, you should use a Form based on Members (with
a combo box created by the combo wizard to locate a member quickly), with a
Subform (or several subforms) to display and edit the data in the child
tables. Table datasheets are NOT designed or appropriate for data entry or
editing; use Forms instead.

 

[Mariah]

We do use forms, we don't edit within tables. But since I was only seeking
to get rid of the lookup field I didn't think speaking to that was an issue.
And no, only the customer address table has an autonumber. The lookup field
(drop down box on the FORM) shows the name but references that autonumber and
sees the information as belonging to the same person.

Fairly new access user seeks confident patient professional with effective
written communication skills. Must be knowledgable in MSAccess and have
strong interpersonal skills. Ability to analyze and address specific
challenges in a positive and constructive manner a must. (Someone else
please?)

 

[Douglas J. Steele]

Are you saying that when you create a form and add a combo box that uses the
query based on the Members table as its RowSource, the entries in the combo
box aren't alphabetic? Put an appropriate ORDER BY clause on the query.

 

[John W. Vinson]

We do use forms, we don't edit within tables. But since I was only seeking
to get rid of the lookup field I didn't think speaking to that was an issue.
And no, only the customer address table has an autonumber. The lookup field
(drop down box on the FORM) shows the name but references that autonumber and
sees the information as belonging to the same person.

Fairly new access user seeks confident patient professional with effective
written communication skills. Must be knowledgable in MSAccess and have
strong interpersonal skills. Ability to analyze and address specific
challenges in a positive and constructive manner a must. (Someone else
please?)

My apologies, Mariah. That was excessivly snarky of me.

I think Douglas has a solution for you - use a Query to specify the sort
order. Don't rely on the order of records in a table for *anything*; not only
can you not control that order, but Access will present the records in
whatever order it finds convenient, unless you specify the sort order using a
Query or a form or report's OrderBy property.