Re: Can't figure out how to write this CrosTab properly - Field heading problem

sara <saraqpost@xxxxxxxxx> wrote in
news:af350a2b-c8ab-48dc-b589-1137b5462891@xxxxxxxxxxxxxxxxxxxxxxxxxxx
m:

On Oct 26, 5:06 pm, Bob Quintal <rquin...@xxxxxxxxxxxxx> wrote:
sara <saraqp...@xxxxxxxxx> wrote
innews:99f10e56-13b5-4194-b131-a68e3ec68
dde@xxxxxxxxxxxxxxxxxxxxxxxxxxx
m:





On Oct 26, 7:00 am, sara <saraqp...@xxxxxxxxx> wrote:
On Oct 26, 12:44 am, Marshall Barton <marshbar...@xxxxxxxxxx>
wrote:

sara wrote:

[]

I tried this and I'm still not getting the "right" order; I
think it's when I switch years.
[]
I expect to see 12, 11, 10, 9 8, 7, 6, 5, 4, 3, 2, 1 (I put
12 - instead of 13 -  I have to work that one out myself
for a bit to figure out which number is right!)

But I'm seeing: 9, 8, 7, 6, 5, 4, 3,2, 12, 11, 10,1

Try it without using the Format function.  Format always
results in a text string, which sort the way you are seeing.

--
Marsh

Thank you, Marsh.  This does give me the numbers in the proper
sequence, but I don't know which month the numbers represent!
 I don't know how to get that.  I've tried tackingthe
FiscalYear and Month at the end of the above (without the
format), but it doesn't . Then it occurred to me to use the
"DateSerial" that Bob formulated for me - use that as the
column heading and I think that may work!

At least gives me the next step.  I have a date  - the first
of the month.  I just have to figure out how to turn that into
YYMM (Fiscal yr and Mo) in the report....which I should be
able to do with a format statement (I'm hoping) OR do it in
the final query I write for the report.

Do you think that will work?  I will try this approach later
this morning.
Many thanks - I'm getting there!
sara- Hide quoted text -

- Show quoted text -

I've tried all I can think of and I'm nowhere.  I need the user
to enter FY and Fiscal Month (each chosen from combo boxes on
the form) and show the prior 12 months' data,  with the proper
heading, on the report. Lost!

Thanks
Sara

You do not want to use e.g. 2009-10 as a column header in the
query feeding the report, because next month you will have a new
column to add and an old column to delete and the other columns
will all need to be moved. .

The query takes the data from the form to generate numbers from 1
to 12. (or 0 to 11)... That same form data can be used in the
report to calculate the headers for the report. The textboxes get
their data from the fields named 1 to 12.

--
Bob Quintal

PA is y I've altered my email address.- Hide quoted text -

- Show quoted text -

This is what I was trying to do - just posted in my last message
(at least that was my intent). I put your formula back in my
crosstab and put the headings in order: 12, 10, 9, ....

Now, how do I konw the column headings that I created using what
Marshall suggested:
=DateSerial([Forms].[frmReports].[cboFiscalYear],
[Forms].[frmReports].[cboFiscalMonth]-12,1) (-11, -10, etc)
"match" the data (col 12 as you suggested has to be the date
associated with the formula here)???

I actually got this working...in that I see data in all the
columns, etc. (I had to change your formula to start with 12- not
13-(12-
DateDiff("m",DateSerial([FiscalYr],[FiscalMonth],1),DateSerial([For
ms].
[frmReports].[cboFiscalYear],[Forms].[frmReports].[cboFiscalMonth],
1)))). But how do I know the column "1" is really the one I have
in the formula (above)? I think it should be, based on it's using
the same fields, but I absolutely want to be certain.

I *might* actually have gotten this (with all your help!)

The only way I can think of is to copy the query, turn off the
crosstab option in the copy, and show both the number 1-12 and the
datevalue side by side. If the number and the datevalue agree, then
you will know that the calculations are correct and can trust the
crosstab.



--
Bob Quintal

PA is y I've altered my email address.
.

Relevant Pages

  • Re: Dates not sorting correctly in Report
    ... the report) from the query and the SQL is as follows: ... I have also tried deleting the inner joined query qryPumpsRunningBin, ... FiscalYear - Ascending with a group Header and Footer ... have removed ALL sorting from the query. ...
    (microsoft.public.access.reports)
  • Re: Cross Tab with dynamic date column
    ... How to make the report to grab the additional column and subtract one column ... You can change your Column Heading to relative dates with ... "Oded Dror" wrote: ... I have a cross tab query ...
    (microsoft.public.access.reports)
  • Re: Dates not sorting correctly in Report
    ... I tried wrapping the field FiscalYear in Valin both the report fields ... the report) from the query and the SQL is as follows: ... FiscalYear - Ascending with a group Header and Footer ... have removed ALL sorting from the query. ...
    (microsoft.public.access.reports)
  • Re: Pivot query as report source
    ... If they might change and you don't want to change your query and report the ... > "Leo" wrote: ... >> I have a specific field marked as Column Heading ...
    (microsoft.public.access.reports)
  • Re: Dates not sorting correctly in Report
    ... I tried wrapping the field FiscalYear in Valin both the report fields ... the report) from the query and the SQL is as follows: ... FiscalYear - Ascending with a group Header and Footer ... have removed ALL sorting from the query. ...
    (microsoft.public.access.reports)