S
spence
Let me apologize for the length of this post but I wanted to provide as much
clarifying information as possible.
I work for a non-profit that helps adults with disabilities manage funds
allocated to them to purchase supports they need to live independently. I'm
trying to create a budget countdown form to track these expenditures and am
in over my head. Here’s the scenario:
I have the following tables:
TblCustomer (contains customer demographic information)
TblPlan (contains plan start and end dates for customers’ annual plans; each
customer starts a new plan each year but old plan data must be maintained)
TblAllocation (contains the budgets amounts per category for each plan in
the system)
TblInvoice (contains the invoices charged against a plan)
TblServiceCode (contains the service categories reflecting the plan
allocation and used to code invoices)
I’ve posted a screenshot of my relationships here because it seems much
easier than trying to describe them:
http://home.comcast.net/~r.j.spence/relationships.html
A customer starts a new plan each year and each plan has a corresponding
budget allocation tied to it. Customer plan years begin on the first day of
the month the customer enrolls and so are not tied to the calendar or fiscal
year. Plan duration is always 365 days. (In other words, theoretically one
twelfth of my plans start on Jan 1st, one twelfth on Feb. 1st, etc.)
The allocation is broken out in different billable categories called Service
Codes. The invoices are coded with these service codes. Additionally each
invoice has a start and end date denoting when services occurred. The goal is
to allow tracking of how much money has been spent in any plan in the system
1. in a given service code and 2. in total. All this would be based on the
dates of the invoices and the start and end date of the plan.
So. I need to figure out how to create a form that will show allocated
amounts, spent amounts, and remaining amounts *per service code* and for the
entire plan allocation. There is always a period when any given plan year
renews that the expenditures and budget allocations for both the old plan and
the new plan need to be tracked, so I need to be able to toggle between these
plan years. (The invoices are not directly tied to plans but the dates on
them should enable me to filter them and attach them to the correct plan
year.) The only piece I’ve accomplished is building a form/subform that
allows me to toggle between plan years and shows the plan allocation by
service code. (See above link for screenshot.) The countdown is what has me
stumped.
I’m not expecting a simple or tidy fix for this but after fooling with it
all week and getting nowhere fast, I thought maybe I’d see if anyone here has
some suggestions or ideas on how to approach this and/or resources I might
access to assist me.
Thanks so much,
spence
clarifying information as possible.
I work for a non-profit that helps adults with disabilities manage funds
allocated to them to purchase supports they need to live independently. I'm
trying to create a budget countdown form to track these expenditures and am
in over my head. Here’s the scenario:
I have the following tables:
TblCustomer (contains customer demographic information)
TblPlan (contains plan start and end dates for customers’ annual plans; each
customer starts a new plan each year but old plan data must be maintained)
TblAllocation (contains the budgets amounts per category for each plan in
the system)
TblInvoice (contains the invoices charged against a plan)
TblServiceCode (contains the service categories reflecting the plan
allocation and used to code invoices)
I’ve posted a screenshot of my relationships here because it seems much
easier than trying to describe them:
http://home.comcast.net/~r.j.spence/relationships.html
A customer starts a new plan each year and each plan has a corresponding
budget allocation tied to it. Customer plan years begin on the first day of
the month the customer enrolls and so are not tied to the calendar or fiscal
year. Plan duration is always 365 days. (In other words, theoretically one
twelfth of my plans start on Jan 1st, one twelfth on Feb. 1st, etc.)
The allocation is broken out in different billable categories called Service
Codes. The invoices are coded with these service codes. Additionally each
invoice has a start and end date denoting when services occurred. The goal is
to allow tracking of how much money has been spent in any plan in the system
1. in a given service code and 2. in total. All this would be based on the
dates of the invoices and the start and end date of the plan.
So. I need to figure out how to create a form that will show allocated
amounts, spent amounts, and remaining amounts *per service code* and for the
entire plan allocation. There is always a period when any given plan year
renews that the expenditures and budget allocations for both the old plan and
the new plan need to be tracked, so I need to be able to toggle between these
plan years. (The invoices are not directly tied to plans but the dates on
them should enable me to filter them and attach them to the correct plan
year.) The only piece I’ve accomplished is building a form/subform that
allows me to toggle between plan years and shows the plan allocation by
service code. (See above link for screenshot.) The countdown is what has me
stumped.
I’m not expecting a simple or tidy fix for this but after fooling with it
all week and getting nowhere fast, I thought maybe I’d see if anyone here has
some suggestions or ideas on how to approach this and/or resources I might
access to assist me.
Thanks so much,
spence