How would you modify a SQL Statement for the addition of another table to join (to filter out records in Access 2003)?

In the following INSERT statement, how would I add the following qualifier:
where (C.Officenumber + C.CustomerNumber) = dbo.tblSpINTMatch.[Account Number]


----------------------------------------------------------------------------------------------------------
INSERT INTO dbo.tblSpINT([Account Number], [Address 1], [Address 2], [Address 3])
SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number], UPPER(C.Address1)
As [Address 1], UPPER(C.Address2) As [Address 2],  UPPER(C.Address3) As [Address 3]
FROM dbo.tblStatesAll  As S INNER JOIN (dbo.tblCustomersNew As C  
INNER JOIN dbo.tblProductsNew As P  
ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber)
ON S.StateFS = C.ResStateCode
zimmer9Asked:
Who is Participating?
 
Kyle AbrahamsSenior .Net DeveloperCommented:
2 Ways:

#1:

INSERT INTO dbo.tblSpINT([Account Number], [Address 1], [Address 2], [Address 3])
SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number], UPPER(C.Address1)
As [Address 1], UPPER(C.Address2) As [Address 2],  UPPER(C.Address3) As [Address 3]
FROM dbo.tblStatesAll  As S INNER JOIN (dbo.tblCustomersNew As C  
INNER JOIN dbo.tblProductsNew As P  
ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber)
ON S.StateFS = C.ResStateCode
inner join dbo.tblSpINTMatch M on
(C.Officenumber + C.CustomerNumber) = dbo.tblSpINTMatch.[Account Number]


OR


#2:

INSERT INTO dbo.tblSpINT([Account Number], [Address 1], [Address 2], [Address 3])
SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number], UPPER(C.Address1)
As [Address 1], UPPER(C.Address2) As [Address 2],  UPPER(C.Address3) As [Address 3]
FROM
dbo.tblSpINTMatch M,
dbo.tblStatesAll  As S INNER JOIN (dbo.tblCustomersNew As C  
INNER JOIN dbo.tblProductsNew As P  
ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber)
ON S.StateFS = C.ResStateCode
where (C.Officenumber + C.CustomerNumber) = M.[Account Number]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.