merging two tables and matching only Badge numbers

  • Thread starter Thread starter tpeter
  • Start date Start date
T

tpeter

I have an internal record that I am trying to find the matches based on badge
number to a customer file. Somewhere over the years a change has been made
where the customer puts additional numbers in the badge file as a check. I
have made a query to only include the records that match the badge numbers.
The results are 4972 records match out of 12,324, here is the SQL statement:

SELECT tblAMCODataCompile.Customer_Badge, Excel_File.[Badge Nbr],
Excel_File.[Model Cd], Excel_File.[Receive Dt], Excel_File.City,
Excel_File.[Intest Rslt], Excel_File.[Intest Dt], Excel_File.[Remove Dt],
Excel_File.[Prior Set Dt], Excel_File.[Set Dt], Excel_File.[Data Source],
tblAMCODataCompile.Open, tblAMCODataCompile.Check,
tblAMCODataCompile.Date_Recieved
FROM Excel_File INNER JOIN tblAMCODataCompile ON Excel_File.[Badge Nbr] =
tblAMCODataCompile.Customer_Badge;

I now need it to give me the same information but look at [Badge Nbr] and
only use the first 7 numbers left to right. I tried using left([Badge Nbr],7)
but it uses it as an and statment. Is there a way to rewrite the SQL to say
find everything that matches exactly or anything that matches the first 7
numbers?

Thank you for your help.

Tim Peter
 
Change the join clause to match on the first 7 characters of the fields.

SELECT tblAMCODataCompile.Customer_Badge, Excel_File.[Badge Nbr],
Excel_File.[Model Cd], Excel_File.[Receive Dt], Excel_File.City,
Excel_File.[Intest Rslt], Excel_File.[Intest Dt], Excel_File.[Remove Dt],
Excel_File.[Prior Set Dt], Excel_File.[Set Dt], Excel_File.[Data Source],
tblAMCODataCompile.Open, tblAMCODataCompile.Check,
tblAMCODataCompile.Date_Recieved
FROM Excel_File INNER JOIN tblAMCODataCompile
ON Left(Excel_File.[Badge Nbr],7) =
Left(tblAMCODataCompile.Customer_Badge,7);

That will show you exact matches as well as any that match on the first seven
characters - exact matches are going to match on the first seven characters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John,

I am at home now but will put the new statement in tomarrow. Thank you for
your assistance, it is greatly appreciated.

Tim Peter

John Spencer said:
Change the join clause to match on the first 7 characters of the fields.

SELECT tblAMCODataCompile.Customer_Badge, Excel_File.[Badge Nbr],
Excel_File.[Model Cd], Excel_File.[Receive Dt], Excel_File.City,
Excel_File.[Intest Rslt], Excel_File.[Intest Dt], Excel_File.[Remove Dt],
Excel_File.[Prior Set Dt], Excel_File.[Set Dt], Excel_File.[Data Source],
tblAMCODataCompile.Open, tblAMCODataCompile.Check,
tblAMCODataCompile.Date_Recieved
FROM Excel_File INNER JOIN tblAMCODataCompile
ON Left(Excel_File.[Badge Nbr],7) =
Left(tblAMCODataCompile.Customer_Badge,7);

That will show you exact matches as well as any that match on the first seven
characters - exact matches are going to match on the first seven characters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have an internal record that I am trying to find the matches based on badge
number to a customer file. Somewhere over the years a change has been made
where the customer puts additional numbers in the badge file as a check. I
have made a query to only include the records that match the badge numbers.
The results are 4972 records match out of 12,324, here is the SQL statement:

SELECT tblAMCODataCompile.Customer_Badge, Excel_File.[Badge Nbr],
Excel_File.[Model Cd], Excel_File.[Receive Dt], Excel_File.City,
Excel_File.[Intest Rslt], Excel_File.[Intest Dt], Excel_File.[Remove Dt],
Excel_File.[Prior Set Dt], Excel_File.[Set Dt], Excel_File.[Data Source],
tblAMCODataCompile.Open, tblAMCODataCompile.Check,
tblAMCODataCompile.Date_Recieved
FROM Excel_File INNER JOIN tblAMCODataCompile ON Excel_File.[Badge Nbr] =
tblAMCODataCompile.Customer_Badge;

I now need it to give me the same information but look at [Badge Nbr] and
only use the first 7 numbers left to right. I tried using left([Badge Nbr],7)
but it uses it as an and statment. Is there a way to rewrite the SQL to say
find everything that matches exactly or anything that matches the first 7
numbers?

Thank you for your help.

Tim Peter
Click to expand...
.
Click to expand...
 
Back
Top