J
J. Mullenbach
Hello,
I use an A2K database to track budget information for company projects. I
have one table that lists each project (project ID, project title, etc.) and
another table that contains approved budget amounts, along with the date that
each budget amount became effective. The two tables are in a one-to-many
relationship (the Project table being the "one" side).
I began the fiscal year with about 90 or so projects. At that point, there
was one Budget record for each Project record. As the year has progressed, I
have only added records to the Budget table when there was a change - so some
projects still only have one corresponding budget entry, while others may
have several entries.
The committee that decides on budget changes has met 4 times so far during
this fiscal year, so there will only be 5 possible budget dates (starting
budget + committee meeting dates). I'd like to create a columnar budget
history report showing the budgets for the three most recent approval dates
but I'm unsure how to filter the columns so that they list the most recently
approved budget amounts on or before each cut-off date.
Is there a better way to structure my data for this scenario?
I use an A2K database to track budget information for company projects. I
have one table that lists each project (project ID, project title, etc.) and
another table that contains approved budget amounts, along with the date that
each budget amount became effective. The two tables are in a one-to-many
relationship (the Project table being the "one" side).
I began the fiscal year with about 90 or so projects. At that point, there
was one Budget record for each Project record. As the year has progressed, I
have only added records to the Budget table when there was a change - so some
projects still only have one corresponding budget entry, while others may
have several entries.
The committee that decides on budget changes has met 4 times so far during
this fiscal year, so there will only be 5 possible budget dates (starting
budget + committee meeting dates). I'd like to create a columnar budget
history report showing the budgets for the three most recent approval dates
but I'm unsure how to filter the columns so that they list the most recently
approved budget amounts on or before each cut-off date.
Is there a better way to structure my data for this scenario?