Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / May 2005

Tip: Looking for answers? Try searching our database.

Updating fileds from another table

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
AVN BDE - 15 May 2005 08:46 GMT
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
tina - 15 May 2005 09:44 GMT
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

> Greetings all,
>   I have a table in Access 2003 called tblComputerInfo.  The fields are as
[quoted text clipped - 28 lines]
>
> Wally Steadman
Walter Steadman - 15 May 2005 18:09 GMT
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

> 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
[quoted text clipped - 81 lines]
>>
>> Wally Steadman
tina - 16 May 2005 01:45 GMT
anything to help out a serviceperson, Wally!  :)

> Tina,
>     Thanks so much for the information, it will help greatly, will put it
[quoted text clipped - 87 lines]
> >>
> >> Wally Steadman
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.