Autonumber IDs and Text

  • Thread starter Thread starter Debbie Nuding via AccessMonster.com
  • Start date Start date
D

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.
 
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?
 
Debbie said:
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?
Click to expand...

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 said:
Horses can have an owner
Click to expand...

In my limited experience, a horse may have more than one owner.
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.
Click to expand...

You only need to record the OwnerRiderID to record the owner. Perhaps
this is what you meant and made a typo i.e. should have said, "included
the OwnerRiderID in a field named Owner". In which case, to get the
name of an Owner would involve creating a JOIN between Horses and
OwnersRiders on Horses.Owner = OwnersRiders.OwnerRiderID. Shame the
element names are not consistent between these two tables ...

Actually, I find this design slightly confusing (aside from
OwnersRiders suggesting a table modelling a one to one relationship). I
guess in your model an OwnerRider only becomes an owner when they
appear in the Owner column in Horses. There may be a flaw here e.g. a
non-rider is entered into OwnersRiders so that they can be associated
with Horses.Owner for a single row because they own a horse; if they
cease to become the owner of that horse and they are not removed from
OwnersRiders they will by default have become a rider simply because
they are no longer an owner.

P.S. I'm left wondering whether a victory for a certain celebrity would
be recorded as: win, OwnerRider = Winona Rider (eat you heart our, John
Vinson <g>).

Jamie.

--
 
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
 
Debbie said:
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.
Click to expand...

You should not store values redundantly (is this what a lookup does? I
have no idea); instead, have only one place for everything. Joining
tables via queries to get the 'text' that goes with the 'number'. You
have chosen to use autonumber candidate/artificial keys so these joins
should be easy and efficient. You can create a VIEW of such a queries
that is 'logically' equivalent to a table e.g. may be subsequently
queried as if it were a table.

Here's some ideas for an alternative schema:

CREATE TABLE Horses (
HorseID INTEGER IDENTITY(1,1)
NOT NULL PRIMARY KEY,
HorseName VARCHAR(200) NOT NULL
)
;

What do riders and owners have in common? You may, now or later, want
to model trainers, breeders, agents, etc. So how about a Persons table
for their common attributes:

CREATE TABLE Persons (
PersonID INTEGER IDENTITY(1,1)
NOT NULL PRIMARY KEY,
FirstName VARCHAR(35) NOT NULL,
LastName VARCHAR(35) NOT NULL
)
;

Owners could be modelled as a relationship between a horse and a
person. The pairings will provide the primary key. If your business
rule is that a horse can only have one owner, then HorseID must have a
unique constraint (otherwise remove it). When a person ceases to become
an owner their association is removed from the Owners table and their
details will remain in Persons (without making them look like a rider).
You may want other columns e.g. ownership_start_date,
ownership_end_date, so that the rows are never removed, like a history
table (you'd need to make ownership_start_date part of the primary key
to model a owner buying a horse she previously already owned):

CREATE TABLE Owners (
HorseID INTEGER NOT NULL
UNIQUE
REFERENCES Horses (HorseID)
ON UPDATE CASCADE
ON DELETE CASCADE,
PersonID INTEGER NOT NULL
REFERENCES Persons (PersonID)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (PersonID, HorseID)
)
;

I won't attempt an Events table because I can't guess what an event is
(something that's sounds vague to me probably means something very
specific in the model) but I bet it has a column named EventID <g>.

I assume 'riders' can be defined as a person who rides a horse in an
event:

CREATE TABLE Rides (
EventID INTEGER NOT NULL
REFERENCES Events (EventID)
ON UPDATE CASCADE
ON DELETE CASCADE,
HorseID INTEGER NOT NULL
REFERENCES Horses (HorseID)
ON UPDATE CASCADE
ON DELETE CASCADE,
PersonID INTEGER NOT NULL
REFERENCES Persons (PersonID)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (PersonID, HorseID, EventID)
)
;

This isn't a complete solution, of course, but I hope it will give you
some ideas.

Jamie.

--
 
Back
Top