Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: One input for all querries

    I was asked to create a database for a darts club.

    (Sorry this is so long but I think you need to know what I have done so you can tell me what I am doing wrong or what I need to do.)

    Two of the tables are called Member Details and PlayerStatistics (both have a relationship created because they both have PlayerRegistrationNumber.

    The PlayerStatistics table has in it the following fields:
    RoundNumber
    PlayerRegistrationNumber
    Score
    Darts
    180s/171s
    Pegs
    PegsOver100
    TeamName
    Versus
    Venue

    What happens is when ever a player plays a round the round and the scores are recorded.

    So PlayerStatistics will end up recording many different players scores for all the rounds.

    I was asked to create a report which gave me the top 3 players for each of the score fields mentioned above and gave me the average score for all rounds played (or if required to select out a specific range of rounds).
    You see the top three players for the Score Field can be different from the top three players for the Darts Field etc etc.

    So I created a querry which simply querried the PlayerStatistics Table as a whole but on the Round Field I had a criteria of Between [Enter start of round] And [Enter end of round]. I called this PlayerStatisticsDivision Querry and created a relationship between this querry and the MemberDetails Table.

    Next move was to create querries which combined the related table and querry (Member Details and PlayerStatisticsDivision Querry), which had TOP VALUES = 3 and had the following fields:
    Score Querry:
    PlayerRegistrationNumber
    Score (Total = AVERAGE Sort = DECENDING)
    MemberFirstName
    MemberSurname

    Darts Querry:
    PlayerRegistrationNumber
    Darts (Total = AVERAGE Sort = DECENDING)
    MemberFirstName
    MemberSurname

    180s/171s Querry:
    PlayerRegistrationNumber
    180s/171s (Total = AVERAGE Sort = DECENDING)
    MemberFirstName
    MemberSurname

    Pegs Querry:
    PlayerRegistrationNumber
    Pegs(Total = AVERAGE Sort = DECENDING)
    MemberFirstName
    MemberSurname

    PegsOver100 Querry:
    PlayerRegistrationNumber
    PegsOver100 (Total = AVERAGE Sort = DECENDING)
    MemberFirstName
    MemberSurname

    Now when ever I run each of the above querries it requests the [Enter start of round] And [Enter end of round] it gives me the top three players for each of the different scores with average totals over the period specified. GREAT!!!!

    Now, I am asked to join all the above querries into one report.

    So I created a report which is not bound to any table etc (Record Souce is blank). I call this the TopThreePlayerDetails Report. With the report open. I drag each of the querries into the report. This creates sub reports. GREAT again this all works!!

    When I run the report it asks for [Enter start of round] And [Enter end of round] for each of the subreports. Not a major problem but is a pain as you have to enter the same information 5 times.

    MY QUESTION IS:
    Is there a way of the report accepting the information once and applying it to all of the querries?


    Regards
    Karen Day
    kday@chariot.net.au

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Hi Karen

    Set up an unbound form. On this form have your start round and end round.

    Use these form fields as your five query criteria.

    Add a button to this form to open the reports, do not close the form.

    This should set the queries criteria to look for the form, and use the entry it finds as criteria.

    You could go one step further and make the start round and end round a combo box. This would eliminate input errors.

    In the on close event of the report, close the form.

  3. #3
    Join Date
    May 2002
    Posts
    157
    Hi there,

    Thanks heaps for trying to help me but I can't seem to get this to work.

    "Set up and unbound form."
    Easy done.

    "On this form have your start round and end round."
    This is the first area where I get a bit lost. Do you mean to create two unbound text boxes? (that is make no changes to their properties).

    If yes then I believe this is so when I open the form I will need to type in the first box the start of round number eg 1 and then I will need to type in the second bos the end of round number eg 10.

    If no then can you be more descriptive in how you want me to set up these boxes?


    "Use these form fields as your five query criteria".
    So am I using these two unbound text boxes to supply the information for all five reports?

    "Add a button to this form to open the reports, do not close the form"
    When I add a COMMAND BUTTON to the form I cannot see a way of opening the forms. So I assume that you want me to write a macro which opens all the forms. This I did. Then I added a COMMAND BUTTON which ran the macro.

    It is here where it does not seem to work. All it does is revert to
    asking for [Enter start of round] And [Enter end of round] for each of the subreports. So I still have to enter the same information 5 times.


    Dont forget my previous posting where I said:
    I created a querry which simply querried the PlayerStatistics Table as a whole but on the Round Field I had a criteria of Between [Enter start of round] And [Enter end of round]. I called this PlayerStatisticsDivision Querry and created a relationship between this querry and the MemberDetails Table.

    Next move was to create querries which combined the related table and querry (Member Details and PlayerStatisticsDivision Querry), which had TOP VALUES = 3. I then created the following 5 queries:
    Score Querry:
    Darts Querry:
    180s/171s Querry:
    PlayerRegistrationNumber
    Pegs Querry:
    PegsOver100 Querry:


    So I am a bit lost with your instructions - Can you assist me some more?

    Regards
    Karen Day

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    I'm sorry Karen, I thought from your post you were more familiar with Access than you are.

    Yes an unbound form is exactly that no recordsource at all. We will call it form3 for now.

    Likewise the two text boxes are unbound, although they do need to be named and we will call the start round txtStart and the end round txtEnd.

    The command button will point to our existing report.

    You need to modify the recordsource for the query underlying each of your five sub reports. Instead of having a parameter query with [Enter Start Round] as criteria we are going to swap this for the text boxes on form3.

    So delete the criteria you have under start round. Then either right click criteria and click build or press Ctrl & F2 this will open the query builder box. On the left hand side you will see a list of the various objects Access has available, select forms. This will drop down a list of all your forms, from this select form3. This will populate the centre box with all available fields in form3, choose txtStart by double clicking. This will place something like[Forms]![form3].[txtStart] into the top box.

    When you are done click ok and this will set the criteria for the report to the text box on the form.

    Repeat this process for all the reports and criteria. Probelm solvered.

  5. #5
    Join Date
    May 2002
    Posts
    157
    Hi there - I am sooooo happy - I can't believe how simple that was. Your wonderful for puting up with me. HUGE THANKS.

    I am self taught mostly by trial and error and also with the help of the forum now and then.

    I have created many databases for work (some quite complicated and I have often been asked by peers who have written many more databases than me - How did you do that?) - each one of mine seams to have it's own new thing I have to learn. So far I have been able to avoid using Builder but what you showed me was great.

    I immediately applied this example to a number of other reports.

    Thank you once again.

  6. #6
    Join Date
    Feb 2002
    Posts
    403
    Happy to help...Please consider some training in a formal environment.

Posting Permissions

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