Crosstab Query/Conversion/Report Generation... 
Author Message
 Crosstab Query/Conversion/Report Generation...

I hope somebody can help me with this one...

I've created an app in access 95 for my accounting
department... It works great... I can select criteria for
one of many reports, and select a month, and two years
for a financial statement (two years used for comparison)
After the user comes up with his/her criteria, a group of
functions creates a crosstab query with an sql statement i've
created based on the criteria selected.  From here, I
open a report with the newly created query... I've got everything
working perfectly... Here's the catch... A few members in
the department have just switched over to access 97...
no problem... just open it up without converting it, and run
it just as if I was using access 95... Well, the crosstab
query gets created, and when it comes time to open the
report, I get an error... It doesn't like the fact that one of my
variables doesn't specify where it will be contained in the
report... So I went into the query design, and it looks exactly
like it did when i created it in acc 95... The problem I found
comes when I use my where clause in the crosstab query...
Access will automatically create a duplicate for any field that
is found in the where clause... So in this case, I have one
field in the query called FISCAL_YEAR which specifies
that it will be shown as a column header in the query,
and it has created another field called FISCAL_YEAR which has
the totals part of the query set to Where, and it's criteria
says: In (1997,1996).

Field:         FISCAL_YEAR     |    someotherfield   |   FISCAL_YEAR
Table:        MyTable                 |                                 |
MyTable
Total:         Group By                |    Expression         |   Where
Crosstab:  Column Heading   |    Value                   |
Sort:           Descending           |                                 |
Criteria                                      |
|    In(1997,1996)

If I open the query, It works fine, and i get
exactly what i'm looking for in both versions of access, but when
It actually gets to the report part, It gives me an error saying
it doesn't recognize FISCAL_YEAR as a valid field name or
expression.  OK... So I go into the query design, and I see
where access has created a new column for the where clause,
and I delete that and put the In(1997,1996) under the FISCAL_YEAR
that has column heading... so now the query looks like this...

Field:         FISCAL_YEAR     |    someotherfield
Table:        MyTable                 |
Total:         Group By                |    Expression
Crosstab:  Column Heading   |    Value
Sort:           Descending           |                                 |
Criteria      In(1997,1996)        |

I open the report, and it works...
OK, so that's the problem... so I look at the sql view to see how
they have stated what i've just changed, to see If i can recreate
the same thing i've just changed it to, and It looks exactly the
same as the first instance... So I guess access 97 interprets the
queries in different ways when it comes to reports... So my question
is... Is there a way to specify in an sql statement how to not get it to
create
another column? How do i get it to not create another column for the where
clause, and include it in the first FISCAL_YEAR column???

This is the first real problem i've had in the conversion from 95 to 97
Thank you very much for the time you've taken to read this...
Please email me with any replies if you've had a similar situation, or have
a solution for this problem... Thank you very much in advance...

David Solovey



Sat, 29 Apr 2000 03:00:00 GMT  
 Crosstab Query/Conversion/Report Generation...

Hi David,

The field names (column headings) of the dynaset produced by executing a
crosstab query are dynamic.

In your situation the column heading grouping is "based on" data values of
the [FISCAL_YEAR] field. You have limited this with the criteria statement
In(1997,1996).
The resulting dynaset should look something like this:

Account    1996    1997
------------------------------
Cash        500        490
AR             12          15

The significant thing is that the resulting dynaset uses the "values" of the
"column grouping field" to produce new field names (column headings). The
report can't find FISCAL_YEAR because it does not exist in the resulting
dynaset. It only exists with the underlying query definition as the field
used to group the columns. Your report controls needs to refer to these
field names created by the crosstab. In your case they are [1996] and
[1997]. The report will recognize those names.
That is the quick fix.

However, on 12/31/97 the accounting year end will end ( assuming the fiscal
year is equal to the calendar year )
In January of 1998 the comparison needs to move forward to 1997 and 1998.
The report will error out again. This time it should a little friendlier
message saying something to the effect that it can't find [1996].
The controls on the report would be referencing [1996] and [1997] and the ct
query will be producing [1997] and [1998].

This is the problem using crosstab queries as record sources for reports.
Reports want absolute field name references back to the source, however, the
crosstab field names are dynamic.

Changing the FISCAL_YEAR column in the crosstab query definition to the
following expression will still group the columns but the column names are
set to fixed labels.

FISCAL_YEAR = iif([Year]=1996,"Year 1",iif([Year]=1997,"Year 2"))

Back on the report. Change the controls to reference [Year 1] and [Year 2]
and you the report will always run.

You also are not required to supply the where clause for the report to run.
It may produce 1995 and 1998 columns but the report is looking only for
[Year 1] and [Year 2]. These are determined from the expression.
Performance and optimization says "WHERE" the SQL. I'm just pointing out is
not required for the report and query to run. The FISCAL_YEAR expression
needs to be maintained.

