Formatting a Number(IP Address)

[Wally Steadman]

Greetings all,
I have building a DB to track IP addresses of machines in many different
builldings at many different locations. I have the following tables:

tblSite
SiteID - Autonumber (PK)
Site Name - Text

tblBldg
BldgID - Autonumber (PK)
SiteID - Number (FK related to tblSite SiteID. The many in a 1 - Many
relationship)
Subnet - Text (this is going to be a number in IP format like 10.1.1.x)

tblMachine - Autonumber (PK)
BldgID - Number (FK related to tblBldg BldgID. The many in a 1 - Many
relationship)
Machine IP - Text (this will be the full IP that is part of the subnet in
the tblBldg, so it will be the 10.1.1.1 address or the 10.1.1.224).

My questions are:

1. Does that look like a smart structure?
2. Is there a way for me to get the Machine IP field to populate with the
first part of the subnet like the 10.1.1. based on the Subnet field in the
tblBldg table. So when I go to add an IP, I don't have to type out the
entire number, I can just type out the 1 or the 224 or whatever the number
is between 0 and 255.

Sites are locations. If the Site goes away, all the buildings go away and
if buiildings go away that machine location will go away also so I was going
to use Referential Integrity. That is also why I put the Site, Bldg and
Machines in different tables.

TIA in advance for any assistance
Wally Steadman
ICQ - 11904383

 

[Guest]

Hi Wally,

Good thinking on the structure. It allows machines to be independant of
sites and buildings same as the IP addresses, since machines and IP addresses
can be shifted arround.

In so far as having a filed populated automatically, then yes it can. What
you need to do though is create an entry form. This form would have a drop
down box populated by records from tblSite, which in turn would then populate
another drop down box or list box, with the buildings relevant to that site
from tblbldg, then a third dropdown box or listbox would be populated by the
subnet addresses for that building. Selecting the relevant subnet address
would then form the basis of the machine address and a final text box would
allow you to enter in the final machine address. This text box and the
selected subnet address could then be concatenated to form the reord for the
machineIP. However why not simply save the subnet address from tblbldgs in
one field and then the final part of the machine address in anotehr field in
tblmachine. The two can be bought together in any report you want out of the
data. It is not necessary to save the machine IP address in full simply have
the two parts of the address saved in two fields.

Hope this helps

DavidC