Hi All,
Sorry this is a bit long, but please bear with me.
I’m new to Access, and I’m also new to the idea of a proper Relational Database.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
I designed and have operated/managed a sort of relational database run in Excel under VBA, and based on 1,000’s of text files for some 7 years now without any major issues.
<o></o>
Because now the number of contracts and therefore the number of transactions is so large, and therefore slowing down the process, I’m now trying to design a replacement in Access, and while I’m beginning to understand the structure of tables, and the principle of not un-necessarily storing the same data more than once. I’m having difficulties getting my head around how I maintain adequate historic data.
<o></o>
The project in question is a Contract Valuation System, which at headline extracts sales and cost transactions for each contract from our business system at the end of each month and evaluates the performance of each contract measured against estimates and Work In Progress. Also measuring the contracts performance Contract To Date (the contracts can run across financial years), Contract Year To Date (financial year to date), and Current Period.
<o></o>
I therefore need to maintain the contract results for each period. That in it’s self is ok, but for each contract, there are an ever increasing number of work packages, we’ll call them schedules, which make up the total result for each contract. Each schedule will comprise an estimate, Sales, and Costs.
<o></o>
When we get the new transactions for a period, we review them by contract, where appropriate a cost transaction is allocated to a schedule to which it relates, or, a new schedule is created, and the transaction is allocated to the new schedule.
<o></o>
Any cost transactions which have not yet been sold remain unallocated, and are treated as Work In Progress. The results from each schedule are totaled to give the contract result.
<o></o>
So far so good.
<o></o>
Inevitably, when reviewing the costs that have been previously allocated to a schedule, we find some cost transactions have been incorrectly allocated to the wrong schedule in a previous period. We then Un-Allocate the transaction(s), and re-allocate as appropriate.
Likewise, in the previous period there may have been a transaction which had been treated as Work In Progress, only subsequently found to be already sold, and when allocated, results in the Work In Progress reducing, without the sales going up, .i.e. a loss.
<o></o>
When at a later date Auditors carry out a review and see costs went down at some stage or reduce Work In Progress without increased sales, they want to see if it was the result of say a cost credit or cost has been removed etc, if removed, where was it moved to!! (Always suspicious Auditors)
<o></o>
Now here is my problem, I need to keep historic records of which Transactions were allocated to which schedules or as Work In Progress, in each historic period.
<o></o>
So for example say we are now in period 8, and there have been no new cost transactions on a particulate contract since period 6, but I can see that the Work In Progress has gone down, there is no increase in sales, and it’s made a loss.
<o></o>
I need to be able to go back and look at the transactions that made up the Work In Progress in the previous period to see what has been moved, and to where.
<o></o>
It was my initial idea to have a table for the Transactions which would include the contract number to identify which contract it belongs to, and, when allocated to a schedule, it would have the schedule number. If the schedule field is null, then it is Work In Progress.
<o></o>
However, with the scenario above, I have no way of knowing which transactions where Work In Progress in previous periods, or in which period a transaction was allocated to a schedule, or subsequently Un-Allocated, without, at the end of each valuation, taking a copy of the whole Transaction Table, and saving it as a period record.
<o></o>
In the seven years we’ve been running the current system, we currently have some
164K transaction records, along with 366k files and 8k folders in the Contracts Folder for 200 contracts.
<o></o>
<o></o>
Some guidance on this would be much appreciated.
<o></o>
Sorry this is a bit long, but please bear with me.
I’m new to Access, and I’m also new to the idea of a proper Relational Database.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>I designed and have operated/managed a sort of relational database run in Excel under VBA, and based on 1,000’s of text files for some 7 years now without any major issues.
<o
></o>Because now the number of contracts and therefore the number of transactions is so large, and therefore slowing down the process, I’m now trying to design a replacement in Access, and while I’m beginning to understand the structure of tables, and the principle of not un-necessarily storing the same data more than once. I’m having difficulties getting my head around how I maintain adequate historic data.
<o
></o>The project in question is a Contract Valuation System, which at headline extracts sales and cost transactions for each contract from our business system at the end of each month and evaluates the performance of each contract measured against estimates and Work In Progress. Also measuring the contracts performance Contract To Date (the contracts can run across financial years), Contract Year To Date (financial year to date), and Current Period.
<o
></o>I therefore need to maintain the contract results for each period. That in it’s self is ok, but for each contract, there are an ever increasing number of work packages, we’ll call them schedules, which make up the total result for each contract. Each schedule will comprise an estimate, Sales, and Costs.
<o
></o>When we get the new transactions for a period, we review them by contract, where appropriate a cost transaction is allocated to a schedule to which it relates, or, a new schedule is created, and the transaction is allocated to the new schedule.
<o
></o>Any cost transactions which have not yet been sold remain unallocated, and are treated as Work In Progress. The results from each schedule are totaled to give the contract result.
<o
></o>So far so good.
<o
></o>Inevitably, when reviewing the costs that have been previously allocated to a schedule, we find some cost transactions have been incorrectly allocated to the wrong schedule in a previous period. We then Un-Allocate the transaction(s), and re-allocate as appropriate.
Likewise, in the previous period there may have been a transaction which had been treated as Work In Progress, only subsequently found to be already sold, and when allocated, results in the Work In Progress reducing, without the sales going up, .i.e. a loss.
<o
></o>When at a later date Auditors carry out a review and see costs went down at some stage or reduce Work In Progress without increased sales, they want to see if it was the result of say a cost credit or cost has been removed etc, if removed, where was it moved to!! (Always suspicious Auditors)
<o
></o>Now here is my problem, I need to keep historic records of which Transactions were allocated to which schedules or as Work In Progress, in each historic period.
<o
></o>So for example say we are now in period 8, and there have been no new cost transactions on a particulate contract since period 6, but I can see that the Work In Progress has gone down, there is no increase in sales, and it’s made a loss.
<o
></o>I need to be able to go back and look at the transactions that made up the Work In Progress in the previous period to see what has been moved, and to where.
<o
></o>It was my initial idea to have a table for the Transactions which would include the contract number to identify which contract it belongs to, and, when allocated to a schedule, it would have the schedule number. If the schedule field is null, then it is Work In Progress.
<o
></o>However, with the scenario above, I have no way of knowing which transactions where Work In Progress in previous periods, or in which period a transaction was allocated to a schedule, or subsequently Un-Allocated, without, at the end of each valuation, taking a copy of the whole Transaction Table, and saving it as a period record.
<o
></o>In the seven years we’ve been running the current system, we currently have some
164K transaction records, along with 366k files and 8k folders in the Contracts Folder for 200 contracts.
<o
></o><o
></o>Some guidance on this would be much appreciated.
<o
></o>
