Avatar of iscivanomar
iscivanomar
Flag for United States of America asked on

How can I create a trigger in Access 2003

I need to move data in a table from one field to another.

Table                           Field1      Field2      Field3       Field4        Field5        Field6
BudgetActual Detail    Budget1  Actual1   Budget2  Actual2    Budget3    Actual3
                                    1000            0           0              0               0               0

                                             Period 1                Period 2                     Period 3

If we did not get any Actual1 in Period 1, the 1000 will be move to Budget2 which is in Period2

This will be a Trigger that will run every "First" and "Sixteenth" of the month.

Thank you.
Microsoft Access

Avatar of undefined
Last Comment
iscivanomar

8/22/2022 - Mon
SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
iscivanomar

ASKER
How can I do this with VBA code.
Scott McDaniel (EE MVE )

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:

tBudget
-------------------
lgBudgetID
stBudgetName
dtBudgetStartDate
dbBudgetAmount
etc etc

tBudgetActuals
--------------------
lgBudgetDetailID
lgBudgetID [Foreign key to tBudget]
dtPeriodDate
dbAmount
etc etc

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

Could you give me your opinion about this design.

Thank you.
relationship.bmp
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
iscivanomar

ASKER
I am doing a cash flow database.

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.

             
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
iscivanomar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.