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
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

705 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