calculate totals from 1 table & insert into another

[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

 

[Wayne Morgan]

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

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

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