Perhaps what you want is the following.
TRANSFORM CDbl(NZ(Sum([LincsProgramMRC/NRConly].Total),0)) AS [YTD Sum]
SELECT tble_LincsClin_budget.[CLIN Description]
, tble_LincsClin_budget.[CSA Account]
, [LincsProgramMRC/NRConly].PROD
FROM tble_LincsClin_budget LEFT JOIN [LincsProgramMRC/NRConly]
ON tble_LincsClin_budget.[CSA Account] =
[LincsProgramMRC/NRConly].[CSA Account]
GROUP BY tble_LincsClin_budget.[CLIN Description]
, tble_LincsClin_budget.[CSA Account]
, [LincsProgramMRC/NRConly].PROD
PIVOT [LincsProgramMRC/NRConly].Month;
Although I really think that YTD SUM would more likely be calculated in
the SELECT clause while the monthly amounts would be calculated in the
TRANSFORM clause. Something like this query
TRANSFORM CDbl(NZ(Sum([LincsProgramMRC/NRConly].Total),0)) AS MnthSum
SELECT tble_LincsClin_budget.[CLIN Description]
, tble_LincsClin_budget.[CSA Account]
, [LincsProgramMRC/NRConly].PROD
, CDbl(NZ(Sum([LincsProgramMRC/NRConly].Total),0)) AS RowTotal
FROM tble_LincsClin_budget LEFT JOIN [LincsProgramMRC/NRConly]
ON tble_LincsClin_budget.[CSA Account] =
[LincsProgramMRC/NRConly].[CSA Account]
GROUP BY tble_LincsClin_budget.[CLIN Description]
, tble_LincsClin_budget.[CSA Account]
, [LincsProgramMRC/NRConly].PROD
PIVOT [LincsProgramMRC/NRConly].Month;
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
AccessUser30 said:
sample rows-with records under the fields--(months continue until Sept)
fields-
description-----csa----Prod------oct---nov---dec--jan--feb---mar-----ytd sum
eastern region cst1234 mrc 45 75 0 0 98 75 593
west point cts879 nrc 0
southwest cts8970 mrc 1 0 0 -75 0 0 74
:
Or maybe i'm not clear.
RANSFORM Sum([LincsProgramMRC/NRConly].Total) AS [YTD Sum]
SELECT tble_LincsClin_budget.[CLIN Description], tble_LincsClin_budget.[CSA
Account], [LincsProgramMRC/NRConly].PROD
FROM tble_LincsClin_budget LEFT JOIN [LincsProgramMRC/NRConly] ON
tble_LincsClin_budget.[CSA Account] = [LincsProgramMRC/NRConly].[CSA Account]
GROUP BY tble_LincsClin_budget.[CLIN Description],
tble_LincsClin_budget.[CSA Account], [LincsProgramMRC/NRConly].PROD
PIVOT [LincsProgramMRC/NRConly].Month;
:
Sorry for being so dense, but for some reason what you are describing is
just not clear to me.
Can you post the SQL of your query? And a sample row with the data as you
want it and as it is now displaying?
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
Perhaps what you want is
YTD sum: =NZ(sum([amount]),0)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
While, no i didn't because I want each row to reflect zero in the ytd
column.
Therefore, your suggestion would not fit this situation. My query is
tracking expenditures, but for the first quarter one department had no
activity, which will change in the future. My question is asking whether
I
can combine the Nz([field],0) and the Sum(field) formulas to make one
formula
in my YTD-column or How can I set the YTD column to add across the rows
including the blanks, which will equal zero in the end?
column: YTD sum: =sum([amount] & nz([amount],0))
:
I don't understand your question.
First, did you try the suggestion? IF it failed, what was wrong?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
:
yes, however, i need the rows to show. Can the Nz and the sum
formulas
work by putting them together?
:
How about adding criteria to your query that eliminates the records
where
the field is null (blank)?
Add the field in the column heading into the query again
Field: [name of column heading field]
Total : WHERE
Critiera: Is Not Null
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
message
hi Guys,
I created a crosstab query to capture selected variables from my
source
table, but at present some of the rows are blank, which is causing
a
problem
in my YTD total column where the system is generating a <> column
in
my
query
result. I tried using the Nz([amount],0) formula but this
computed
the
blanks rows only and eliminated the other rows. How can I solve
this
problem?
