Solved

Newbie SQL Question

Posted on 1998-08-21
5
183 Views
Last Modified: 2011-09-20
Greetings,
I have created an SP which summarizes finacial statement information. I have created three temp tables(one with Revenue, one with Expenses, one with Excluded items).

I now want to: Merge these three temp tables into one so I can have one record with expenses, revenue and excluded items. The keys which are common for all temp tables are pool number, loan number and statement date.

How do I join these three tables.

Thank you for your help...
Brad
0
Comment
Question by:bhummel
[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
5 Comments
 
LVL 2

Expert Comment

by:alokm
ID: 1430359
I would appreciate detailed table structure.
0
 
LVL 8

Expert Comment

by:MikeP090797
ID: 1430360
If it has the same structure, use INNER JOIN
0
 
LVL 3

Expert Comment

by:a111a111a111
ID: 1430361
bhummel,
Hi,

E-mail me the database even with 2 records and I'll send you the code.

shay@hili.com

0
 
LVL 2

Accepted Solution

by:
richtsteig earned 100 total points
ID: 1430362
Hey bhummel,

I try to give you an answer:

First you have to decide, whether you want to create your result table (A) or have the structure already defined (B)

(A) You have to use the SELECT INTO command
(B) Your have to use the INSERT INTO command

If your three source tables are ALL containing the keydata, you can use the INNER JOIN, if one table has no data, you should use the LEFT JOIN

Example

SELECT REV.<fld1>,.. REV.<fldn>,EXP.<fld1>,..EXP.<fldn>,XCL.<fld1>,.. XCL.<fldn>
    INTO <your new table>
   FROM (
             REVENUE AS REV
  INNER JOIN EXPENSES AS EXP
       ON REV.POOL = EXP.POOL AND REV.LOAN = EXP.LOAN
             )
 INNER JOIN EXCLUDE AS XCL
       ON REV.POOL = XCL.POOL AND REV.LOAN = XCL.LOAN
WHERE <your criteria>

Replace the "INNER" with "LEFT" if data in XCL or EXP is missing.

If your new structure is already defined the Statement would be:
INSERT INTO <your table>
SELECT REV.<fld1>,.. REV.<fldn>,EXP.<fld1>,..EXP.<fldn>,XCL.<fld1>,.. XCL.<fldn>
   FROM (
             REVENUE AS REV
  INNER JOIN EXPENSES AS EXP
       ON REV.POOL = EXP.POOL AND REV.LOAN = EXP.LOAN
             )
 INNER JOIN EXCLUDE AS XCL
       ON REV.POOL = XCL.POOL AND REV.LOAN = XCL.LOAN
WHERE <your criteria>

If you don't want to transfer all of the fields, you have to name
the destination fileds after the INSERT INTO
INSERT INTO <your table> (fldA, fldB, ....)

Hope this helps
0
 

Author Comment

by:bhummel
ID: 1430363
richtstieg,
I used your code and it combined the rows. Thanks for your help.

Brad
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

733 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