Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    7

    Unanswered: Help with my reports please

    Hi Everyone,

    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.

    Can anyone help?

    Many thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.

    IE

    =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.

    I hope this sets you on the right track
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2008
    Posts
    7
    hi there,

    first of all, many thanks for your reply.

    Couple of questions. You mention that first I need to sort out the repeating locations. I had noticed that this was ocurring but to be honest I have no idea why it is doing that. Any ideas?

    Second (more of a statement than a question I suppose), I presume that I can use the DCount example that you have used below in a text box in the report? Is that correct?

    Thanks

    Matt

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, that's correct.

    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

    Now that's GOTTA set you on the right path

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jun 2008
    Posts
    7

    Thanks StarTrekker

    Hi there,

    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!

    Again, many thanks Startrekker

    Matt

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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