adding a row with empty results in a query

G

Guest

Hi there,
I don't know if this is best added in reports or trhrough a query but I've
limited my query below to look for values TRV, USCPA and MMF and calculate
percentage allocations for each subtotal using Dsum. There is another type,
SLN which needs to be ignored completely, and I need to roll anything else up
into an "Other"category and I'm having trouble:
1) creating this other category (would this have to be through a union
query?);
2) this Other category needs to show up even if there are no values (see
example below)


Trade TypeID|Total Balance|Portforlio %|Result|
MMF|$2,060,000,000 |0.2341|PASS|
TRV|$651,000,000 |.07401 | PASS|
USCPA|$6,085,000,000 |0.6917| PASS|

OTHER|$0|0.0|PASS <-----i need this to be able to show up in the query or
report, even if empty-- it might include a rollup of

SPV|$250,000
STR|$600,000
VVR|$340,000

Any help would be greatly appreciated.
Frank
 
G

Gary Walter

Frank said:
I don't know if this is best added in reports or trhrough a query but I've
limited my query below to look for values TRV, USCPA and MMF and calculate
percentage allocations for each subtotal using Dsum. There is another
type,
SLN which needs to be ignored completely, and I need to roll anything else
up
into an "Other"category and I'm having trouble:
1) creating this other category (would this have to be through a union
query?);
2) this Other category needs to show up even if there are no values (see
example below)


Trade TypeID|Total Balance|Portforlio %|Result|
MMF|$2,060,000,000 |0.2341|PASS|
TRV|$651,000,000 |.07401 | PASS|
USCPA|$6,085,000,000 |0.6917| PASS|

OTHER|$0|0.0|PASS <-----i need this to be able to show up in the query
or
report, even if empty-- it might include a rollup of

SPV|$250,000
STR|$600,000
VVR|$340,000
Click to expand...
Hi Frank,

Depending on your data...

I'd probably just create a table ("tblGroup")
with 2 fields

Trade TradeGrp
------ ---------
MMF MMF
TRV TRV
USCPA USCPA
SLN IGNORE
SPV OTHER
STR OTHER
VVR OTHER

In original query, just include
tblGroup to get a "TradeGrp"
for each record,

then when you get your aggregates,
group on TradeGrp
WHERE TradeGrp <> 'IGNORE'
 
G

Guest

Gary,
Thanks for the response. That is actually a better way to do it than to run
a separate query and then do a union.
My only problem still remains: how to show the result even when there's no
values in it.

ie: OTHER|$0|0.0|PASS .... as the last row
 
G

Guest

Here is my SQL code -- I think the problem might be that I am trying to
create everything in one query. I think I need a group by statement that
includes OTHER, but I can't because the OTHER subcategory is created in this
query. Does this sound right.

SELECT
IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID ]="SLN","IGNORE","OTHER"))) AS Type,
Sum([Asset and SLN Data].[Orig Notional ]) AS [SumOfOrig Notional],
Sum([Asset and SLN Data].[Orig Notional]/DSum("[Orig Notional]","Asset and
SLN Data","[Trade TypeID ] <> 'SLN'")) AS [Portfolio %],
IIf([Type]="OTHER" And [SumOfOrig Notional]>0,"FAIL","PASS") AS Result
FROM
[Asset and SLN Data]

GROUP BY
IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID ]="SLN","IGNORE","OTHER")))

HAVING
(((IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID
]="SLN","IGNORE","OTHER"))))<>"IGNORE"))
ORDER BY Sum([Asset and SLN Data].[Orig Notional ]) DESC;
 
G

Gary Walter

Frank said:
Here is my SQL code -- I think the problem might be that I am trying to
create everything in one query. I think I need a group by statement that
includes OTHER, but I can't because the OTHER subcategory is created in
this
query. Does this sound right.

SELECT
IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID ]="SLN","IGNORE","OTHER"))) AS
Type,
Sum([Asset and SLN Data].[Orig Notional ]) AS [SumOfOrig Notional],
Sum([Asset and SLN Data].[Orig Notional]/DSum("[Orig Notional]","Asset and
SLN Data","[Trade TypeID ] <> 'SLN'")) AS [Portfolio %],
IIf([Type]="OTHER" And [SumOfOrig Notional]>0,"FAIL","PASS") AS Result
FROM
[Asset and SLN Data]

GROUP BY
IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID ]="SLN","IGNORE","OTHER")))

