Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
287 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 41

Accepted Solution

by:
Kyle Abrahams earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

610 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