Updating fileds from another table

  • Thread starter Thread starter AVN BDE
  • Start date Start date
A

AVN BDE

Greetings all,
I have a table in Access 2003 called tblComputerInfo. The fields are as
follows:

ComputerName
ComputerSerNum
Owner
OwnerPhone
OperatingSystem
ServicePacks
DatePurchased

etc....

I also have a table built for trouble tickets. Some of the fields on the
trouble ticket table are:

ComputerName
ComputerSerNum
Owner
OwnerPhone

etc... I am trying to get the following results on the trouble ticket.
When I input the ComputerName I want access to automatically populate the
remaining three fields above with the information from the tblComputerInfo.
I have done this in the past but since I am in Iraq, I don't have access to
my other DB's to copy examples and for some reason I am lost. Any help in
doing this action would certainly be appreciated.

Thanks in Advance

Wally Steadman
 
your tables aren't normalized; duplicate data is a big no-no. i'll assume
that each computer has only one owner, but it seems safer to assume that one
owner could have many (more than one) computers. suggest the following
tables, as

tblOwners (or tblCustomers, perhaps?)
OwnerID (primary key)
OwnerFirstName
OwnerLastName
OwnerPhone
(if an owner may have more than one phone number, you need a separate table
for phone numbers, with OwnerID in it as a foreign key.)

tblComputers
ComputerSerNum (pk)
ComputerName
OwnerID (foreign key from tblOwners)
OperatingSystem
ServicePacks
DatePurchased
(note: if you need to list multiple service packs for one computer, then
you need a separate table for service packs, with ComputerSerNum as a
foreign key in it. ditto for operating system.)
(also note: serial number should be a unique value, so it makes a good
natural key. if you don't want to use it as primary key for some reason, add
another field with autonumber data type to serve as your primary key field;
you could call it ComputerID.)

tblTickets
TicketID (pk)
ComputerID (fk from tblComputers)
(other fields that describe a specific trouble ticket)

you don't enter the owner information into a ticket. each ticket record is
linked to a specific computer record, and each computer record is linked to
a specific owner - so you can display the owner info for a specific ticket
on a specific computer any time you need to. that's the whole point of a
relational database model: you enter each data element only once, and since
related data elements have a pk/fk link, you can pull them together in
multiple ways for whatever data entry/display purposes you need.

hth
 
Tina,
Thanks so much for the information, it will help greatly, will put it
all together and make it work. Great ideas, going to start fixing it now.

Wally Steadman
 
Back
Top