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.
Start Free Trial
