access form fields values that autofill based on others. (1 Viewer)

pandy

New member
Local time
Today, 13:33
Joined
Feb 9, 2021
Messages
3
Dear all,

I need your help with certain fields I have with my access database which I am struggling with. Firstly, I have to say I only learnt this over the last two months so apologies if I come across as a borderline novice to you. I would like my first term fees, 2nd term fees and third term fees to change based on what financial year I and Year/Class I choose. For example, if Financial year is 2019/2020 and I select Pre-Nursery, then 1st term fees would differ based on these values. Please find screenshot of what I am trying to work on. Certain fields are calculated fields such as Outstanding and total paid sections. Can anyone recommend an event under properties or a process to get this work as planned. I have screenshots of the table and the actual form on access. The reason for this is because different Year/Classes have different fees and fees change during different financial years. I am as basic as it comes or less when it comes to access, so please bare this in mind.​
 

Attachments

  • Table screenshot.png
    Table screenshot.png
    13.2 KB · Views: 545
  • Untitled.png
    Untitled.png
    795.1 KB · Views: 549

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:33
Joined
May 7, 2009
Messages
19,175
you need "another" table with "financial year" and "class" and the "fee" for that class.
your current table need "normalization"

what you need is the "ID" of the above table,
the ID of the student,
the date of payment
and the amount of payment.
 

pandy

New member
Local time
Today, 13:33
Joined
Feb 9, 2021
Messages
3
you need "another" table with "financial year" and "class" and the "fee" for that class.
your current table need "normalization"

what you need is the "ID" of the above table,
the ID of the student,
the date of payment
and the amount of payment.
Thank you for your reply. I should have mentioned that there are two tables on my access database. These cover two forms for student’s data and finances relating to each student. I have attached a pdf that shows images and explains all this
 

mike60smart

Registered User.
Local time
Today, 13:33
Joined
Aug 6, 2017
Messages
1,899
Thank you for your reply. I should have mentioned that there are two tables on my access database. These cover two forms for student’s data and finances relating to each student. I have attached a pdf that shows images and explains all this
Hi
Can you upload a zipped copy of your database?
 

pandy

New member
Local time
Today, 13:33
Joined
Feb 9, 2021
Messages
3
Hi
Can you upload a zipped copy of your database?
Please find attached zipped folder as requested. My main challenge at this time will be to Charllis forms data entry and ensuring I can get different Term fees based on the financial year I choose. In lay mans terms, If Financial year = x, and Year/Class=Y, Then 1st Term fees, 2nd Term Fees, Third Term fees would be what I want. I've learnt all I know in two months when I've got spare time, but I'm now stuck.
 

plog

Banishment Pending
Local time
Today, 08:33
Joined
May 11, 2011
Messages
11,613
You need to put aside this issue and work on the big problem--a poor table structure. I suggest you read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), work through a few tutorials, then give it a shot with your database. Once done, post a sample back here for us to review and talk through.

Here's the big issues you have with your tables:

1st and foremost-- You have "sets" of fields. You essentially have a bunch of duplicate fields in [FINACES ALTERNATIVE]--one set of fields for each term, that is wrong. When you feel the need to do this, its time for a new table with another field to hold what term the data is for.

2. Storing data in multiple places. In [FINANCES ALTERNATIVE] you have a [Studentid] field which is correct. But you also have [First name] and [Surname] fields which is incorrect. All you need in [FINANCES ALTERNATIVE] is that [Studentid]. With it you can link to [STUDENT TABLE] in a query and have access to all the related data in it.

3. Storing calculated values. In a database you store the the most granular data, if you need another piece of data built on something you have stored at a lower level, you don't store it as well. Instead you use a query and do the calculations there and reference that query when you need access to the calculations.

4. Spaces and special characters in names. Coding, querying and just communicating like this is more difficult when you use spaces in field names. Do yourself a favor and get rid of them. instead of [First name] use [FirstName], instead of [Year/Class] use [YearClass].

Again, put this issue you posted about aside and work on setting up your tables properly.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:33
Joined
Feb 19, 2002
Messages
42,981
I did a little clean up because I hate huge forms. I made a tabbed form so that each section of data is on its own tab. I also got rid of the popup for the main form but you can put it back if you prefer it that way.

The most important thing though is I normalized your payments table. This table will contain one row for each term for each payment. It takes some code to make this work well. For example, you need a button on the main form that lets you start a new year. That will automatically create three Fee records with the total amount due for the semester. In the form where you take payments, if the payment for the fee is insufficient, the code will create a second payment due record with the remaining amount for the semester. This gives you a little more flexibility so that people can make one or more payments but the final installment is due by the FeeDueDT.

That's just a start. There's lots of logic before you get this to work. However, I recommend strongly that you look at it to understand where we're going. A properly normalized schema makes the whole app much simpler and more robust and over time will save more effort than it takes.

So, just so you could move on, I modified the form with your existing Schema. To use the same style , you would use a subform on each of the term tabs and the query for that form would select just the rows for the term selected. Use Continuous or DS view for these subforms so you can see all the payment records for each term in a list.

You should also develop a more professional naming style. Do not use embedded spaces or special characters or all caps. I prefer CamelCase as you will see by the sample table I made. Others prefer the_underscore. Your preference but be consistent. Consistency will allow you to keep more of your hair because you won't be pulling it out in frustration as you try to remember what you called something :)

I changed the file name to avoid confusion.
 

Attachments

  • ACCESS DATABASE COPY_Pat.zip
    1.5 MB · Views: 512

Users who are viewing this thread

Top Bottom