Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Access 2007 calculating a total for a field using a query - summary type

    Hi there - this is driving me mad and I don't know what else to try.
    I am working in Access 2007 and have a number of different tables. Of these, I have 2 tables joined together by a one to many relationship. In the first table, I have a number field called (called CreditsAwarded) which looks up data from the second table. The data type in the second table (called UnitCredits) is Number, Double. This works fine, and the data is displayed in the first table accurately.

    All I want to do is to total the amount for each record in the CreditsAwarded field in the first table. To do this I have created a Query using the Query Wizard and have selected the Summary option. When I do this, the Sum/Avg etc options are 'greyed out' and I only have the option to go to the next step of the Wizard. When I run the Query, it Counts the number of times an individual has been awarded Credits, not totalled it.

    I tried changing how the data was entered so instead of a LookUp field using the second table, I used a Lookup field using the 'type my own list' option in the Lookup Wizard. This time, when I created the Query it worked and totalled the CreditsAwarded.

    So you may be asking, well what is wrong with doing it that way as it works! Well, the second table has fields that are also used as look up fields in other tables, and I really don't want to keep entering lists for several fields.

    Can anyone help me with this please? Thanks!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Linda,

    Welcome to the lounge as a member. Always nice to have new blood.

    In a summary query you need at least 2 fields, at lease one groupby and at least one to sum.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lindaloo84 (2011-08-12)

  4. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Linda

    Can you post an example db showing your problem. Just the bare minimum of tables and data. The db needs to be zipped to post it here.

    Lookup fields often cause a lot of confusion because Access likes to hide what is really going on.
    Experienced Access users try to avoid using them. The tables are created with just the data type required, and the lookups are created at the form level rather than the table level. When you look at the raw data in the tables you see what is really there.
    Regards
    John



  5. The Following User Says Thank You to johnhutchison For This Useful Post:

    Lindaloo84 (2011-08-12)

  6. #4
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Thanks - example db attached

    Hi John - example database is attached with minimum tables and fields as requested. As you can probably gather, I have very basic Access knowledge and appreciate any help you can offer.
    Linda
    Attached Files Attached Files

  7. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The Credits field in Assessment Records is actually a text field rather than a number, but hiding this from you, so you can't add it.

    If you knew that the Credits for a Unit would never change, you would not need a Credits field in Assessments. You could get this info from the Units table, but the Credits may change over time. So the Credits a person gets for a unit should be (I think) the Credits it had at the time they did it.

    In the attached example I have changed the tables a bit, and created a form with a subform for adding Assessment records to people. This uses a little bit of code to copy the Credits from the Unit table to the Assessment table when a Unit is chosen.

    For Calculating Credits per person you want to sum them. I also have assumed that the Credits only count if the unit has been passed.

    totalcredits.gif
    Attached Files Attached Files
    Regards
    John



  8. The Following User Says Thank You to johnhutchison For This Useful Post:

    Lindaloo84 (2011-08-13)

  9. #6
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks so much John. I can see what you've done and, most importantly, understand what to do now. You have assumed correctly regarding the Credits a person earns and so now I will apply this to my own database.

    Thanks again for your very prompt reply.

    Linda

Posting Permissions

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