Hi -

I am trying to get a report that will show 12 months of data for a
Department (or less if fewer than 12 months are on file), BUT the user
is only supplying the Ending Fiscal year and Fiscal Month.

I have a query that gives me all the data - perfectly - but I think I
need a crosstab to get the data on the report.  If there's a better
way, I'm open to it!

SO, the report would be:

"Values for the 12 months ending FY 2009 Fiscal Month 7"

          FY 08-9   FY08-10   FY08-11  FY08-12  FY09-1  FY09-2
FY09-3.......FY09-7   Avg   Total
Dept    $$            $$            $$           $$             $
$       $$         $$              $$         $$      $$

I have a crosstab but when I go to do the report, I realize the Column
Headings are "fixed" - they say "FY08-9"  etc. Where I need the
headings on the report to be dynamic - they would change with every
report, based on the user's entry parameter.

I figure I could make the headings on the report be like "DateAdd "m"
-12,(entry info)",
"DateAdd "m" -11 (entry info)" - I could probably figure that out.

But I can't get the crosstab to sort properly - no matter what I do!
Obviously the data on the report has to appear under the proper
heading.

I tried to think of a way to use a temporary table to help me - I'm
just stuck!  I don't believe this should be all that hard but I've
been trying for the better part of 3 days now and it's time to ask for
help!

Thank you -
Sara

On Oct 23, 3:17 pm, sara <saraqp... at (no spam) yahoo.com> wrote:





Hi -

I am trying to get a report that will show 12 months of data for a
Department (or less if fewer than 12 months are on file), BUT the user
is only supplying the Ending Fiscal year and Fiscal Month.

I have a query that gives me all the data - perfectly - but I think I
need a crosstab to get the data on the report.  If there's a better
way, I'm open to it!

SO, the report would be:

"Values for the 12 months ending FY 2009 Fiscal Month 7"

          FY 08-9   FY08-10   FY08-11  FY08-12  FY09-1  FY09-2
FY09-3.......FY09-7   Avg   Total
Dept    $$            $$            $$           $$             $
$       $$         $$              $$         $$      $$

I have a crosstab but when I go to do the report, I realize the Column
Headings are "fixed" - they say "FY08-9"  etc. Where I need the
headings on the report to be dynamic - they would change with every
report, based on the user's entry parameter.

I figure I could make the headings on the report be like "DateAdd "m"
-12,(entry info)",
"DateAdd "m" -11 (entry info)" - I could probably figure that out.

But I can't get the crosstab to sort properly - no matter what I do!
Obviously the data on the report has to appear under the proper
heading.

I tried to think of a way to use a temporary table to help me - I'm
just stuck!  I don't believe this should be all that hard but I've
been trying for the better part of 3 days now and it's time to ask for
help!

Thank you -
Sara

Use the IN clause of the Pivot Table statement to do the ordering.- Hide quoted text -

- Show quoted text -

sara <saraqp... at (no spam) yahoo.com> wrote innews:b6a76afa-422c-4b1a-a418-e08191237a31 at (no spam) o13g2000vbl.googlegroups.co
m:





Hi -

I am trying to get a report that will show 12 months of data for a
Department (or less if fewer than 12 months are on file), BUT the
user is only supplying the Ending Fiscal year and Fiscal Month.

I have a query that gives me all the data - perfectly - but I
think I need a crosstab to get the data on the report.  If there's
a better way, I'm open to it!

SO, the report would be:

"Values for the 12 months ending FY 2009 Fiscal Month 7"

          FY 08-9   FY08-10   FY08-11  FY08-12  FY09-1  FY09-2
FY09-3.......FY09-7   Avg   Total
Dept    $$            $$            $$           $$             $
$       $$         $$              $$         $$      $$

I have a crosstab but when I go to do the report, I realize the
Column Headings are "fixed" - they say "FY08-9"  etc. Where I need
the headings on the report to be dynamic - they would change with
every report, based on the user's entry parameter.

I figure I could make the headings on the report be like "DateAdd
"m" -12,(entry info)",
"DateAdd "m" -11 (entry info)" - I could probably figure that out.

But I can't get the crosstab to sort properly - no matter what I
do! Obviously the data on the report has to appear under the
proper heading.

I tried to think of a way to use a temporary table to help me -
I'm just stuck!  I don't believe this should be all that hard but
I've been trying for the better part of 3 days now and it's time
to ask for help!

Thank you -
Sara

To do this, you need to return column headings in the crosstab as
formatted numbers, 01,02,03, through 12
so given your ending year and month values, you would create the
column for the heading as
Format(13-datediff("m",
dateserial(FiscalYear,FiscalMonth,1),
dateserial([Forms].[frmChooseAccount].[cboFiscalYear],
[Forms].[frmChooseAccount].[cboFiscalMonth],1)),00)

Then in the report, you would make the visible column headers by
reversing the process.

--
Bob Quintal

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

- Show quoted text -

Hi -

I am trying to get a report that will show 12 months of data for a
Department (or less if fewer than 12 months are on file), BUT the
user is only supplying the Ending Fiscal year and Fiscal Month.

I have a query that gives me all the data - perfectly - but I
think I need a crosstab to get the data on the report. If there's
a better way, I'm open to it!

SO, the report would be:

"Values for the 12 months ending FY 2009 Fiscal Month 7"

FY 08-9 FY08-10 FY08-11 FY08-12 FY09-1 FY09-2
FY09-3.......FY09-7 Avg Total
Dept $$ $$ $$ $$ $
$ $$ $$ $$ $$ $$

I have a crosstab but when I go to do the report, I realize the
Column Headings are "fixed" - they say "FY08-9" etc. Where I need
the headings on the report to be dynamic - they would change with
every report, based on the user's entry parameter.

I figure I could make the headings on the report be like "DateAdd
"m" -12,(entry info)",
"DateAdd "m" -11 (entry info)" - I could probably figure that out.

But I can't get the crosstab to sort properly - no matter what I
do! Obviously the data on the report has to appear under the
proper heading.

I tried to think of a way to use a temporary table to help me -
I'm just stuck! I don't believe this should be all that hard but
I've been trying for the better part of 3 days now and it's time
to ask for help!

Thank you -
Sara

To do this, you need to return column headings in the crosstab as

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

On Oct 23, 3:17 pm, sara <saraqp... at (no spam) yahoo.com> wrote:





Hi -

I am trying to get a report that will show 12 months of data for a
Department (or less if fewer than 12 months are on file), BUT the user
is only supplying the Ending Fiscal year and Fiscal Month.

I have a query that gives me all the data - perfectly - but I think I
need a crosstab to get the data on the report. If there's a better
way, I'm open to it!

SO, the report would be:

"Values for the 12 months ending FY 2009 Fiscal Month 7"

FY 08-9 FY08-10 FY08-11 FY08-12 FY09-1 FY09-2
FY09-3.......FY09-7 Avg Total
Dept $$ $$ $$ $$ $
$ $$ $$ $$ $$ $$

I have a crosstab but when I go to do the report, I realize the Column
Headings are "fixed" - they say "FY08-9" etc. Where I need the
headings on the report to be dynamic - they would change with every
report, based on the user's entry parameter.

I figure I could make the headings on the report be like "DateAdd "m"
-12,(entry info)",
"DateAdd "m" -11 (entry info)" - I could probably figure that out.

But I can't get the crosstab to sort properly - no matter what I do!
Obviously the data on the report has to appear under the proper
heading.

I tried to think of a way to use a temporary table to help me - I'm
just stuck! I don't believe this should be all that hard but I've
been trying for the better part of 3 days now and it's time to ask for
help!

Thank you -
Sara

Use the IN clause of the Pivot Table statement to do the ordering.- Hide
quoted text -

- Show quoted text -

Thanks, Chuck. But I am doing a Crosstab Query, not a Pivot Table.
Unless maybe they are the same name? Anyway, I don't see where to put
the IN clause and I don't see anything in Help or in Google posts that
gives me any info.

can you possibly be a little more specific for me here?

Thank you -
sara

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.

sara wrote:
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.

I think the heading text boxes would each use an expression
something like:

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 12, 1)

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 11, 1)

        . . .

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 1, 1)

Set the text box's Format property to display the date the
way you want to see it.

--
Marsh

It may me off the point, Sara, but I frequenly have to do a CrossTab query
to get the "body" of the query in this case money and a second crosstab
query to get the titles ( in this case the date info)
The body query has and arbitary Expr1:2 and the date query has an Expr1:1
Then use a union query to combine these 2 queries with an ORDER BY Expr1 to
get the date info first

This enable me to generate this query with the first line (Not the heading)
as the information on the report

   RptPos TypeOfSpace SortOrder 1 3 4 5 6 7 8 9 10 11 12
      1 Type of Space 10 Sep 09 - Oct 09 Nov 09 Dec 09 Jan 10 Feb 10 Mar 10
