Trouble calculating in a report b/c of data structure
- From: Susan L <SusanL@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 1 Aug 2008 13:32:34 -0700
We have a report for our customer that must be set up as follows:
Transactions Jan Feb Mar etc to Dec Total %ofTotal
Valid 170,000 150,000 190,000 nnnn
pp.p%
Invalid 100,000 125,000 160,000 nnnn
pp.p%
Totals 270,000 275,000 350,000 nnnn pp.p%
% Valid 62.9 54.5 54.2 etc to Dec
The table from which the data comes, however, is structured as follows.
Month Valid Invalid Ect. (7 columns)
Jan 170,000 100,000
Feb 150,000 125,000 etc
Mar 190,000 160,000 etc
So I’ve done a union query to place the transactions (Valid, Invalid) in
rows, and a crosstab query to get the data displayed by month. So we end up
with a field Transactions and fields for each month. (The field TotalOfMonth
seems irrelevant and looks wrong to me. But I digress.)
The totals at the bottom, of course, work just fine. However, because
“Valid” and “Invalid” are in a single field, Transactions, I don’t know how I
can calculate the totals on the right (nnnn) and the percentages (pp.p). Is
there a way to refer to rows of a field?
Maybe the report will have to be exported to Excel, though I’d prefer the
nicer-looking reports from Access.
Here is the SQL in case it’s helpful.
Query 1 – (sums data by month)
SELECT tbl_Trans_Val_Inval.STDT, Sum(tbl_Trans_Val_Inval.VLD_TXN_CNT) AS
SumOfVLD_TXN_CNT, Sum(tbl_Trans_Val_Inval.IVLD_TXN_CNT) AS SumOfIVLD_TXN_CNT
FROM tbl_Trans_Val_Inval
GROUP BY tbl_Trans_Val_Inval.STDT;
Query 2 - (extracts valid and invalid to a field; formats data into months;
creates a sequence field to be used to order Valid and Invalid)
SELECT Format([STDT], "MMM") As Mth, "Valid" as [Transactions], "1" as
Sequence, [VLD_TXN_CNT] as
[Month Total]
FROM tbl_Trans_Val_Inval
UNION ALL SELECT Format([STDT], "MMM") As Mth, "Invalid" as [Transactions],
"2" as Sequence,
[IVLD_TXN_CNT] as [Month Total]
FROM tbl_Trans_Val_Inval;
Query 3
TRANSFORM Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [SumOfMonth
Total]
SELECT qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence,
Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [Total Of Month Total]
FROM qry_ValInvlTrans_Count2_Union
GROUP BY qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence
ORDER BY qry_ValInvlTrans_Count2_Union.Sequence
PIVOT qry_ValInvlTrans_Count2_Union.Mth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Thanks for any suggestions.
--
susan
.
- Prev by Date: Re: Sorting Multiple Columns Independently
- Next by Date: RE: Format$ Error
- Previous by thread: Re: NEW - Change colors on one field based on another
- Next by thread: Can a form tell another form to add a record?
- Index(es):
Relevant Pages
|
