Results 1 to 6 of 6
  1. #1
    grahamluckhurst
    Guest

    Using Crosstab queries in Reports (Access 97)

    A form supplies a pair of parameters to a crosstab query. The query work fine. The only problem is using this query as a data set for a report. It appears that as the number of columns vary, the report will not or can not accept this.

    Has anyone managed to get round this?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Crosstab queries in Reports (Access 97)

    You get around this by creating a set of fixed headings for your crosstab query. For example, if your query returns data in columns by month, you might put something like "Jan","Feb","Mar",..."Dec" in the column headings property of your crosstab query (I dropped some months here for brevity). Then even if there is no data for the month of October, you'll still get a column for it and you can count on it appearing on your report as an empty column.
    Charlotte

  3. #3
    grahamluckhurst
    Guest

    Re: Using Crosstab queries in Reports (Access 97)

    Thanks Charlotte. This has solved half my problem. What remains is that the number of columns eary at both ends of the data. So with one set of data the column heading may be 1980 to 1995 and different parameters can be just 2000 by its self. Also new data is being added all the time, we'll soon be adding 2001 data.

    Is there a way of not setting the column headings property and reading the column heading in the query itself. (could this be done with another query?)

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Crosstab queries in Reports (Access 97)

    Careful Charlotte at least for the non-english versions.

    Many years ago I did exactly this with the short month names on a German system. Microsoft changed the short form of March from one version of Windows to the next. It took a while to find out why nothing was ever sold/delivered in March!
    Now I always use a coding such as Mnth_01 to Mnth_12 and providet the short form of the month on the report directly.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Crosstab queries in Reports (Access 97)

    Thanks for the tip. I only work with the English version and so haven't encountered any problems. In this case, I was using the month abbreviations as illustrations since they're easy to understand.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Crosstab queries in Reports (Access 97)

    Take a look at the solutions.mdb that comes with Access 97 (Solutions9.mdb with Access 2000). The EmployeeSales report in that database uses dynamic column headings.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •