How can I create/update record in Table1 depending on row in Table2?

Here is what I have

Table 1: (Accounts Activities)


Table 2: (Account 1)
TransActionID : Autonumber
TransactionType:
...
TransactionAmount:



what I am looking at is to create in Table1 a record everytime a record is created in Table2 and keep that record updated/linked to TransactionAmount of Table2

for example if I add in Table 2 an entry for $10 I want to create a corresponding record in Table1 linked to it and if that amount is updated in Table 2 the record in Table 1 shld follow

Is there anyway to do that in Access?

Thank you
LVL 50
AkhaterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pushpakumara MahagamageVPCommented:
AkhaterAuthor Commented:
@Pushpakumara thank you for the reply, I am well aware of the relationships as I use them already however it doesn't (or at least I can't see how) solve my issue
Pushpakumara MahagamageVPCommented:
Hi Akhater,

Do you need to duplicate a value on a field in table 2 with a Value on a field in table 1.

or when you insert/update value to table 2 and table 1 linked value has to insert/update corresponding another value.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AkhaterAuthor Commented:
No I do not need to duplicate, actually if it is possible not to duplicate but just link it would even be better but, as I just said, I wasn't able to think of a way to do it
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What's the reasoning behind this? As the other Experts have indicated, it sounds like you're storing duplicated data in Table1, which is not generally a good idea (or you're storing Calculated Values in Table1, which is also a bad idea).

Perhaps create a Query that gives you those calculated values?

There are Data Macros in later versions of Access (2010+, I believe) that allow you to trigger events when data actions occur in a data table (kind of like triggers on SQL Server), but - again - what you're asking to do sounds as if it would violate standard data normalization practices.
AkhaterAuthor Commented:
Hi Scott

thank you for your input, maybe you can indeed give me a better way to do it ?

what I want is to have one table/view (or whatever) that has consolidation "ledger" of all accounts

if I have Table1 for "Cash" account and Table2 for "Bank Account" for example

I just want one table that shows transactions of "Cash and Bank Account" consolidated in one with the "total balance" of both accounts...

Something like
ID1  Cash  (10)  990
ID2  Bank (90)   900

thank you
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need a Journal table. When a record from one of your Cash or Bank tables is "posted", you would add a separate entry to your Journal table. Entering a transaction and posting that transaction are two different things (at least in most accounting systems). If this were my system, I'd have a form where they could enter the transaction, and on that form would be a Post button. When the user clicks the Post button, you'd write a record in your Journal table that's related back to the parent table. From there, you can use a query on your Journal table to determine the status of your accounts.

Some accounting systems also include a "costing" table, which is a rollup of the Journal table for each Account + Fiscal Period. I personally think it's unnecessary (and violates normalization rules), but sometimes we err on the side of performance :). If you have millions of rows in your Journal table and must roll those rows up to parent accounts, it could take quite a while to produce reports and such - hence the need for that rollup Costing table.
Fabrice LambertConsultingCommented:
You can achieve that with data macros (also known as triggers).

Once properly coded, any change (add row / delete row / update row) in 1st table will be automatically reflected on 2nd table.
AkhaterAuthor Commented:
@Scott thanks again is there anything like a "journal table" or it is just another table you called "journal"?

@Fabrice, thank you for your input, any chance you could have any input/example/tutorial on how to accomplish this ?
Fabrice LambertConsultingCommented:
This maybe:
https://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/

Else, just google: ms Access Data Macro
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It's just a table named Journal or Journal_Entry.
AkhaterAuthor Commented:
@Fabrice well After Create / Update macros worked but the "After delete" isn't working, I guess by the time the macro runs the ID is already deleted so i can't look it up in the other table to delete it. and I am finding no way to pass variables between the "Before delete" macro and the "After delete" macro
AkhaterAuthor Commented:
Ok I used [old]. as in https://msdn.microsoft.com/en-us/library/office/ff836323.aspx?f=255&MSPPError=-2147217396 and it is now working all 3 of them

@Scott any disadvantages of using Macros ?

thanks
PatHartmanCommented:
It isn't necessary to duplicate the data.  I'm also not sure why you have multiple accounts tables.  It's been a while since I developed a general ledger application but by including an account number and an account type, you should be able to differentiate between Cash, Savings, Checking, etc accounts.  Wouldn't that simplify the process?

In any event, if you leave the data in multiple tables, you can use a Union query to simulate the ledger.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As I mentioned earlier (and in agreement with Pat), it seems redundant to duplicate data in this case. Use a Journal table, and "post" those entries instead. You can easily query that Journal table to determine the balances of all your Accounts.

I can sort of see the need for multiple tables, but not for individual accounts (like a Bank account versus a Cash account). In many cases, you'd have AR and AP tables (header and detail), Receipt tables, etc. Those tables would hold the details of your document, and the Journal table would hold information about the posting status and such. In a double entry system, you'd always have offsetting entries in the JE system (if you're creating an offsetting system, of course).

@Scott any disadvantages of using Macros ?
Not much control over the process, and no way to alert in the event the trigger doesn't happen. Plus, very, very few people are actually using these, so it's tough to get help with them.
AkhaterAuthor Commented:
First thank you all for you input and help, I finally dropped the macros and went with Pat's way in having them in one table and differentiate them with queries depending on the ID.

Cheers and enjoy your weekend
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.