Autonumber IDs and Text

[Debbie Nuding via AccessMonster.com]

This may be a design flaw on my part, but how do you get the text output
from an associated autonumber ID? When I run one of my queries using
several tables that have autonumber IDs for items, I get the text.
However, when I export this query results to Excel, all I get are the
numbers. This also happens with web output from asp pages generated by
MSFrontpage.

Where are I going wrong?
Thanks.

 

[Duane Hookom]

You must include the lookup tables in your queries. Consider reading this
link http://www.mvps.org/access/lookupfields.htm about the evils of lookup
fields in tables.

 

[Debbie Nuding via AccessMonster.com]

So lookups are not good in tables. Then, how do you handle this situation:
Say I have 2 tables: OwnersRiders and Horses
Horses can have an owner and many riders, some owners are also riders.

In my OwnersRiders table I have an OwnerRiderID and FirstName and LastName
fields.

In my Horses table I have HorseID and HorseName, I also included the
OwnerRiderID and a field for Owner. The OwnerRiderID is a combo box lookup
query to the OwnerRider table with the OwnerRiderID and Owner:
[OwnersandRiders].[FirstName] & " " & [LastName].
The field Owner is a list box query to Owner: [FirstName] & " " & [LastName]

I did take a look at the sample Northwinds database to kind of get an idea
of how to do some of this. I do most of my data entry in the tables
themselves and don't use forms. I'm beginning to get the idea that it is
best to create the queries and forms and do the data entry in forms instead
with lookups to the tables. Would that give me the text instead of the
numbers?

 

[Rick Brandt]

So lookups are not good in tables. Then, how do you handle this
situation: Say I have 2 tables: OwnersRiders and Horses
Horses can have an owner and many riders, some owners are also riders.

In my OwnersRiders table I have an OwnerRiderID and FirstName and
LastName fields.

In my Horses table I have HorseID and HorseName, I also included the
OwnerRiderID and a field for Owner. The OwnerRiderID is a combo box
lookup query to the OwnerRider table with the OwnerRiderID and Owner:
[OwnersandRiders].[FirstName] & " " & [LastName].
The field Owner is a list box query to Owner: [FirstName] & " " &
[LastName]

I did take a look at the sample Northwinds database to kind of get an
idea of how to do some of this. I do most of my data entry in the
tables themselves and don't use forms. I'm beginning to get the idea
that it is best to create the queries and forms and do the data entry
in forms instead with lookups to the tables. Would that give me the
text instead of the numbers?

A ComboBox. Enable the wizard first and it will walk you right through the
process. In fact if you use the wizard to create a two column ComboBox with a
numeric key field and a text field the wizard will bind to the numeric value and
hide it by default.

 

[Debbie Nuding via AccessMonster.com]

The IDs are spelled the same in all tables.
Table ownersandriders
ownerriderID-autonumber
firstname-text
lastname-text

Table horses
horseID-autonumber
horsename-text-no lookup
ownerriderID-number-with lookup to ownersandriders table on ownerriderID,
Owner: [tblownersandriders].[FirstName] & " " & [LastName]
owner-text-with lookup to table ownersandriders on owner:
[tblownersandriders].[FirstName] & " " & [LastName]

I know this looks crazy, but I was very frustrated when entering data in
the tables and only seeing numbers.

Are you saying that I should take the lookups out of the horses table and
create queries and forms to do the data entry?

You see, this is just the beginning of my problem because I have 2 other
tables, Classes and Entries, that have similar lookups to the horses and
owenrsandriders tables. Fortunately, I don't have so much data that I
can't re-enter it. I just want to get it fixed so I am looking at text
instead of numbers.

And yes, if you are ever an owner or rider or both, you will stay in the
ownersandriders table forever.
Thanks for your comments and help.
Debbie