Apr 10 May 10 Jun 10 Jul 10 Aug 10
      1 Discount 20 0.00% 8.33% 16.67% 25.00% 33.33% 41.67% 50.00% 58.33%
66.67% 75.00% 83.33%
      2 Canoe Rack 30 £26.50 £24.29 £22.08 £19.88 £17.67 £15.46 £13.25
£11.04 £8.83 £6.63 £4.42
      2 Dinghy Park 30 £52.50 £48.13 £43.75 £39.38 £35.00 £30.62 £26.25
£21.88 £17.50 £13.13 £8.75
      2 Dinghy Rack 30 £31.50 £28.88 £26.25 £23.63 £21.00 £18.37 £15.75
£13.13 £10.50 £7.88 £5.25
      2 Fetherston Inner 30 £147.00 £134.75 £122.50 £110.25 £98.00 £85.75
£73.50 £61.25 £49.00 £36.75 £24.50
      2 Fetherston Outer 30 £168.00 £154.01 £139.99 £126.00 £112.01 £97.99
£84.00 £70.01 £55.99 £42.00 £28.01
      2 Grange Deep 30 £147.00 £134.75 £122.50 £110.25 £98.00 £85.75 £73.50
£61.25 £49.00 £36.75 £24.50
      2 Grange Shallow 30 £124.00 £113.67 £103.33 £93.00 £82.67 £72.33
£62.00 £51.67 £41.33 £31.00 £20.67
      2 Mast Rack 30 £21.00 £19.25 £17.50 £15.75 £14.00 £12.25 £10.50 £8.75
£7.00 £5.25 £3.50
      2 Old Pontoon 30 £147.00 £134.75 £122.50 £110.25 £98.00 £85.75 £73.50
£61.25 £49.00 £36.75 £24.50
      2 Outboard Shed 30 £26.50 £24.29 £22.08 £19.88 £17.67 £15.46 £13.25
£11.04 £8.83 £6.63 £4.42
      2 Rib Rack 30 £26.50 £24.29 £22.08 £19.88 £17.67 £15.46 £13.25 £11.04
£8.83 £6.63 £4.42
      2 Sail Locker 30 £26.50 £24.29 £22.08 £19.88 £17.67 £15.46 £13.25
£11.04 £8.83 £6.63 £4.42

Phil

"sara" <saraqp... at (no spam) yahoo.com> wrote in message

news:99f10e56-13b5-4194-b131-a68e3ec68dde at (no spam) m11g2000vbl.googlegroups.com...
On Oct 26, 7:00 am, sara <saraqp... at (no spam) yahoo.com> wrote:





On Oct 26, 12:44 am, Marshall Barton <marshbar... at (no spam) wowway.com> 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- Hide quoted text -

- Show quoted text -

"sara" <saraqp... at (no spam) yahoo.com> wrote in message

news:fc7d1a4b-7e84-4b55-a91e-db11f9e14c18 at (no spam) d34g2000vbm.googlegroups.com...
On Oct 25, 12:56 pm, Chuck Grimsby <cgat... at (no spam) gmail.com> wrote:





On Oct 23, 3:17 pm, sara <saraqp... at (no spam) yahoo.com> wrote:

Hi -

I am trying to get a report that will show 12 months of data for a
Department (or less if fewer than 12 months are on file), BUT the user
is only supplying the Ending Fiscal year and Fiscal Month.

I have a query that gives me all the data - perfectly - but I think I
need a crosstab to get the data on the report. If there's a better
way, I'm open to it!

SO, the report would be:

"Values for the 12 months ending FY 2009 Fiscal Month 7"

FY 08-9 FY08-10 FY08-11 FY08-12 FY09-1 FY09-2
FY09-3.......FY09-7 Avg Total
Dept $$ $$ $$ $$ $
$ $$ $$ $$ $$ $$

I have a crosstab but when I go to do the report, I realize the Column
Headings are "fixed" - they say "FY08-9" etc. Where I need the
headings on the report to be dynamic - they would change with every
report, based on the user's entry parameter.

I figure I could make the headings on the report be like "DateAdd "m"
-12,(entry info)",
"DateAdd "m" -11 (entry info)" - I could probably figure that out.

But I can't get the crosstab to sort properly - no matter what I do!
Obviously the data on the report has to appear under the proper
heading.

I tried to think of a way to use a temporary table to help me - I'm
just stuck! I don't believe this should be all that hard but I've
been trying for the better part of 3 days now and it's time to ask for
help!

