Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 11-26-2007, 06:37 PM   #1
timmeahy
Registered User
 
Join Date: Nov 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
timmeahy is on a distinguished road
Question Beginners question - Auto update fields based on fields in another table

I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.

I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.

The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".

Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
Code:
DEVICES:
Name		Description	Asset	IP
Xserve		File Server	107	203.30.144.75
ProliantX	DHCP		119	203.30.144.15
Code:
IP:
Address			IP Type		Device
203.30.144.75		Static		
203.30.144.15		Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".

I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.

thank you
-Tim

timmeahy is offline   Reply With Quote
Old 11-26-2007, 06:48 PM   #2
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,091
Thanks: 6
Thanked 59 Times in 56 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Tim,

The real answer is that you don't need the information in both tables.
In fact, you really DO NOT WANT the information in both tables.

A query can retrieve all info at any time:

Code:
Select Devices.Name, Devices.Description, Devices.Asset, Devices.IP, IP.Type
From   Devices Inner Join IP On Devices.IP = IP.Address
Having multiple copies of the same information allows you to introduce errors
into your data.

hth,
Wayne
WayneRyan is offline   Reply With Quote
Old 11-26-2007, 06:49 PM   #3
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Quote:
Originally Posted by timmeahy View Post
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.

I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.

The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".

Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
Code:
DEVICES:
Name		Description	Asset	IP
Xserve		File Server	107	203.30.144.75
ProliantX	DHCP		119	203.30.144.15
Code:
IP:
Address			IP Type		Device
203.30.144.75		Static		
203.30.144.15		Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".

I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.

thank you
-Tim
Believe me, I am trying to go easy on you, but I want you to know that you really, really appear to be doing something totally unnecessary. You should only store a value ONCE, not in multiple tables. I think you are possibly looking to create a table that is not necessary and that the information can be pulled via a query but you aren't sure how to do it and making a table in the format you want to see the data in is easier for you (even though it is really incorrect). Is that correct?

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
auto populate field based on other table rashidzaib Forms 3 10-07-2007 10:16 PM
Update table fields from another table! voskouee Tables 5 03-02-2007 05:04 AM
Copying data in one table to another-how to? jjh Tables 1 01-12-2007 11:25 PM
Auto Update Form jballotti Forms 1 11-13-2006 12:29 AM




All times are GMT -8. The time now is 06:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World