Solved

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

Posted on 2013-01-30
1
279 Views
Last Modified: 2013-01-30
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
0
Comment
Question by:zimmer9
1 Comment
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 38836963
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question