returning all rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rich R. - 07 Nov 2007 22:03 GMT

I am querying the results of a UNION query that joined 2 sets of data
(expenses and revenue). I want to return all the rows from the UNION query,
but I only want dollar amounts for those rows that contain 'expenses' (I
later want to do the same thing to deliver 'renenue' rows). The whole point
of this exercise is to produce a report that delivers expense amounts and
revenue amounts side by side. The number of expense rows in the UNION result
does NOT EQUAL the number of revenue rows.

The initial simple query written is:
SELECT qrySandySS_union.Appr_Sequence AS [Section], qrySandySS_union.[Agency
Long Name], qrySandySS_union.[Line Item Long Name],
qrySandySS_union.Appr_Unit_Long_Name, qrySandySS_union.Object_Category_Desc
AS [Expense Type], qrySandySS_union.type, qrySandySS_union.[CY Act Final]
FROM qrySandySS_union
WHERE (((qrySandySS_union.type)="E"))
ORDER BY qrySandySS_union.Appr_Sequence;

This WHERE clause, of course delivers only expense rows. Any suggestions on
how to deliver ALL rows, even though the [CT ACT] value of the non-expense
rows will be null?

Reply to this Message

KARL DEWEY - 07 Nov 2007 23:14 GMT

Try this --
WHERE (qrySandySS_union.type)="E" OR (qrySandySS_union.type)="R")

It seems odd to expect that there would be exactly the same number of
expenses as revenue entries.

Signature

KARL DEWEY
Build a little - Test a little

> I am querying the results of a UNION query that joined 2 sets of data
> (expenses and revenue). I want to return all the rows from the UNION query,
[quoted text clipped - 16 lines]
> how to deliver ALL rows, even though the [CT ACT] value of the non-expense
> rows will be null?

Reply to this Message

Rich R. - 08 Nov 2007 16:26 GMT

Thanks Karl, but that doesn't work.
Let me re-state my problem. I have a UNION query result that is reurning
rows of data on expenses and funding (revenues) by governmental unit. The
result of the query lists the rows such that the $ amounts (CY Final) for
expense rows (type=E) and funding rows (type=F) appear in a single column. I
need to be able to produce a result (either by query or through a report)
that will list the Section, governmental units and the CY Final amounts with
the expense and funding amounts in 2 separate columns (side-by-side). Doing
this via a query did not seem to work since joining on Section (e.g.)
produces duplicate expense or funding amounts when there are more expense
rows than funding rows for a particular Section or vice-versa.

I have tried to approach it through a report by designing a Master report
and 2 subreports, one listing "E" rows and the other "F" rows, and placing
themk side-by-side. However, since the number of E rows and the number of F
rows are unequal (and as you noted, there is no reason they shouldn't be),
placing the 2 subreports side-by-side produces lines where expense data is
next to funding data that it is unrelated to (not a good situation!).

My thought was to force an equal number of rows in the E and F subreports by
designing a query that would (for type=E, e.g.) return all the rows in the
UNION query but would return a NULL (or a 0) in those rows where type NE "E".
Likewise for the "F" query. It may be something simple that I'm missing, but
I'm not necessarily a SQL expert.

Thanks & I hope this makes the problem a little clearer.
-Rich

> Try this --
> WHERE (qrySandySS_union.type)="E" OR (qrySandySS_union.type)="R")
[quoted text clipped - 22 lines]
> > how to deliver ALL rows, even though the [CT ACT] value of the non-expense
> > rows will be null?

Reply to this Message

KARL DEWEY - 08 Nov 2007 18:38 GMT

Modify you union query this way ---
SELECT Field1, Field2, Revenue, Null, Field_X
FROM Xxxx
SELECT Field1, Field2, Null, [CY Act Final], Field_X
FROM Yyyy

Signature

KARL DEWEY
Build a little - Test a little

> Thanks Karl, but that doesn't work.
> Let me re-state my problem. I have a UNION query result that is reurning
[quoted text clipped - 50 lines]
> > > how to deliver ALL rows, even though the [CT ACT] value of the non-expense
> > > rows will be null?

Reply to this Message

Rich R. - 08 Nov 2007 16:51 GMT

I think I may have found the answer to this. Early on, I had tried a IIf
expression querying against the UNION result set but couldn't get it to work
and so, thought that it couldn't work. As it turned out, I had an obscure
syntax error in the expression. I re-wrote it as
IIf(([qrySandySS_union.type]="E"),[qrySandySS_union.CY Act Final],0)
This delivers a 0 in the amount field (CY Act Final) when the type = F.
-Rich

Reply to this Message