zimmer9
asked on
How would you rewrite a query to yield 2 records which are the only 2 values for one of the fields selected ?
I am developing an Access application using an ADP file with Access as the front end and SQL Server as the back end database.
I use the following query to extract records from a table and insert the result set into another table.
Do you know how I could rewrtie this query to yield ONLY 2 records based on the field [Legal Entity} ?
The field [Legal Entity] has only 2 distinct values.
For example: [Legal Entity] will have a value of either "Class E" or "Class O".
INSERT INTO ztbl_Source_Funds_Ungroupe d_SSFIII (masterID, Fund, reportClientName, originalCommitment,
Redemptions, Reinvestments, Nav, Market_app_depre, Distributions, sourceid, reportDt, sourceFund, [Gross_Sales],
[Investor Name], GP, B.[Legal Entity], ExtractType)
SELECT A.id AS masterID, 'Special Situations Fund III','MS Employees' As reportClientName,
SUM(ISNULL(A.[original commitment],0)) As [Original Commitment],
SUM(ISNULL(B.Redemptions,0 )) AS Redemptions,
0.0, 0.0, 0.0,
SUM(ISNULL(B.[Income Distributions],0)) + SUM(ISNULL(b.[Capital Distributions],0)) As Distributions,
[Account Name],
Max(filedate) As [Reporting Date],
'SSFIII' AS fundsource,
SUM(ISNULL(B.Contributions ,0)) AS Contributions,
B.[Investor Name], B.GP, B.[Legal Entity], 'CLASSEO'
FROM ztbl_Master_Template A
INNER JOIN ztbl_Source_SSFIII B ON
B.[Investor Name] = A.[Account Name] AND
A.[Legal Entity] LIKE '%' + B.[Legal Entity] + '%'
AND B.GP NOT IN('MS GP','MS LP') AND (B.[Legal Entity] LIKE '%CLASS E%' OR B.[Legal Entity] LIKE '%CLASS O%')
GROUP BY B.[Legal Entity],B.[Investor Name], A.id, A.[Fund Name], A.[Account Name],GP
The output from this query could be used as input into another table, if that is easier.
I use the following query to extract records from a table and insert the result set into another table.
Do you know how I could rewrtie this query to yield ONLY 2 records based on the field [Legal Entity} ?
The field [Legal Entity] has only 2 distinct values.
For example: [Legal Entity] will have a value of either "Class E" or "Class O".
INSERT INTO ztbl_Source_Funds_Ungroupe
Redemptions, Reinvestments, Nav, Market_app_depre, Distributions, sourceid, reportDt, sourceFund, [Gross_Sales],
[Investor Name], GP, B.[Legal Entity], ExtractType)
SELECT A.id AS masterID, 'Special Situations Fund III','MS Employees' As reportClientName,
SUM(ISNULL(A.[original commitment],0)) As [Original Commitment],
SUM(ISNULL(B.Redemptions,0
0.0, 0.0, 0.0,
SUM(ISNULL(B.[Income Distributions],0)) + SUM(ISNULL(b.[Capital Distributions],0)) As Distributions,
[Account Name],
Max(filedate) As [Reporting Date],
'SSFIII' AS fundsource,
SUM(ISNULL(B.Contributions
B.[Investor Name], B.GP, B.[Legal Entity], 'CLASSEO'
FROM ztbl_Master_Template A
INNER JOIN ztbl_Source_SSFIII B ON
B.[Investor Name] = A.[Account Name] AND
A.[Legal Entity] LIKE '%' + B.[Legal Entity] + '%'
AND B.GP NOT IN('MS GP','MS LP') AND (B.[Legal Entity] LIKE '%CLASS E%' OR B.[Legal Entity] LIKE '%CLASS O%')
GROUP BY B.[Legal Entity],B.[Investor Name], A.id, A.[Fund Name], A.[Account Name],GP
The output from this query could be used as input into another table, if that is easier.
Please can you give us a sample of the data in your tables, and state what output you want from your query for that data.
ASKER
Great idea. That's the way I like to work. Give me some time. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.