Solved

Newbie SQL Question

Posted on 1998-08-21
5
180 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 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add a task in Outlook from access 11 39
How to compare ms sql hashbytes results within vb6 5 75
how to know if my Checkbox is True in VB6.0? 9 55
MsgBox 4 61
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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 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…

777 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