Crosstab query problem

[Bongard]

Hi I have a crosstab query that only has two records. It is a very
simple query with a sum field that is working for one of the records
but not the other. Here is my SQL

TRANSFORM Sum([99_02_Report - EIP Asset Value Summary].Net_Assets) AS
SumOfNet_Assets
SELECT [99_02_Report - EIP Asset Value Summary].Date, [99_02_Report -
EIP Asset Value Summary].ProductGroupID, Sum([99_02_Report - EIP Asset
Value Summary].Net_Assets) AS [Total Of Net_Assets]
FROM [99_02_Report - EIP Asset Value Summary]
GROUP BY [99_02_Report - EIP Asset Value Summary].Date, [99_02_Report
- EIP Asset Value Summary].ProductGroupID
PIVOT [99_02_Report - EIP Asset Value Summary].AssetType In
("Aggregate_Cash","Aggregate_MV","EQ_MV","EQ_Cash","RE_Cash","RE_MV","FI_Cash","FI_MV","BW_Cash","BW_MV");


One record is summing correctly in the field [Total Of Net_Assets] and
the other is taking the total of only [BW_Cash] and [BW_MV]... Not
sure why this is happening and any help you could offer would be much
appreciated as this needs to work in the morning!

Thanks much,
Brian

 

[KARL DEWEY]

One record is summing correctly in the field [Total Of Net_Assets] and the
other is taking the total of only [BW_Cash] and [BW_MV]...
I am confused with your phrasing.
"BW_Cash" and "BW_MV" are terms stored in field AssetType. You can not
total these terms.

Try removing the 'In' function of the PIVOT statement.

 

[Bongard]

That is still giving me the same result actully. My new SQL is this:-

TRANSFORM Sum([99_02_Report - EIP Asset Value Summary].Net_Assets) AS
SumOfNet_Assets
SELECT [99_02_Report - EIP Asset Value Summary].Date, [99_02_Report -
EIP Asset Value Summary].ProductGroupID, Sum([99_02_Report - EIP Asset
Value Summary].Net_Assets) AS [Total Of Net_Assets]
FROM [99_02_Report - EIP Asset Value Summary]
GROUP BY [99_02_Report - EIP Asset Value Summary].Date, [99_02_Report
- EIP Asset Value Summary].ProductGroupID
PIVOT [99_02_Report - EIP Asset Value Summary].AssetType


Still giving me the same result. My total field is still equals the
sum of those two fields.

Any other ideas?

Thanks,
Brian

 

[KARL DEWEY]

My total field is still equals the sum of those two fields.
"BW_Cash" and "BW_MV" are not fields according to your SQL.

You say you have two records. Post the data from those two records. Open
the table, click on the upper left corner, copy, and paste in a post.

 

[Bongard]

Date ProductGroupID Total Of Net_Assets Aggregate_Cash Aggregate_MV
BW_Cash BW_MV EQ_Cash EQ_MV FI_Cash FI_MV RE_Cash RE_MV
4/30/2008 1844 64570272.1002 50155.4401 32234980.61 -132759.19 5034700
184838.9001 22126016 13260.54 2241144.55 -15184.81 2833120.06
4/30/2008 1885 2 -4170991.5 4170991.5 1 1 -3454895 3454895 -344754
344754 -371342.5 371342.5

This is the data I get when I run the SQL


TRANSFORM Sum([99_02_Report - EIP Asset Value Summary].Net_Assets) AS
SumOfNet_Assets
SELECT [99_02_Report - EIP Asset Value Summary].Date, [99_02_Report -
EIP Asset Value Summary].ProductGroupID, Sum([99_02_Report - EIP Asset
Value Summary].Net_Assets) AS [Total Of Net_Assets]
FROM [99_02_Report - EIP Asset Value Summary]
GROUP BY [99_02_Report - EIP Asset Value Summary].Date, [99_02_Report
- EIP Asset Value Summary].ProductGroupID
PIVOT [99_02_Report - EIP Asset Value Summary].AssetType

Hopefully you can decipher that little paste. If a table in an email
would be easier I could do that easily as well.
Thanks I really appreciate your help,
Brian

 

[KARL DEWEY]

What you posted is your query results. You said you had TWO RECORDS. Your
posting is not records.

I do not see anything wrong with the results.
-4170991.5 50155.4401
4170991.5 32234980.61
1 -132759.19
1 5034700
-3454895 184838.9001
3454895 22126016
-344754 13260.54
344754 2241144.55
-371342.5 -15184.81
371342.5 2833120.06

2 64570272.1

 

[Bongard]

I guess I'm not sure what you're asking for. I ran the query, clicked
in the top left hand corner and pasted the records. What are you
looking for?

Thanks,
Brian