Matching mac addresses in a query??

[Jim Scheffler]

Hello,

I'm new to Access so forgive me if this is a simple thing. I'm working in
Access 2002 and with two tables. Each table contains hundreds of mac
addresses and I have to find all the macs that are in both tables. Here's
the catch, in table one the macs are entered as 00aa.00ab.abc0, in table
two the matching mac is entered as 00AA00ABABC0. Access won't match these
up. I've tried using format, <0AAA\.AAAA\.AAAA with no luck. The format
changes the way the mac is displayed but still won't match up in the query.
Both these tables a linked to text files as they are updated twice daily.

Thanks for any help,

Jim Scheffler

 

[John Nurick]

Hi Jim,

You need to create a calculated field in the query to insert the dots.
Access matching isn't case sensitive so you needn't worry about that.
This seems to do the job:

fMAC: Left(Mac,4) & "." & Mid(Mac,5,4) &"." & Right(Mac,4)

If you're new to SQL as well as to Access, create a simple Select query
that returns the fields you need from the "undotted" table plus the
calculated field as above. Then create a second query that joins this
query and the other table on the now-compatible MAC fields.

To do it in a single query, you need (I think) a subquery, along these
lines:

SELECT A.MAC, A.OtherField, B.OtherField [,...]
FROM DottedTable AS A
INNER JOIN (
SELECT Left(MAC,4) & "." & Mid(MAC,5,4) & "."
& Right(MAC,4) AS fMAC, Otherfield [,...]
FROM UndottedTable
) AS B
ON A.MAC = B.fMAC;

 

[Jim Scheffler]

John, thanks for the help on this.

I've been trying to get this to work but have not had any success. I think
I'm getting confused with the table names you used in the example.
The table names I'm using are as follows, table "CSGMWAB" contains the macs
without the dots, 00A0C0BB00DD. Table "Macs" contains the macs with the
dots, 00a0.c0bb.00d.
I did try the SQL query as well but could not get it to work.
Could you insert my table names in your examples so I can follow them a
little better?

Thanks,

Jim

Hi Jim,

You need to create a calculated field in the query to insert the dots.
Access matching isn't case sensitive so you needn't worry about that.
This seems to do the job:

fMAC: Left(Mac,4) & "." & Mid(Mac,5,4) &"." & Right(Mac,4)

If you're new to SQL as well as to Access, create a simple Select query
that returns the fields you need from the "undotted" table plus the
calculated field as above. Then create a second query that joins this
query and the other table on the now-compatible MAC fields.

To do it in a single query, you need (I think) a subquery, along these
lines:

SELECT A.MAC, A.OtherField, B.OtherField [,...]
FROM DottedTable AS A
INNER JOIN (
SELECT Left(MAC,4) & "." & Mid(MAC,5,4) & "."
& Right(MAC,4) AS fMAC, Otherfield [,...]
FROM UndottedTable
) AS B
ON A.MAC = B.fMAC;

Hello,

I'm new to Access so forgive me if this is a simple thing. I'm working in
Access 2002 and with two tables. Each table contains hundreds of mac
addresses and I have to find all the macs that are in both tables. Here's
the catch, in table one the macs are entered as 00aa.00ab.abc0, in table
two the matching mac is entered as 00AA00ABABC0. Access won't match these
up. I've tried using format, <0AAA\.AAAA\.AAAA with no luck. The format
changes the way the mac is displayed but still won't match up in the query.
Both these tables a linked to text files as they are updated twice daily.

Thanks for any help,

Jim Scheffler

 

[John Nurick]

Jim,

The first example, for a calculated field in a select query, doesn't
include the table name, but it assumes that the field that contains the
MAC is called "Mac".

What actually happens when you try this?

In the subquery example, replace DottedTable with Macs and UndottedTable
with CSGMWAB. Again, you'll have to adjust the field names.

John, thanks for the help on this.

I've been trying to get this to work but have not had any success. I think
I'm getting confused with the table names you used in the example.
The table names I'm using are as follows, table "CSGMWAB" contains the macs
without the dots, 00A0C0BB00DD. Table "Macs" contains the macs with the
dots, 00a0.c0bb.00d.
I did try the SQL query as well but could not get it to work.
Could you insert my table names in your examples so I can follow them a
little better?

Thanks,

Jim

Hi Jim,

You need to create a calculated field in the query to insert the dots.
Access matching isn't case sensitive so you needn't worry about that.
This seems to do the job:

fMAC: Left(Mac,4) & "." & Mid(Mac,5,4) &"." & Right(Mac,4)

If you're new to SQL as well as to Access, create a simple Select query
that returns the fields you need from the "undotted" table plus the
calculated field as above. Then create a second query that joins this
query and the other table on the now-compatible MAC fields.

To do it in a single query, you need (I think) a subquery, along these
lines:

SELECT A.MAC, A.OtherField, B.OtherField [,...]
FROM DottedTable AS A
INNER JOIN (
SELECT Left(MAC,4) & "." & Mid(MAC,5,4) & "."
& Right(MAC,4) AS fMAC, Otherfield [,...]
FROM UndottedTable
) AS B
ON A.MAC = B.fMAC;

Hello,

I'm new to Access so forgive me if this is a simple thing. I'm working in
Access 2002 and with two tables. Each table contains hundreds of mac
addresses and I have to find all the macs that are in both tables. Here's
the catch, in table one the macs are entered as 00aa.00ab.abc0, in table
two the matching mac is entered as 00AA00ABABC0. Access won't match these
up. I've tried using format, <0AAA\.AAAA\.AAAA with no luck. The format
changes the way the mac is displayed but still won't match up in the query.
Both these tables a linked to text files as they are updated twice daily.

Thanks for any help,

Jim Scheffler