access form fields values that autofill based on others.

[pandy]

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.​

 

[arnelgp]

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]

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]

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]

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]

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.