?
Solved

How would you rewrite a query to yield 2 records which are the only 2 values for one of the fields selected ?

Posted on 2008-09-29
3
Medium Priority
?
262 Views
Last Modified: 2013-12-05
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_Ungrouped_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.
0
Comment
Question by:zimmer9
3 Comments
 
LVL 10

Expert Comment

by:calpurnia
ID: 22601683
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.
0
 

Author Comment

by:zimmer9
ID: 22601690
Great idea. That's the way I like to work. Give me some time. Thanks.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 2000 total points
ID: 22605041
if you want only 2 records based on field "legal entity", then "legal entity" must be the only GROUP BY field in your quesry; all others have to be aggregates. That is, now you have

select ...
GROUP BY B.[Legal Entity],B.[Investor Name], A.id, A.[Fund Name], A.[Account Name],GP

it should become

GROUP BY B.[Legal Entity]

of course you have to decide what to do in case

legal entity           investor name
class e                 john
class e                 bob

i.e. how to aggregate investor name into one record. The same also applies to  A.id, A.[Fund Name], A.[Account Name],GP
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 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