Results 1 to 9 of 9

Thread: Crosstab Query

  1. #1
    Join Date
    Oct 2007
    Posts
    22

    Unanswered: Crosstab Query

    Hi,

    I have a crosstab query, which fields change everytime I run a different command on the switchboard. There is nothing wrong with this. It has to change, since the commands run different queries, which retrieve different data.

    I need this crosstab query as a report. Because the fields of the crosstab query change, I cannot create a report, because fields are missing (new ones add up).
    What do I have to do, in order for the report to recognize each field from the crosstab query. Or is the another dynamic way of showing the data in the crosstab query.
    The crosstab query is based on a query, which results change with the selection of the user at the beginning.
    Please advise.

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Look at "DemoCrostabMonthA2000.mdb" (attachment).
    There you have got 2 ways.
    1) Table1, Query1Crostab, Report1Crostab.
    (important; dummy records in Table1, VBA in Report).
    2) Table2, Query2Crostab, Report2Crostab.
    (important; COLUMN HEADINGS properties in query).
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One problem with crosstabs in reports is that you when you design the report and set up the subreport on a crosstab, Access creates the subreport with the headers all set up so adding a new row to the table which results in a new column for the crosstab, means manually editing the subreport and adding in the column. This can be a pain to maintain.

    There's a little trick you can do so you don't have to continually add in new columns to the subreport. Design your main report and add in the subreport. Cancel the wizard to automatically create a subreport!! Then set the SourceOjbect of your subreport to the crosstab query (not another report.) Keep in mind that you'll need to size the subreport (again - sourceobject based on the crosstab query - NOT another subreport) so you can support new columns which are added when you insert new rows into the table.

    Also note that adjusting the column widths in the crosstab query (and saving the crosstab query with the new column widths) will affect how it shows on the report. So if the column widths are too wide on the report, adjust them in the query and woola, they are also adjusted on the report.

    Hence, the SourceObject of your subreport will have something like: Query.Query1Crostab verses Report.Report1Crostab
    Last edited by pkstormy; 10-31-07 at 12:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Oct 2007
    Posts
    22
    Thanks for the answer.

    The problem with the report is also that I have huge query output that it actually does not look very good in a report. Could I send you the file and you could take a look at it?

    deb

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sure. Upload it to this set of threads.
    .
    .
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Oct 2007
    Posts
    22
    There is very confidential information about the company. And all the queries are connected to each other. Let me see how I can put it together.

  7. #7
    Join Date
    Oct 2007
    Posts
    22
    I have deleted the information, which is critical. I also deleted the main switchboard. IN the main switchboard the user makes a selection and then everything is updated and adjusted. It is not possible to see it in this file.

    The query is called: Query1aa_All_Entities. These query changes field in it columns and heading. Sometimes new ones are added, or deleted. Based on the selection.

    In the table CoGroups you find the list of all fields which could be in the query. If you have any idea, it would make me really happy.
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    ok...what do you want to accomplish? If you want to do a report on the crosstab query, simply design a new report, add in a subreport (cancel the wizard), and then for the sourceobject of the subreport select the query: Query1aa_All_Entries. When you say you're missing fields or sometimes new ones are added or deleted, I don't know what you mean. The crosstab is based on the Query1ab which is based on a table and IF the table is set up correctly, you shouldn't have an issue with the crosstab. Keep in mind that I don't know this data so please try to explain what fields seem to be missing or new ones added.

    Also, you do realize that if Company No 1 or Company No 2 ever have a value of 00001, 00005, 00020 or 99999, since these values are duplicated in your CoGroup table, your Dlookups in the query may or may not return the right values.

    I would highly suggest you set up some relationships between the tables on the fields as there looks to be commonality between several fields in the tables.

    Query1ab does not need to be a summization query. You can click the E to remove the summary from the query as you are not really grouping/summing.
    Last edited by pkstormy; 10-31-07 at 13:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Deb,

    See Query1abNEW in this example which is a better way of doing the query verses the dlookups.

    Once you give me more info on the crosstab, I'll see what I can do for you on that.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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