?
Solved

Newbie SQL Question

Posted on 1998-08-21
5
Medium Priority
?
199 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
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 400 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

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…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

621 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