I have attached a database that I have created that will record scores for my league (yes I am a geek!)
If you look at the reports, there is one called report_bradford and one called report_shipley. What I would like to do is add a statistics box at the bottom of the report looking something like this:
Number of games at Shipley(or bradford, depending on the report) between 100 - 200 = X
Number of games at Shipley between 200 - 250 = X
Number of games at Shipley between 250 - 300 = X
Highest Game at Shipley= XX
Lowest Game at Shipley= XX
Highest Series at Shipley(this is from the total of the 3 games) = XX
Lowest Series at Shipley = XX
Each time I update the table, and then reopen the report I would want it to have updated the statistics.
I have been told that I need to do an aggregate query?
To be honest I am absolutely flumoxed with this, the original report is the edge of my database capabilities unfortunately.
I have spent ages looking around for something that I could use, tried doing loads of different queries, but to no avail. I hope that I have posted this in the correct place.
TBH, since it's only going to run the stats section once per report, I think what you should do is use a bunch of lookups to generate each value.
=DCount("ID", "Match_Details", "Game1 Between 100 and 200 And Location = 'Shipley'") + DCount("ID", "Match_Details", "Game2 Between 100 and 200 And Location = 'Shipley'") + DCount("ID", "Match_Details", "Game3 Between 100 and 200 And Location = 'Shipley'")
That will answer the first one.
IMO, you first need to deal with whatever is causing Shipley/Bradford to be repeated in your Location table. I would have, for example, Bradford being ID 1 and Shipley being ID 2. No more records are needed. Then you could use Location_ID = 2 to specify Shipley, instead of the "error" that I have used in this expression (Location = 'shipley') which simply won't work.
To correct the other situation, you need to remove the lookups at the table level -- set the Display Control to Text Box in the Lookup tab in table design for your foreign keys (Location_ID and Opponent_ID) in your match_details table.
Next, in the input_form, fix the combo box for Location. This combo is doing the wrong thing. It's entering data into the Location table instead of the match_details table.
Before you can do that you need to add your foreign keys to the underlying query of the form... and BTW a query called tblStatistics is really really confusing! A better name would have been qryMatchDetails.
These are the property changes to make to the combo box (and try using the general idea to fix your Opponent combo box too... it is also wrong).
Control Source: Location_ID Row Source Type: Table/Query Row Source: SELECT ID, Location FROM Location ORDER BY Location; Column Count: 2 Column Heads: No Column Widths: 0cm;4cm Bound Column: 1
It may have taken me a while (thats down to me being rubbish, no reflection on your help!) but I have finally done it! Tables all working properly, reports showing what they are meant to! I'm very very happy!