Beginners question - Auto update fields based on fields in another table (1 Viewer)

timmeahy

New member
Local time
Tomorrow, 08:11
Joined
Nov 27, 2007
Messages
1
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:
[B]Name[/B]		[B]Description[/B]	[B]Asset[/B]	[B]IP[/B]
Xserve		File Server	107	203.30.144.75
ProliantX	DHCP		119	203.30.144.15

Code:
IP:
[B]Address[/B]			[B]IP Type[/B]		[B]Device[/B]
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
 

WayneRyan

AWF VIP
Local time
Today, 21:11
Joined
Nov 19, 2002
Messages
7,122
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
 

boblarson

Smeghead
Local time
Today, 14:11
Joined
Jan 12, 2001
Messages
32,059
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:
[B]Name[/B]		[B]Description[/B]	[B]Asset[/B]	[B]IP[/B]
Xserve		File Server	107	203.30.144.75
ProliantX	DHCP		119	203.30.144.15

Code:
IP:
[B]Address[/B]			[B]IP Type[/B]		[B]Device[/B]
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?
 

Users who are viewing this thread

Top Bottom