Advanced access query/macro for network database

[markpember1982]

Hi,

Apologies if this is the wrong place to post. I'm trying to design a database for my network. See link for design so far

http://homepage.ntlworld.com/daniel.bhatoa/project/relationships.jpg

I've done a simple query which will show two directly connected devices (say a switch to a switch).

SELECT media.Section_id, media.From_Pair, media.To_Pair, media.FromIp, media.ToIp
FROM media
WHERE (((media.FromIp)=[Please Enter IP])) OR (((media.ToIp)=[Please enter ip]));


What i would like to do is to also include network element name in the toip and fromip fields, as well as more information from the section table, although I'm not sure how to go about.

Would I need to write an access module to store the ip input to a variable, run 3 different queries and display them on the same page?

Any info much appreciated.

Thanks

 

[PHV]

A starting point:
SELECT m.Section_id, s.Media_type, m.From_Pair, m.To_Pair, m.FromIp, f.Name, m.ToIp, t.Name
FROM ((media AS m
INNER JOIN Network_Element AS f ON m.FromIp = f.IP)
INNER JOIN Network_Element AS t ON m.ToIp = t.IP)
INNER JOIN Section AS s ON m.Section_id = s.Section_id
WHERE [Please Enter IP] IN (m.FromIp,m.ToIp);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886