Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Unanswered: Using an Update Query in Access 2007 to sum various fields

    Hello,

    I have built a table in Access 2007 that holds several records of students attendance and would like to use an update query to sum the hours they have attended. In summary, the students have 5 years to attend 35 hours in sessions of 3.5 or 7 hours. So, I have 10 fields showing what they attended and how many hours that session was. As some of the fields will remain empty for years I am aware that I need to use the NZ to get the expression to assume that the value is 0.

    I need help as to where I put the NZ reference and what to put in the "Update to" box in the update query. The field I want to update is [Total Attendance:] and the fields I would like to sum are:

    [Att 1 Duration:]
    [Att 2 Duration:]
    [Att 3 Duration:]
    [Att 4 Duration:]
    [Att 5 Duration:]
    [Att 6 Duration:]
    [Att 7 Duration:]
    [Att 8 Duration:]
    [Att 9 Duration:]
    [Att 10 Duration:]

    Thank you in anticipation!

    Emma

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the smart aleck answer is you don't store derived data unless there are performance or other reasons why you have to.

    the very fact that you have 10 columns recording attendance (called Att x Duration) suggests your design may be flawed

    BTW it will be smarter in future to name columns without space or repalce the space with an underscore

    as to how to use the NZ function try the help system or try ms access nz function in query - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use excel if you're dead set on that table structure. It will make your life easier.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What these guys are worrying about is First Normal Form

    You would be well advised to listen to them!

  5. #5
    Join Date
    Jun 2010
    Posts
    3
    Thank you for your responses.

    As I am new to building a database from scratch I now wonder what design I should be using to store this data. As I said, the students must complete 35 hours over a 5 year period to gain the qualification. As some of the sessions they attend are 3.5 hours long this could therefore mean 10 separate sessions.

    I have built a database with a main menu for all of the qualifications we offer so that the Director can look in one place for anything rather than having a separate spreadsheet for each one and out of date data. The other qualifications are thankfully not structured in the same way so I have everything built for them. It would be a shame to have a separate spreadsheet just for one qualification that holds a vast amount of students!

    Suggestions welcome - I have noted the comments in regards to field names not including spaces etc.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a session is a session, so I'd argue you don't need to identify 10 sessions, becuase you don't control the outside world
    for all you know someone may do 11 sessions, they may do one.

    instead I'd suggest you record a session as a discrete item in say an sessions table. you could have the persons ID as part of the primary key or just use an autogenerated number as the PK but have the person's ID as a foreign key. you'd also need say the date of the session and the time / duration of session

    then when it comes to finding how many hours someone has done within a 5 year period you ask the sessions table to SUM the hours for a specific personID for sessions within (say) 5 years of today.

    to do maths on the date you MUST store the date as a date/time value, use datediff/dateadd functions.

    your query then becomes something like
    select PersonID, SUM(SessionDuration) as TotalAttendance from MyTable
    having SessionDate >= DateAdd ("yyyy", -5, date())
    group by PersonID


    on the face of it I think you may perhaps need to rethink your overall design. it sounds liek you have developed something in Excel which probably should have been done in Access. Excel is great at analysing data, allowing users to manipulate data, its not very good at storing data, its nto very good at manipulating data into repeating patterns (such as periodic reports).

    however, what you could do is be a leetle bit sneaky
    export the data from Access into Excel... its a technique I've used before where senior people don't won't or can't use anythign excpet spreadsheets, so the data capture is done in Access (or a DB) its processed, validated and manipulated in Access/db layer and then pumped out to spreadsheets.

    for me Excle is a no no if you are doing data capture for basic information eg things like your attendance records
    why
    its easy to put data in the wrong place, its easy to mangle formula, its hard to spot such mistakes
    only one person can have the same spreadsheet open at the same time (for edit)
    its difficult to do comparisons say this year against last/last 5 years and so on.
    alos its very very tricky to have an audit trail which means you can fidn out who changed what bit of data and when. granted it may not be to much of a problem for your nbeeds but sometimes you do need to do the equaivalent of reverse Cluedo (so you know when, where and what was done to change the data
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2010
    Posts
    3
    Hi again,

    I really appreciate your input on this.

    The qualification in question is a legal requirement for all UK HGV drivers to achieve and we are obliged to log each session with the governing body after each session for the driver to achieve it. If they do not complete all 35 hours they will not be able to continue to drive for a living. So, unfortunately, we do need to break it down specifically into each session. It is a great idea to split this information onto a separate table as you suggested and I will use this to calculate the total hours.

    The company I work for is a very small company however, we have a vast amount of students and the numbers are growing due to the nature of it being compulsory training. The person who used to collate this data designed this in excel and passed it to me when I started designing the company database for the exact reasons you mentioned before. Ex staff members had all but destroyed its functionality, messed up formulas and did not keep the spreadsheet up to date.

    Hopefully I will now be able to get the end result I needed and thank you for the use of your brain :-)

    Emma
    Last edited by Stroughair; 06-12-10 at 04:08. Reason: error

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looking at the session table again
    I'd suggest the PK should probably be the person/candidate ID AND the session date/time...
    on the grounds that that is demonstrably unique, its a natural (real world) key.

    ..its protects against the same session data being recorded twice. you are still exposed if, say, the session time varies
    eg...
    driverID 1234 21/04/2010 14:00 session duration 3 hours
    driverID 1234 21/04/2010 15:00 session duration 2 hours
    ..you may need to put in a software procedure to check that you can't double record such entries. I'd do that as part of the data capture process, and because I'm an untrusting sneaky cynical... I'd run it as a periodic report (say weekly just to make certain the data is 'correct' before say submission to a government department)

    naturally if your sessions are fixed periods (say 3 hours AM or PM) then the recording becomes more trivial.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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