HAVING
(((IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID
]="SLN","IGNORE","OTHER"))))<>"IGNORE"))
ORDER BY Sum([Asset and SLN Data].[Orig Notional ]) DESC;
Click to expand...

Hi Frank,

Did you create a table ("tblGroup")
with 2 fields

Trade TradeGrp
------ ---------
MMF MMF
TRV TRV
USCPA USCPA
SLN IGNORE
SPV OTHER
STR OTHER
VVR OTHER

If so, start a new query

add "tblGroup"
add "Asset and SLN Data"

-------------
aliases
------------
Right-mouse click on tblGroup
and choose "Properties"
Change "Alias" to G

Right-mouse click on other table
and choose "Properties"
Change "Alia" to A

--------
joins
-------

Drop-and-drag the [Trade] field in G table
over onto [Trade TypeID] field in A table
(which will create a join line between the
two tables)

Right-mouse click on this join line
and choose "Properties"

Select the option that returns all of G
and only matching A

------------
return fields
------------

Drag-and-drop [TradeGrp] from G
down to a Field row in grid

Drag-and-drop [Orig Notional] from A
down to a Field row in another col of grid

----------
aggregate
----------

Click on summation icon (greek sigma) in toolbar
to change to totals query

In grid column for [Orig Notional]
change "Group By" to "Sum"

Save query and then open in "SQL View"
so will probably look like:

SELECT
G.TradeGrp,
Sum(A.[Orig Notional]) As [SumOfOrig Notional]
FROM
tblGroup As G
LEFT JOIN
[Asset and SLN Data] As A
ON
G.Trade = A.[Trade TypeID]
GROUP BY
G.Trade;

To use sum alias in future calculations,
plus change null sums to 0,
plus include your Result logic w/o making
it have to be part of GROUP BY,
plus not include "IGNORE",
change to

SELECT
G.TradeGrp,
Sum(IIF(A.[Orig Notional])<>0,A.[Orig Notional],0) As GrpSum,
Max(SELECT Sum(T.[Orig Notional]) FROM
[Asset and SLN Data] As T
WHERE T.[Trade TypeID ]<>'SLN') AS NoSLNTotalSum,
Max(GrpSum/NoSLNTotalSum) As PortfolioPerCent,
Max(Switch((G.TradeGrp="OTHER" And GrpSum>0),
"FAIL",True,"PASS")) AS Result
FROM
tblGroup As G
LEFT JOIN
[Asset and SLN Data] As A
ON
G.Trade = A.[Trade TypeID]
WHERE (G.TradeGrp<>"IGNORE")
GROUP BY
G.Trade;
 
G

Gary Walter

maybe it was too early in AM....
but after looking at earlier post,
I believe this will *actually* work:

SELECT
G.TradeGrp,
Sum(IIf(A.[Orig Notional]<>0,A.[Orig Notional],0)) AS GrpSum,
(SELECT Sum(T.[Orig Notional]) FROM
[Asset and SLN Data] As T
WHERE T.[Trade TypeID ]<>'SLN') AS NoSLNTotalSum, GrpSum/NoSLNTotalSum AS
PortfolioPerCent,
IIf(G.TradeGrp="OTHER" And GrpSum>0,"FAIL","PASS") AS Result
FROM
tblGroup AS G
LEFT JOIN
[Asset and SLN Data] AS A
ON
G.Trade = A.[Trade TypeId]
WHERE (((G.TradeGrp)<>"IGNORE"))
GROUP BY G.TradeGrp

I can only guess I was thinking
about crosstabs earlier...

note also that all this depends on tblGroup
having every possible [Trade TypeID] from
[Asset and SLN Data] in its [Trade] field.

Gary Walter said:
Frank said:
Here is my SQL code -- I think the problem might be that I am trying to
create everything in one query. I think I need a group by statement that
includes OTHER, but I can't because the OTHER subcategory is created in
this
query. Does this sound right.

