Results 1 to 8 of 8
  1. #1
    123wouter is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    4

    Question Access making an form

    I have a school project and there is also an access part of it. I have to make a program that documents grades for a school. I made it in Access and I have to make some forms in it for an overview from the grades. I also need to calculate the avarage from each study. I want to do that in a tabel but that's not possible. So I made query's to calculate the avarage from each study. But I cant place those avarages in a form with tabel data. So I want a form or rapport for each student that shows their grades for each different study. I have uploaded the file from what I have made. All the names/numbers etc are random. I tried to upload it on the site, but there is a 500kb cap so I uploaded it to tinyupload: http://s000.tinyupload.com/index.php...11776431693024
    I'ts writen in dutch. If there are any questions please ask them and I will try to reply them.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    2MB zip file attachment allowed. Follow instructions at bottom of my post and file can be attached.

    Your db structure is not normalized.

    There are 3 tables with identical structure (Duits, Engels, Nederlands). This should be 1 table with another field to identify the 3 types (appears the Vaknummer field does that). Also, multiple Periode fields might not be optimal. If you have to add more periode fields, this means modifying table, queries, forms, reports, calcs.


    Build a report using its Sorting & Grouping features with aggregate calcs in textboxes in group and report header/footer sections.


    Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    123wouter is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    4
    Thanks for your reply! I understand the first part that the 3 tables should be one, I don't know why I didn't think about it.

    The Periode part I don't understand. It's basically every 1/4 of a school year you will get 1 grade, that's why I made 4 fields. And from those 4 parts I need the average.

    For that last part you suggest me to not calc in a query but in a rapport, so I don't need to select fields from table and query?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You certainly need the fields selected for the report but the aggregate calcs would be done in report. This is not the calc that averages the 4 fields. An aggregate calc is a calculation that acts on records - sum or average of all values in a field, example: Avg([Periode1]).

    If you will NEVER need more than 4 periods then this structure can probably be made to work.

    Be aware that arithmetic with Null returns Null. If any of the 4 fields is Null then the calculation will return Null. Need to handle possible nulls if you still want a number result even if any field is Null.

    Nz([Periode1,0) + Nz([Periode2,0) + Nz([Periode3,0) + Nz([Periode4,0)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    123wouter is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    4
    Thanks, I will work on it tomorrow and let you know.

    For the Null I made a otter solution, the Periode field I made a validation rule that the value must be between 0 and 10.

    Also I did for the average: avarage[Periode1]+ [Periode2] + [Periode3] + [Periode4])/4 because I didn't get avg to work, might look it to it tomorrow if I can get it to work with the avg syntax.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Avg() won't work for this expression. As I said, aggregate functions serve to aggregate records, not multiple fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    123wouter is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    4
    I have thinked about it and tried something out. The problem is when I put (Duits, Engels, Nederlands) together is that the field Leerlingnr have to be a duplicated field. When I do that it will be possible to give somebody more grades for the fields Duits, Engles and Nederlands, and that's something I want to prevent.

    I worked on the reports to make, but it's so complicated. I got to work the grouping on each student (Leerlingnr). I was happy that that worked for me. But the next thing is when I group on VakID I want to display those 3
    Duits, Engels, Nederlands sections from the field Vakken. It only shows the last section, and doesnt order the Periode correctly. Is this the problem with having multiple tables?

    Also when I make a report and group them, it only shows 3 studens (1,2 and 5). This is becouse only those 3 students are in all the 3 tables
    Duits, Engels, Nederlands. Is it easy possible to display everyone?

    And the Nz() function is really usefull, and makes it better. Thanks for that

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why would Leerlingnr field have to be duplicated - what do you mean by duplicated? What is this field for?

    It is possible to set up table to prevent duplicate entries for each student and subject combination. Set the two fields as a compound index no duplicates.

    As I said, best to combine the 3 tables. Otherwise, build report/subreport(s).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Making the switch from Access 03 to 13
    By Sarge, USMC in forum Access
    Replies: 3
    Last Post: 03-31-2014, 01:14 PM
  2. Making a simple access combobox and form
    By dukati7 in forum Access
    Replies: 2
    Last Post: 06-15-2012, 01:50 PM
  3. Replies: 3
    Last Post: 08-25-2011, 02:28 PM
  4. making expire ms access form..
    By alex_raju in forum Access
    Replies: 2
    Last Post: 07-13-2011, 11:27 AM
  5. Making the launched form seperate from Access
    By MonsterMaxx in forum Access
    Replies: 1
    Last Post: 09-06-2009, 12:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums