UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need To Do An Ambiguous Lookup, Access 2013    
 
   
phonegeek
post Apr 20 2017, 08:34 PM
Post#1



Posts: 22
Joined: 22-October 10



Hello everyone. Hope you are doing well. I am dealing with IP addresses in a database and I'm stuck with a task that involves two tables that do not have a relationship, so I'm not sure this is possible.

I have VBA that converts IP address into their numeric values, and for qrySubnet I turned Subnet into [Start_Number] & Mask into [End Number] so I will avoid the networking lingo and talk in real numbers here.

tblServer has ServerName, IP Addresses
qrySubnet has SubnetName, Start_Number, End_Number, Location
There is no relational connection.

I need to compare each address in tblServer to every range in qrySubnet.
When the query finds the range the IP address fits into, then it will return the name of that IP Range.


Example:
tblServer ([Name], [IP_Address]):
ServerABC, 3232236033
ServerXYZ, 168116485

qrySubnet ([SubnetName], [Start_Number], [End_Number]):
SubnetA, 3232235776, 3232236287
SubnetB, 168116224, 168116735
SubnetC, 2886077440, 2886078464

So the query would take ServerABC and run number 3232236033 against all three rows in qrySubnet

IIF([IP_Address]>Tbl2.[Start_Number] AND [IP_Address]<Tbl2.[End_Number],[SubnetName],"NoMatch")

Result:
ServerABC, 3232236033, SubnetA

How do I make this happen when there is no relationship between the two sources?

I thought about using ELookUp but the lack of relationship has me stumped.

Thanks.


Go to the top of the page
 
projecttoday
post Apr 20 2017, 08:58 PM
Post#2


UtterAccess VIP
Posts: 8,182
Joined: 10-February 04
From: South Charleston, WV


No problem. Just create a join using that expression.

--------------------
Robert Crouser
Go to the top of the page
 
phonegeek
post Apr 20 2017, 09:50 PM
Post#3



Posts: 22
Joined: 22-October 10



Really? I'm stuck on the idea of lines connecting two sources and selecting a join option.

How do I create a join using that expression?
Go to the top of the page
 
projecttoday
post Apr 20 2017, 09:56 PM
Post#4


UtterAccess VIP
Posts: 8,182
Joined: 10-February 04
From: South Charleston, WV


SELECT qrySubnet.Subnetname, tblServer.[IP Address] FROM qrySubnet INNER JOIN tblServer ON tblServer.[IP_Address] > qrySubnet.[Start_Number] AND tblServer.[IP_Address] < qrySubnet.[End_Number]

although it seems to me you would want >= and <=.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 09:58 PM
Post#5


UA Admin
Posts: 29,753
Joined: 20-June 02
From: Newcastle, WA


Keep in mind that there is a difference between what we usually mean when we casually refer to "relationships" and what we mean by "joins" in a query.

In fact, what we often mean is that one table has a foreign key restraint imposed on a particular field. Let us say, for example, that you have a table of Servers, uniquely identified by some field which has been identified as the Primary Key for that table. In a second table, which has some relationship to the server table, there will be a field which contains the same values as those in the Primary Key field in the server table. To formalize that relationship, we can apply a "Foreign Key Restraint" on it. That means no value can be inserted into the Foreign Key field unless it previously exists in the server table. That's probably what you are thinking about.

However, joins in a query don't need to be made on primary and foreign keys. You can create joins on any fields which have a common datatype. It may or may not make sense to do that, of course. In this case, you are interested in joining on the IP with a corresponding range of subnet values. This would require a non-equijoin, like this : tblServer.IPAddress between qrySubnet.Start_Number AND qrySubnet.End_Number

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th June 2017 - 08:47 PM