Before you travel along this path, I'd urge you to review the design of your tables. Anytime you see columns named like "Budget1", "Budget2" etc then you can be sure your tables are NOT normalized; they're basically a spreadsheet in Access, and Access doesn't handle spreadsheets well. Access prefers "tall" tables rather than "wide" - that is, your tables should look like this:
From there, you'd have 3 records in tBudget, and 3 in tBudgetActuals. If you have more than one "period" in a Budget, then you would add more records to tBudgetDetails, of course.
If you tell us more about your actual program, and what it is supposed to do, we can certainly help further with normalizing your tables.
iscivanomar
ASKER
Sorry for getting back to you until now, I have a busy week and I could not get back to this project until today.
I am sending you an image with the relationship of the database.
The table Cash_Flow_Summary contains all period until 24 plus Adjustment and Total.
You only will be able to see until Budget_Period19
We want to know how much money do we have everyday and if we have to get a loan or if we have enough money to pay the loans that we already have.
Thank you.
Scott McDaniel (EE MVE )
You structure is definitely not normalizes; anytime you see columns which are numbered (i.e. Budget_Period2, Actual_Period5, etc) then it's almost certain that your data isn't being stored properly.
I'm not sure exactly what you mean by "cash flow database", but if you're just trying to track transactions, then a simple Header/Detail setup (with appropriate lookups) should suffice.
If you could define your major Objects in the databse, it might help:
Funder
Project
Contract
Budget
Payment
Account
What do each of those represent? Obviously, I know what a Payment is, but how does it figure into your overall business model, where do you get them, what do you do with them, what are they in payment of, etc etc ... that's the sort of info I need for ALL of the objects, along with an overall summary of what your db is supposed to track. Only then can we really discuss your structure.
iscivanomar
ASKER
The major object are:
Payment Table: This table has everyday transactions that contain how much money came for each contract.
Contract: This table has all the information for the contract such as Amount, Contract Start Date, End Date, Contract ID, and Contract Description.
Cash Flow Summary contains 24 periods with the Adjusted Budget and Actuals(Actuals are the payments).
What I am trying to do is to find how much money we have at the end of each period and find out if we need to barrow money.
i.e. We got 200 of payments(Actual) and we predict 300 which is the Budget.
This will mean that we need 100 from the line of credit.
What I am trying to find in this question is: how can I sent a quantity from one period to the next period every 1st and 16th of the month.
The table is cash_Flow_Summary.
For instance, today is April 1st 8:00 am and all the quantities in the field Budget_Period18 should move to and add to Budget_Period19.
Thank you and sorry for getting so late to this question.