Thank you -
Sara

Use the IN clause of the Pivot Table statement to do the ordering.- Hide
quoted text -

- Show quoted text -
Thanks, Chuck.  But I am doing a Crosstab Query, not a Pivot Table.
Unless maybe they are the same name?  Anyway, I don't see where to put
the IN clause and I don't see anything in Help or in Google posts that
gives me any info.

can you possibly be a little more specific for me here?

Thank you -
sara

If you are using the query builder, look at "column headings" in the query
property box. Enter "1,2,3" if your headings will be 123. In the SQL window
your will see "PIVOT tablename.fieldname In (1,2,3);" at the end of the
query. Note if some of your months are NOT in the data, this will force the
column to output, making report writing much easer.- Hide quoted text -

- Show quoted text -

On Oct 26, 12:44 am, Marshall Barton <marshbar... at (no spam) wowway.com> 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 -

sara wrote:
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.

I think the heading text boxes would each use an expression
something like:

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 12, 1)

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 11, 1)

        . . .

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 1, 1)

Set the text box's Format property to display the date the
way you want to see it.

--
Marsh

On Oct 25, 12:56 pm, Chuck Grimsby <cgat... at (no spam) gmail.com> wrote:





On Oct 23, 3:17 pm, sara <saraqp... at (no spam) yahoo.com> wrote:

Hi -

I am trying to get a report that will show 12 months of data for a
Department (or less if fewer than 12 months are on file), BUT the user
is only supplying the Ending Fiscal year and Fiscal Month.

I have a query that gives me all the data - perfectly - but I think I
need a crosstab to get the data on the report.  If there's a better
way, I'm open to it!

SO, the report would be:

"Values for the 12 months ending FY 2009 Fiscal Month 7"

          FY 08-9   FY08-10   FY08-11  FY08-12  FY09-1  FY09-2
FY09-3.......FY09-7   Avg   Total
Dept    $$            $$            $$           $$             $
$       $$         $$              $$         $$      $$

I have a crosstab but when I go to do the report, I realize the Column
Headings are "fixed" - they say "FY08-9"  etc. Where I need the
headings on the report to be dynamic - they would change with every
report, based on the user's entry parameter.

I figure I could make the headings on the report be like "DateAdd "m"
-12,(entry info)",
"DateAdd "m" -11 (entry info)" - I could probably figure that out.

But I can't get the crosstab to sort properly - no matter what I do!
Obviously the data on the report has to appear under the proper
heading.

I tried to think of a way to use a temporary table to help me - I'm
just stuck!  I don't believe this should be all that hard but I've
been trying for the better part of 3 days now and it's time to ask for
help!

Thank you -
Sara

Use the IN clause of the Pivot Table statement to do the ordering.- Hide quoted text -

- Show quoted text -

Thanks, Chuck.  But I am doing a Crosstab Query, not a Pivot Table.
Unless maybe they are the same name?  Anyway, I don't see where to put
the IN clause and I don't see anything in Help or in Google posts that
gives me any info.

can you possibly be a little more specific for me here?

Thank you -
sara

sara wrote:
On Oct 26, 12:35 pm, Marshall Barton <marshbar... at (no spam) wowway.com> wrote:
sara wrote:
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.

I think the heading text boxes would each use an expression
something like:

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 12, 1)

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 11, 1)

        . . .

        =DateSerial(Forms.frmChooseAccount.cboFiscalYear,
Forms.frmChooseAccount.cboFiscalMonth - 1, 1)

Set the text box's Format property to display the date the
way you want to see it.

--
Marsh

I hope I didn't speak too soon,....While I am able to get the data on
a report, I don't see how to get from the Crosstab query into a Temp
Table - dynamically.  I know how to put the code for a query into VBA
(I struggle, but I usually manage to get it done!), but I don't even
see how this will help me here.

I have the crosstab which creates columns with the names "7/1/2009"
"8/1/2009".  I need to get that into a temp table (or other query) but
the field names will be different every time.

Does this make sense?  Once again - I fell I'm really close, but just
not there.  Like I have step 1 and 3, but can't get step 2.

Why would you want to put that into a table?  You should be
able to run the report's crosstab query whenever you want to
see the data.

I can't see why you would need to, but if you really feel
it's necessay to put that data in another table, can you use
the crosstab query as the source of a make table or append
query?

--
Marsh- Hide quoted text -

- Show quoted text -