Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Question Unanswered: How to use three crosstable querys in one form/report

    Hello,

    How can i use three different crosstable query's in one form/report.

    I crosstable query:
    MISTAKES BY WORK
    WEEK | TOTAL OF SUM | WORK1 | WORK2 | WORK3 | WORK 4 |
    32 | 79 | 9 | 60 | 0 | 10 |

    II crosstable query:
    MISTAKE TYPES
    WEEK | TOTAL OF SUM | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
    32 | 79 | 0 | 0 | 2 | 10 | 6 | 7 | 8 | 5 | 8 | 10 |

    III crosstable query:
    MANUFACTURING
    WEEK | TOTAL OF SUM | WORK1 | WORK2 | WORK3 | WORK4 | WORK5 |
    32 | 576 | 73 | 141 | 30 | 225 | 107 |

    I need one form where i can select week and then in form or report shows me only this week MISTAKES BY WORK, MISTAKE TYPES and MANUFACTURING.

    I have dried to use subform but, no use. Subforms dosnt cange when i selecting week.

    Please help me....

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Subforms are the way to go.

    There's at least a couple of ways of doing this I think but I find this method fairly straight forward...

    If you launch your report from a button on a form, then add a text box to your form where the user has to put the required week number. Then each of your queries can use the value in the text box as part of the criteria for the query i.e. each query will automatically return only the required weeks to the report.

    You can also use the same text box on the form to add a dynamic heading to your report. e.g. "Report for week " & Forms!myForm!myChosenWeekBox

    If you are launching your report from the report list rather than from a form then you will have to run some code in the OnOpen event to grab the week number and store it somewhere e.g. in a table. Then use Dlookup in the criteria for your queries to get the week number form the table.

    If you are handy with VBA then you can create your crosstab queries on the fly negating the need to store variables.

    hth
    Chris

  3. #3
    Join Date
    Aug 2006
    Posts
    2
    I have tried top use combo box, where are week number, but query dosn't work. Gives me error, but what gind i can't tell right now. I dosn't have the database in this computer. I will tell it tomorrow.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Janar

    Welcome to the forum

    When you refer to controls in crosstabs you need to explicitly define your parameters - Like this:
    http://support.microsoft.com/kb/209778/en-us
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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