IP Address Directory

[neo651]

I work in IT at a small medical company and I manage the database for all of our devices, specifically things like computers, multifunction printers, switches, access points, etc. All of these things have IP addresses and I would like to create a query that shows all devices that have an IP address assigned that I can use like an IP directory. I'm relatively new to Access so I'm stumped on how I could do this. Any ideas?

[orange]

How do you manage these devices now?
How do they get assigned IP addresses?

[ajetrumpet]

How do you manage these devices now?
How do they get assigned IP addresses?

that doesn't seem relevant, If I'm reading the question right.

without seeing the data, no one can answer this quickly. I'm assuming you have a list of devices that you sell, listed in a table and you want to query the IP field or something??

[neo651]

In total we have 16 different types of devices and most of them have their own management system.

Their IPs are assigned when we assign them manually.

What I have are 16 different tables of different IP devices. I want a single query that shows me all the IP addresses that are currently in use.

[orange]

Do the 16 tables have similar structure?
Like deviceId,DeviceName etc that sort of thing?

If so you might want to do some searching on UNION queries.

[neo651]

Each table mostly has fields unique to the device but every table has 2 fields in common: DeviceName and IP.

[orange]

So, if I understand you correctly, you really want to know which IP addresses are in use.
So a query that orders the result by IP address may be most handy.

If your IP address assignments are not grouped by Devicetype, you might want to include a field to identify which Table the IPAddress is in (possibly for further analysis). I was thinking that some devices in the same table may have very different IP addr(eg 231.43.45.6 and another 41.21.4.67 etc so you might want to list a table identifier in the query).
If so, post back and I'll adjust the sample.

A UNION query will work and is formatted along these lines. I am using your info that the DeviceName and IPAddr fields are common to all tables.

SELECT IP, DeviceName FROM Table1
UNION
SELECT IP, DeviceName FROM LargeDevices
UNION
SELECT IP, DeviceName FROM SmallDevices
etc. etc.for all tables
UNION
SELECT IP, DeviceName FROM OtherDevices
ORDER BY IP

[neo651]

Yes! Thank you Orange. That worked like a charm. I also added a WHERE statement to exclude devices that don't have assigned IPs.

You're right though, I'm going to need a field to show what table the IP address is in. Is there a way to add that just to the query or do I have to add a field to each table and bring it into the query?

[orange]

Yes! Thank you Orange. That worked like a charm. I also added a WHERE statement to exclude devices that don't have assigned IPs.

You're right though, I'm going to need a field to show what table the IP address is in. Is there a way to add that just to the query or do I have to add a field to each table and bring it into the query?

Yes. Using my previous example, with the made up table names you could do this.

SELECT IP, DeviceName ,"Table1" as DevTable FROM Table1
UNION
SELECT IP, DeviceName, "Largedevices" as DevTable FROM LargeDevices
UNION
SELECT IP, DeviceName, "Smalldevices" as DevTable FROM SmallDevices
etc. etc.for all tables
UNION
SELECT IP, DeviceName, "OtherDevices" as DevTable FROM OtherDevices
ORDER BY IP

I think that will work.

Good luck.