What to choose for value in crosstab

[Susan L]

I'm trying to set up a cross tab to report "valid", "invalid" and "total"
transactions (rows) by month (columns). I have a form on which a user can
select a date, but in truth, the date parameter will always start with
January 1 of the current year. (So maybe I could use "DateSerial(Year(Date),
1, 1)" as criteria -- but i don't know where to put it.)

Here is the SQL for the query as it now stands -- and which is not working.
It shows what I want to be rows as columns.

PARAMETERS [Forms]![frm_Generate_Reports]![txtStartDate] DateTime;
TRANSFORM Max(qry_ValidationByMonth.STDT) AS MaxOfSTDT
SELECT qry_ValidationByMonth.VLD_TXN_CNT AS Valid,
qry_ValidationByMonth.IVLD_TXN_CNT AS Invalid, qry_ValidationByMonth.Total
FROM qry_ValidationByMonth
GROUP BY qry_ValidationByMonth.VLD_TXN_CNT,
qry_ValidationByMonth.IVLD_TXN_CNT, qry_ValidationByMonth.Total
PIVOT "Mth" &
DateDiff("m",[STDT],[Forms]![frm_Generate_Reports]![txtStartDate]) In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I don't know how to define the value -- have used the STDT (date) and have
tried count, max, first. Maybe i need an expression? Am baffled -- always get
stuck on the value part of a crosstab.

Another factor to know about is that for the first 5 months of this year,
the data were totalled manually by month, so there is only one entry per
month in the table. Starting in June and from here forward, there will be
daily data, which will then be summarized by the month in the query. I think
this is not relevant to my issue, but thought you should know.

Would appreciate any help you can provide. Thanks.

 

[KARL DEWEY]

Your select query is not set correctly for your crosstab rows. All row
labels must be in the same field, not separate fields as you have.
The select query fields need to be like this --
Status - "valid", "invalid" and "total"
Your_Date_Field - DateTime
Your_Number_Field - for value

I assume that you are putting a number in [STDT] as you are using it as
offset to date in [Forms]![frm_Generate_Reports]![txtStartDate].
In that case the fields would be --
Status - "valid", "invalid" and "total"
[STDT]
Your_Number_Field - for value

You most likely do not need the select query at all. Post sample raw data
and example of what you want the crosstab query output to look like.

 

[Susan L]

Thanks for responding.
Here is a sample of my raw data. I don't use the State number in the query,
but included it so the sample data would make more sense. These are totals by
state of valid and invalid transactions for the month of June. What I want is
the total of valid and invalid transactions distributed by month.

STDT ENDT State_No VLD_TXN_CNT IVLD_TXN_CNT
6/1/2008 6/30/2008 01 2646 552
6/1/2008 6/30/2008 02 2106 83
6/1/2008 6/30/2008 03 13752 762
6/1/2008 6/30/2008 04 13705 78
6/1/2008 6/30/2008 05 15458 651

Starting in June, there will be similar data for every month -- except that
for the first 5 months of 2008, the totals were done manually, and what I
have is the total for each month (which is close to what I want to see in the
report).

1/1/2008 1/31/2008 N/A 1115972 63264
2/1/2008 N/A N/A 1078684 677790

Here's what I want to see in the report:
Jan Feb Mar
Valid Transactions 1115972 1078684 1258788
Invalid Transactions 63,264 67790 76442
Total Transactions Calculated in the report
Percentage of Valid Calculated in the report

--
susan

Your select query is not set correctly for your crosstab rows. All row
labels must be in the same field, not separate fields as you have.
The select query fields need to be like this --
Status - "valid", "invalid" and "total"
Your_Date_Field - DateTime
Your_Number_Field - for value

I assume that you are putting a number in [STDT] as you are using it as
offset to date in [Forms]![frm_Generate_Reports]![txtStartDate].
In that case the fields would be --
Status - "valid", "invalid" and "total"
[STDT]
Your_Number_Field - for value

You most likely do not need the select query at all. Post sample raw data
and example of what you want the crosstab query output to look like.

--
KARL DEWEY
Build a little - Test a little

I'm trying to set up a cross tab to report "valid", "invalid" and "total"
transactions (rows) by month (columns). I have a form on which a user can
select a date, but in truth, the date parameter will always start with
January 1 of the current year. (So maybe I could use "DateSerial(Year(Date),
1, 1)" as criteria -- but i don't know where to put it.)

Here is the SQL for the query as it now stands -- and which is not working.
It shows what I want to be rows as columns.

PARAMETERS [Forms]![frm_Generate_Reports]![txtStartDate] DateTime;
TRANSFORM Max(qry_ValidationByMonth.STDT) AS MaxOfSTDT
SELECT qry_ValidationByMonth.VLD_TXN_CNT AS Valid,
qry_ValidationByMonth.IVLD_TXN_CNT AS Invalid, qry_ValidationByMonth.Total
FROM qry_ValidationByMonth
GROUP BY qry_ValidationByMonth.VLD_TXN_CNT,
qry_ValidationByMonth.IVLD_TXN_CNT, qry_ValidationByMonth.Total
PIVOT "Mth" &
DateDiff("m",[STDT],[Forms]![frm_Generate_Reports]![txtStartDate]) In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I don't know how to define the value -- have used the STDT (date) and have
tried count, max, first. Maybe i need an expression? Am baffled -- always get
stuck on the value part of a crosstab.

Another factor to know about is that for the first 5 months of this year,
the data were totalled manually by month, so there is only one entry per
month in the table. Starting in June and from here forward, there will be
daily data, which will then be summarized by the month in the query. I think
this is not relevant to my issue, but thought you should know.

Would appreciate any help you can provide. Thanks.