Results 1 to 8 of 8
  1. #1
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11

    Maths dependent on input


    Hi

    I am very new to this so please bear with me.

    I am wanting to calculate a field that will change based on input from the user.

    The fields are A) Maximum number of users.
    B) Remaining available slots.

    When a user completes the form, I want the available slots to reduce my one. I assume this will be on a button click?

    To add to this I will have potentially 50+ of these so it will need to reduce the correct field. I am defining these by a unique reference number e.g

    REF Course Max Users Remaining Users

    A23 Maths 8 7

    A33 Physics 19 11

    B11 English 11 11

    So what I would like ideally to happen is when someone for example selects course A33 (Physics) then the remaining user count will reduce from 11 to 10.

    Any help with this is much appreciated in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Saving calculated data, especially aggregate data (or in your case, decreasing value) is poor design. Enter transaction records (increases and decreases) and calculate net balance when needed. The concept is described in http://allenbrowne.com/AppInventory.html
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Saving calculated data, especially aggregate data (or in your case, decreasing value) is poor design.
    I don't see where it states that the calculation is to be stored, but it was a reasonable assumption. I'm going to assume that the course selections are being stored, so it could be as simple as the DSum or DCount of that field, subtracted from the course limit, as an expression in a form calculated field. However, what also raises a red flag is this
    To add to this I will have potentially 50+ of these so it will need to reduce the correct field
    I suspect these tables are designed like spreadsheets; i.e. there is a field for every course. If that is the case, it may be time to start over with the design.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Biggest clue to me that OP wanted to save calculation was stating there is a field for:
    B) Remaining available slots.

    I presumed they were talking about a table field.

    Last edited by June7; 09-21-2019 at 03:39 PM.
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Good catch!

  6. #6
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11

    No use

    Quote Originally Posted by RikApple View Post
    Hi

    I am very new to this so please bear with me.

    I am wanting to calculate a field that will change based on input from the user.

    The fields are A) Maximum number of users.
    B) Remaining available slots.

    When a user completes the form, I want the available slots to reduce my one. I assume this will be on a button click?

    To add to this I will have potentially 50+ of these so it will need to reduce the correct field. I am defining these by a unique reference number e.g


    REF Course Max Users Remaining Users

    A23 Maths 8 7

    A33 Physics 19 11

    B11 English 11 11

    So what I would like ideally to happen is when someone for example selects course A33 (Physics) then the remaining user count will reduce from 11 to 10.

    Any help with this is much appreciated in advance.

    I did start this tread by saying I am a novice at Access! The above answers seem to indicate I am fully conversant with Access which I am not. Let me rephrase this another way.

    Where would I do the math? In a query, or in the table? Yes I would like to save the calculation and yes it would be in a table field.

    Many thanks again

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Where would I do the math? In a query, or in the table?
    a query.
    Yes I would like to save the calculation and yes it would be in a table field.
    Why? In excel, yes you might do that, but this is access and it is not the way it is intended to work. Either way you still need to use a query to update the table

    Think about the full process - you are handling one scenario - student selects a course. Other things to consider include:
    What if a student subsequently changes their mind?
    How are you handling the situation where a course is full booked?
    Or there is only one place left and two students try to enrol at the same time?
    The tutor decides to leave and you have no replacement so need to cancel the course?
    etc

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Saving calculated value to table requires code (macro or VBA) and/or an UPDATE or INSERT action SQL.

    So, yes, in button Click event or a data control AfterUpdate event or form BeforeUpdate:

    Me!fieldname = something

    Nothing to prevent user repeatedly clicking or with edit of textbox causing same calc over and over which will make inaccurate data.

    Again, best way to assure accuracy of aggregate data is to base calcs on transaction 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.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-04-2014, 12:07 PM
  2. Replies: 6
    Last Post: 10-27-2014, 08:05 PM
  3. basic maths with time
    By merlin777 in forum Access
    Replies: 16
    Last Post: 10-20-2014, 04:19 PM
  4. Replies: 1
    Last Post: 06-16-2014, 03:25 PM
  5. Database of Maths Questions
    By akash2824 in forum Access
    Replies: 2
    Last Post: 05-01-2011, 06:05 AM

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