calculate totals from 1 table & insert into another

  • Thread starter Thread starter Kristine Nguyen
  • Start date Start date
K

Kristine Nguyen

Hi,

I have an append query to calculate the weekly totals and taxes which would
do the job for me, however, since Access does not round correctly, I need to
go through each record to calculate and use my own rounding function and
insert into the payh table. Here's the query:

INSERT INTO payh ( emp_id, week_ending, gross, ss, med, net_pay )
SELECT [payd].[emp_id] , [payd].[week_ending] AS Expr2, Sum([payd].[total])
AS gross, round(([gross]*0.062),2) AS ss, round(([gross]*0.0145),2) AS med,
([gross]-[ss]-[med]) AS net_pay
FROM payd
WHERE (([payd].[create_payh_flg])='N'))
GROUP BY [payd].[emp_id], [payd].[week_ending]

Because the round function in Access does not round correctly (round .5 up),
I would need to go through each of the sum of amount above to calculate the
ss, med, & net_pay fields. The question is, how do I accomplish the same
thing above by going through each records? I'm not very familiar with VBA
records. Help is appreciated.

Thanks,
Kristine
 
Is "round" your function or the one in Access? If it is your function, I
recommend changing the name to avoid confusion. Perhaps call it "MyRound".

You should be able to round off the sum, just as you are the other
computations.
Sum([payd].[total]) AS gross
Click to expand...
MyRound(Sum([payd].[total])) AS gross

If you don't have your own rounding function, then open a code module. If
you don't have one, create a new one (in the Modules tab of the database
window) and give it a name such as basMyFunctions. In the module, create a
Public Function called MyRound.

Public Function MyRound(curInput As Currency) As Currency
'This will round to 2 decimals
Dim curOutput As Currency
curOutput = Int(curInput * 100 + 0.5) / 100
MyRound = curOutput
End Function

--
Wayne Morgan
MS Access MVP


Kristine Nguyen said:
Hi,

I have an append query to calculate the weekly totals and taxes which would
do the job for me, however, since Access does not round correctly, I need to
go through each record to calculate and use my own rounding function and
insert into the payh table. Here's the query:

INSERT INTO payh ( emp_id, week_ending, gross, ss, med, net_pay )
SELECT [payd].[emp_id] , [payd].[week_ending] AS Expr2, Sum([payd].[total])
AS gross, round(([gross]*0.062),2) AS ss, round(([gross]*0.0145),2) AS med,
([gross]-[ss]-[med]) AS net_pay
FROM payd
WHERE (([payd].[create_payh_flg])='N'))
GROUP BY [payd].[emp_id], [payd].[week_ending]

Because the round function in Access does not round correctly (round .5 up),
I would need to go through each of the sum of amount above to calculate the
ss, med, & net_pay fields. The question is, how do I accomplish the same
thing above by going through each records? I'm not very familiar with VBA
records. Help is appreciated.

Thanks,
Kristine
Click to expand...
 
Back
Top