I apologise in advance for any poor forum etiquette or any general or specific database newbie dumbass-ery.
I am involved in centrally managing a project , a part of which involves employees of certain companies being given a weekly subsidy for study on academic courses.
Despite my newbie-ness (newbility?), I have successfully set up a table for all the learners and a related table for companies.
Each learner will receive a fixed weekly rate subsidy in the first year, and a lower fixed weekly rate subsidy in the second year. Somebody (not me) will need to check their paychecks to ensure that employees have been paid and all is above board. The learners/employees can start on the programme at any time. I need a way to track this (total claimed amounts, where they're up to etc).
My theoretical solution, is to have a field for each week a learner is on the programme (52 first year, 52 second), and instead of entering a currency amount in the field, the check-person can enter the date they saw the evidence and are happy with it. I thought then if I create a query that searches any dates that fall within the current claim period (eg >01/01/2015, <=Date()), and multiplies field count that fall within those parameters by first/second year subsidy rate if they’re in that table. I think my logic is sound, I’m just not sure how to write the query/expression, or if it's possible, or if there is a much simpler way to do it that I am too bone-headed to see.
I am new to databases and this kind of thinking in general, and I would be extremely grateful if any experienced Access hands could point me in the right direction or offer any advice at all (beyond "Get a different job, the one you have is over your head"). Thank you very much.
I am involved in centrally managing a project , a part of which involves employees of certain companies being given a weekly subsidy for study on academic courses.
Despite my newbie-ness (newbility?), I have successfully set up a table for all the learners and a related table for companies.
Each learner will receive a fixed weekly rate subsidy in the first year, and a lower fixed weekly rate subsidy in the second year. Somebody (not me) will need to check their paychecks to ensure that employees have been paid and all is above board. The learners/employees can start on the programme at any time. I need a way to track this (total claimed amounts, where they're up to etc).
My theoretical solution, is to have a field for each week a learner is on the programme (52 first year, 52 second), and instead of entering a currency amount in the field, the check-person can enter the date they saw the evidence and are happy with it. I thought then if I create a query that searches any dates that fall within the current claim period (eg >01/01/2015, <=Date()), and multiplies field count that fall within those parameters by first/second year subsidy rate if they’re in that table. I think my logic is sound, I’m just not sure how to write the query/expression, or if it's possible, or if there is a much simpler way to do it that I am too bone-headed to see.
I am new to databases and this kind of thinking in general, and I would be extremely grateful if any experienced Access hands could point me in the right direction or offer any advice at all (beyond "Get a different job, the one you have is over your head"). Thank you very much.
