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

On Oct 26, 5:06 pm, Bob Quintal <rquin...@xxxxxxxxxxxxx> wrote:
sara <saraqp...@xxxxxxxxx> wrote innews:99f10e56-13b5-4194-b131-a68e3ec68dde@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([Forms].
[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!)

.

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)