The above does the job and is quick to incorporate into the existing
database design regardless of what it is.

What I prefer doing is to construct master tables that define periods. These
are low maintenance tables requiring editing once every few years. And it
can be done through user friendly forms. Accordingly,

Table [tbl Master Fiscal Periods] looks like (field types all integers):

[id Pd Fiscal]   [id Month Fiscal]    [id Year Fiscal]    [id Month
Calendar]    [id Year Calendar]
   199701                      10                        1997
1                          1998
   199702                      11                        1997
2                           1998
...

   199712                      9                          1998
12                         1998

Table [tbl Master Fiscal Year Defs] looks like:

[id Year]        [id Year Fiscal]
Year 1            1996
Year 2            1997
Year 3            1998
...

These tables get added to the original crosstab query definition.

Joining  [tbl Master Fiscal Year Defs]![id Year Fiscal] to [tbl Master
Fiscal Periods]![id Year Fiscal]

The catch is the structure of the table contain the GL Data to have field
[id Pd Fiscal] in.
I have incorporated this structure into my table(s) design.

Then
[tbl Master Fiscal Periods]![id Pd Fiscal] joins with [GL Data]![id Pd
Fiscal]

[id Year] becomes the field that the columns are grouped on.
A form passes user selected values back to the query the criteria or WHERE
clause of the crosstab.

The maintenance is reduced to the Fiscal Period Master Tables.

I hope this helps you. Provides some alternatives. And is not overkill.

Good Luck

Quote:

>I've created an app in access 95 for my accounting
>department... It works great... I can select criteria for
>one of many reports, and select a month, and two years
>for a financial statement (two years used for comparison)
>After the user comes up with his/her criteria, a group of
>functions creates a crosstab query with an sql statement i've
>created based on the criteria selected.  From here, I
>open a report with the newly created query... I've got everything
>working perfectly... Here's the catch... A few members in
>the department have just switched over to access 97...
>no problem... just open it up without converting it, and run
>it just as if I was using access 95... Well, the crosstab
>query gets created, and when it comes time to open the
>report, I get an error... It doesn't like the fact that one of my
>variables doesn't specify where it will be contained in the
>report... So I went into the query design, and it looks exactly
>like it did when i created it in acc 95... The problem I found
>comes when I use my where clause in the crosstab query...
>Access will automatically create a duplicate for any field that
>is found in the where clause... So in this case, I have one
>field in the query called FISCAL_YEAR which specifies
>that it will be shown as a column header in the query,
>and it has created another field called FISCAL_YEAR which has
>the totals part of the query set to Where, and it's criteria
>says: In (1997,1996).

>Field:         FISCAL_YEAR     |    someotherfield   |   FISCAL_YEAR
>Table:        MyTable                 |                                 |
>MyTable
>Total:         Group By                |    Expression         |   Where
>Crosstab:  Column Heading   |    Value                   |
>Sort:           Descending           |                                 |
>Criteria                                      |
>|    In(1997,1996)

>If I open the query, It works fine, and i get
>exactly what i'm looking for in both versions of access, but when
>It actually gets to the report part, It gives me an error saying
>it doesn't recognize FISCAL_YEAR as a valid field name or
>expression.  OK... So I go into the query design, and I see
>where access has created a new column for the where clause,
>and I delete that and put the In(1997,1996) under the FISCAL_YEAR
>that has column heading... so now the query looks like this...

>Field:         FISCAL_YEAR     |    someotherfield
>Table:        MyTable                 |
>Total:         Group By                |    Expression
>Crosstab:  Column Heading   |    Value
>Sort:           Descending           |                                 |
>Criteria      In(1997,1996)        |

>I open the report, and it works...
>OK, so that's the problem... so I look at the sql view to see how
>they have stated what i've just changed, to see If i can recreate
>the same thing i've just changed it to, and It looks exactly the
>same as the first instance... So I guess access 97 interprets the
>queries in different ways when it comes to reports... So my question
>is... Is there a way to specify in an sql statement how to not get it to
>create
>another column? How do i get it to not create another column for the where
>clause, and include it in the first FISCAL_YEAR column???

>This is the first real problem i've had in the conversion from 95 to 97
>Thank you very much for the time you've taken to read this...
>Please email me with any replies if you've had a similar situation, or have
>a solution for this problem... Thank you very much in advance...

>David Solovey




Sun, 14 May 2000 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. report based on a crosstab query

2. report based on a crosstab query

3. Crosstab Query for Report

4. dynamic report formatting from crosstab queries??

5. query regarding Crosstab report

6. multiple queries into a crosstab query in VB

7. multiple queries into a crosstab query in VB

8. Report generation takes a LOOOOONG time for 7-page report

9. Report generation takes a LOOOOONG time for 7-page report

10. Crystal Report : CrossTab Report

11. Feedback from a adodb.recordset object during query generation

12. VBA for crosstab query

 

 
Powered by phpBB® Forum Software