Best Way to update a related many side table.

[abujafar]

I have a Salary table(The one side) and a Deductions table(The Many side).

Whenever the salary is entered I want to calculate and store the deductions in the deductions table.

I know calculations should be done in queries but I want to store the calculated deductions because the tax system changes pretty often so I want individual tax calculations for each paycheck to be stored.

I can write code use macros and design queries.

I am simply looking for suggestions on the most efficient way to do this.

e.g. should I build queries on the fly in VBA, should I build insert queries in design view and let then update the deductions table using values in the salary form.

should i make create macros that call the insert queries as soon as I update the salary table.

I am using Access 2007

All suggestions will be appreciated.

 

[the_net_2.0]

How are you calculating deductions and where are you getting them from? IRS tables? I would suggest that instead of using Access to do this work you purchase either a Quickbooks license or maybe Microsoft Money?

Quickbooks definately does this better than Access. The reason why Access is such a pain when it comes to this sort of stuff is because it doesn't have any ODBC links to other sources like, for example, Intuit. Not that I know of anyway...

Wouldn't it be worth the time to switch to a program like that? The cost of it might be less than the value of time you're going to spend writing your own solution with Access, even though that's obviously a free program.

 

[abujafar]

How are you calculating deductions and where are you getting them from? IRS tables? I would suggest that instead of using Access to do this work you purchase either a Quickbooks license or maybe Microsoft Money?

Quickbooks definately does this better than Access. The reason why Access is such a pain when it comes to this sort of stuff is because it doesn't have any ODBC links to other sources like, for example, Intuit. Not that I know of anyway...

Wouldn't it be worth the time to switch to a program like that? The cost of it might be less than the value of time you're going to spend writing your own solution with Access, even though that's obviously a free program.

Its for a small company that I work for and it is not in the US, So the tax calculations are different. They requested for me to do it in Access.

 

[John Big Booty]

the_net_2.0's advise is pretty good, there some good reasonably priced accounting packages out there in the market, you are really making a rod for your own back if you are trying to re-invent the wheel. There would have to be some very compelling reasons to go down this path, and you would want to have a very good knowledge of accounting processes and practices, as well as Access. Given your question I would suggest you have failed to tick at least one of those boxes.

 

[the_net_2.0]

Its for a small company that I work for and it is not in the US, So the tax calculations are different. They requested for me to do it in Access.

abu,

Part of being a creative business person is having the ability to explain away other peoples' perceptions. It doesn't matter what country you're in...Access is not the program to do this with. Unless you're in the military, I would bet that the risk is very low that you'll be terminated for explaining away your boss's perception for using Access. I can guarantee you that if you comply with the demand, and your boss is interested in making money, there's a very good chance that you will look like a fool telling him/her that you can do it in Access because down the road you'll start wondering why you're spending 5 hours on every single task trying to make it work.

This is a common mistake by people working for others. I understand that complying with a request is natural because you want to satisfy the powers that be, but it's a complete bust in the future. But if you're only going to be with the company for a few months, then it's a different story in terms of your job security. But again, only worrying about yourself doesn't project a good image to anyone.

That's some friendly advice from someone who's been there and done that.

 

[boblarson]

I agree - sometimes the wise thing to do is to present to the boss the actual cost figure estimates. So, let's say your boss wants to have you create this app. Well, for a very experienced developer my "guesstimate" (in order for it to be right and with all testing) to build it would be at least 2 months full-time work. Now, figure out that you are not-so-experienced and you multiply that by quite a bit.

But for the sake of argument, lets say you can get it done in 2 months of full-time work. And let's say that you make $20 per hour as a general amount.

2 months - 320 hours (at 40 hours a week)

$320 * $20 = $6400

So, now you could go spend $199 on the Quickbooks Pro 2011 and be done in hours.

So, sometimes the financial reality needs to set in for them to realize what they're doing.