Join of IP Addresses not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matthew Tisdel - 10 Apr 2006 15:38 GMT

If this has been answered already, or is better answered somewhere else, then
please let me know.

I have done many joins in Access before - to databases in Access, on SQL
server and to Excel spreadsheets. However, I have hit a problem that I don't
understand. I have a DB in Access that has a table with many fields of
information about servers and network equipment. IP address is the Primary
Key. I am attempting join this table to another table that I have in Access
that includes MAC address and IP Address only - it also has IP address as the
Primary Key. When I join the tables on the IP Address field, I get no matches
at all. So, the Mac Address table looks like this:

10.1.1.1 00-04-23-a6-a0-de
10.1.1.2 00-c0-9f-7a-15-c4

The table is designed with both fields as text fields.

Thanks,

Signature

Matthew Tisdel
South Carolina

Reply to this Message

Allen Browne - 10 Apr 2006 16:14 GMT

Microsoft mutilated Access from version 2000 onwards so that it does not
match fields reliably if there is a dash in the field if you use the Like
operator.

More info:
Query Returns no Records with an Indexed Field That Contains Dashes
at:
http://support.microsoft.com/kb/271661/en-us

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> If this has been answered already, or is better answered somewhere else,
> then
[quoted text clipped - 19 lines]
>
> Thanks,

Reply to this Message

Matthew Tisdel - 10 Apr 2006 16:35 GMT

Thanks.

I am not actually attempting to match the fields that have the dashes in
them. The join is on the IP Address field that looks like 10.1.1.1 or
10.1.1.243 or 10.1.1.89, etc. Should this be working?

Signature

Matthew Tisdel
South Carolina

> Microsoft mutilated Access from version 2000 onwards so that it does not
> match fields reliably if there is a dash in the field if you use the Like
[quoted text clipped - 28 lines]
> >
> > Thanks,

Reply to this Message

Allen Browne - 10 Apr 2006 16:52 GMT

It should provided the fields are identical, i.e. no leading zeros or
spaces.

If you had control over the database structure, you might consider storing
IP addresses in 4 fields of type Byte.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks.
>
[quoted text clipped - 38 lines]
>> >
>> > Thanks,

Reply to this Message

Michel Walsh - 11 Apr 2006 12:54 GMT

Hi,

You want a match if the first three octets are equal?

SELECT a.*, b.*
FROM a INNER JOIN b
ON Left(a.ip, InStrRev( a.ip, ".")) = Left( b.ip, InStrRev( b.ip,
"." ))

Hoping it may help,
Vanderghast, Access MVP

> Thanks.
>
[quoted text clipped - 38 lines]
>> >
>> > Thanks,

Reply to this Message

Matthew Tisdel - 11 Apr 2006 13:31 GMT

Thanks for the help.

Actually, I need all 4 octets to match for the join. I have the IP addresses
in both tables, however, one of the tables have MAC Addresses and I need to
put those MAC Addresses in the other table.

Signature

Matthew Tisdel
South Carolina

> Hi,
>
[quoted text clipped - 50 lines]
> >> >
> >> > Thanks,

Reply to this Message

Matthew Tisdel - 11 Apr 2006 14:04 GMT

Thanks. I got it to work with the help of a developer friend.

The MAC Address table had the IP Addresses padded with space. I had to use

New Field Name : Trim([Field Name])

in the query.

Thanks,

Signature

Matthew Tisdel
South Carolina

> Thanks for the help.
>
[quoted text clipped - 56 lines]
> > >> >
> > >> > Thanks,

Reply to this Message