If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autonumber IDs and Text
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. -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
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. -- Duane Hookom MS Access MVP -- "Debbie Nuding via AccessMonster.com" wrote in message news:8497d20b8e28462489cdc350c4f711f5@AccessMonste r.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. -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
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? -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Debbie Nuding via AccessMonster.com wrote:
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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#5
|
|||
|
|||
Debbie Nuding via AccessMonster.com wrote: Horses can have an owner 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. 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. -- |
#6
|
|||
|
|||
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 -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Debbie Nuding via AccessMonster.com wrote:
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. 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. -- |
Thread Tools | |
Display Modes | |
|
|