SELECT
IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID ]="SLN","IGNORE","OTHER"))) AS
Type,
Sum([Asset and SLN Data].[Orig Notional ]) AS [SumOfOrig Notional],
Sum([Asset and SLN Data].[Orig Notional]/DSum("[Orig Notional]","Asset
and
SLN Data","[Trade TypeID ] <> 'SLN'")) AS [Portfolio %],
IIf([Type]="OTHER" And [SumOfOrig Notional]>0,"FAIL","PASS") AS Result
FROM
[Asset and SLN Data]

GROUP BY
IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID ]="SLN","IGNORE","OTHER")))

HAVING
(((IIf([Asset and SLN Data]![Trade TypeID ]="USCPA"
Or [Asset and SLN Data]![Trade TypeID ]="TRV"
Or [Asset and SLN Data]![Trade TypeID ]="MMF",
[Asset and SLN Data]![Trade TypeID ],
(IIf([Asset and SLN Data]![Trade TypeID
]="SLN","IGNORE","OTHER"))))<>"IGNORE"))
ORDER BY Sum([Asset and SLN Data].[Orig Notional ]) DESC;
Click to expand...

Hi Frank,

Did you create a table ("tblGroup")
with 2 fields

Trade TradeGrp
------ ---------
MMF MMF
TRV TRV
USCPA USCPA
SLN IGNORE
SPV OTHER
STR OTHER
VVR OTHER

If so, start a new query

add "tblGroup"
add "Asset and SLN Data"

-------------
aliases
------------
Right-mouse click on tblGroup
and choose "Properties"
Change "Alias" to G

Right-mouse click on other table
and choose "Properties"
Change "Alia" to A

--------
joins
-------

Drop-and-drag the [Trade] field in G table
over onto [Trade TypeID] field in A table
(which will create a join line between the
two tables)

Right-mouse click on this join line
and choose "Properties"

Select the option that returns all of G
and only matching A

------------
return fields
------------

Drag-and-drop [TradeGrp] from G
down to a Field row in grid

Drag-and-drop [Orig Notional] from A
down to a Field row in another col of grid

----------
aggregate
----------

Click on summation icon (greek sigma) in toolbar
to change to totals query

In grid column for [Orig Notional]
change "Group By" to "Sum"

Save query and then open in "SQL View"
so will probably look like:

SELECT
G.TradeGrp,
Sum(A.[Orig Notional]) As [SumOfOrig Notional]
FROM
tblGroup As G
LEFT JOIN
[Asset and SLN Data] As A
ON
G.Trade = A.[Trade TypeID]
GROUP BY
G.Trade;

To use sum alias in future calculations,
plus change null sums to 0,
plus include your Result logic w/o making
it have to be part of GROUP BY,
plus not include "IGNORE",
change to

SELECT
G.TradeGrp,
Sum(IIF(A.[Orig Notional])<>0,A.[Orig Notional],0) As GrpSum,
Max(SELECT Sum(T.[Orig Notional]) FROM
[Asset and SLN Data] As T
WHERE T.[Trade TypeID ]<>'SLN') AS NoSLNTotalSum,
Max(GrpSum/NoSLNTotalSum) As PortfolioPerCent,
Max(Switch((G.TradeGrp="OTHER" And GrpSum>0),
"FAIL",True,"PASS")) AS Result
FROM
tblGroup As G
LEFT JOIN
[Asset and SLN Data] As A
ON
G.Trade = A.[Trade TypeID]
WHERE (G.TradeGrp<>"IGNORE")
GROUP BY
G.Trade;
Click to expand...
 
G

Guest

Gary,
Thank you for putting so much time and thought into this! This is very
useful information and really getting me to think about my approach.

I did not create tblGroup because unfortunately the "other" category might
contain values that are not defined yet and hence I have to screen for NOT
values on the fly.
I have the query working right now -- except for inserting the "0" into null
valuse which I see you addressed. I will also try and addressthe nested IIf
statements which aren't very elegant in my code. I will try your version
later on tonight and let you know.
Thanks again for all your help! I really approeciate it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Returning a value from a query field to a form in Access 2000 3
adding dsum results in a query 3

Top