Results 1 to 15 of 15
  1. #1
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42

    Trying to build a class register

    Hi,

    I am trying to build a class register in a report.

    What I want is for the form to generate each weekly class datein field up to the term ending.

    I.e. If the class starts on 12/9/2015 and the term finishes on the last day of November;



    I want the report to create a field for each Saturday up to the last one before the last day of November.

    Is this possible,


    Ta

  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,825
    What do you mean by 'create a field'? Why would you be creating fields? Do you mean create records?
    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
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    I have kinda cracked it with this formula;

    Week2: IIf([StartDate]+7>=[HalfTerm] And [StartDate]+7<[HalfTerm]+7,[StartDate]+14,IIf([StartDate]+7<[EndTerm],[StartDate]+7,Null))

    I am building each day into the query now as that seemed smarter.

    New question. Is there a better way of doing the above formula? As it stands I will have to change [StartDate] to [Week2] and so on on each new field.

    I intend to do 20 odd so this seems a tad laborious.

  4. #4
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    Something like this?Sudents.pdf

  5. #5
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    Yeh basically

    Gonna have the dates across the top and checkboxes under. But yeh that concept

  6. #6
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    ok now that formula is making my DB crash.

    Any Ideas people??

  7. #7
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    I have now killed a few of the weeks and it says "Query is Too Complex."

    This is with 7 weeks. I need at least 15.

    Is there any way to deal with this??

    I basically need my query to check the date in the field to its left. Add 7 to it and check it is within the term and not in the half term. That's it.

    Help me June7 you're my only hope.

  8. #8
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    I put this in the Query Expression builder and it worked great.tblClassStarDateQuery.pdf
    Start Date: IIf([StartDate]+7>=[HalfTerm] And [StartDate]+7<[HalfTerm]+7,[StartDate]+14,IIf([StartDate]+7<[EndTerm],[StartDate]+7,Null))

  9. #9
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    Yeh i know, but when I put it in 15 times it crashes access.

  10. #10
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    I don't understand what you are saying that you put it in 15 times. what are you putting in and where?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I need better understanding of data structure and form/report design. If you want to provide db ...

    Think I have done something like this. I don't have each expression look to their neighbor - they all calculate from the StartDate. First calc is +7, next is +14 ...
    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.

  12. #12
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    Yeh I will attach dB in a bit. Thanks mate.

  13. #13
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    Have attached the DB.

    I see that normally it would be easier to calculate from the StartDate however I'm not sure how to calculate for Half term doing this.

    Please have a look and let me know what you think.


    Thanks
    Attached Files Attached Files

  14. #14
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    Sorry I missed this.

    I meant that I need to generate around 15 consecutive weeks. To do this i created a new field for each week and changed the calculation to refer to its left neighbour. I tried to do this in the query however this was crashing. I am gonna try and put it straight in to the report next. Have a look at my dB in the last post. Might make more sense.

  15. #15
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    I am still not clear on what you are trying to do. I downloaded you db and just copied and pasted your code in a new field in the query and just changed the weeks number and it runs fine for me. you already filled in the Half semester date it is a fixed date or is it different for each class with a different start date. if Different then the query field for Half date is [startDate]+49

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

Similar Threads

  1. Check Register Database
    By burrina in forum Sample Databases
    Replies: 12
    Last Post: 06-15-2014, 12:02 PM
  2. Using calculated value from previous register
    By eduardo10 in forum Access
    Replies: 2
    Last Post: 08-19-2011, 12:09 PM
  3. Duplicate Register
    By maluna in forum Access
    Replies: 1
    Last Post: 09-28-2010, 12:45 AM
  4. Attendency register in a database
    By pp100 in forum Access
    Replies: 2
    Last Post: 08-31-2010, 06:51 AM
  5. Login form register
    By isnpms in forum Security
    Replies: 1
    Last Post: 08-01-2010, 10:30 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