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:
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 (Total = AVERAGE Sort = DECENDING)
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?
Thanks heaps for trying to help me but I can't seem to get this to work.
"Set up and unbound form."
"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:
So I am a bit lost with your instructions - Can you assist me some more?
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.
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.