INSERT INTO with summation and DSUM difficulties

[ddohnggo]

hello all,

i stumbled upon this site via google while i was trying to find a solution to my problems! i'm a pretty proficient pl/sql writer and this is my first time trying to learn jet sql so a lot of the things i'm used to in pl/sql can't be done in jet sql!

1) i'm trying to create a summary record from RR_FT_COMPONENT to RR_FT_TOT_PAYOUT. this is basically summing some payout fields by grouping by payee_id and period_id. from my research i've seen that jet sql doesn't like sum or group by's for updates. is it the same for doing select statements for insert into?

when i run the query i get the following error: "ms access can't append all the records in the append query. MS access set 0 fields to null due to a type conversion failure"

all of the fields on both tables have the same type. is this a group by and sum problem? i ran the select statement separately and it worked fine.

code:
INSERT INTO RR_FT_TOT_PAYOUT ( market, region, financial_center, payee_id, employee_name, manager_flg, lic_code, job_code, period_id, volume, cp_spread, ytd_spread, cp_comm, ytd_comm, prior_yr_flag )

SELECT max(rc.market), max(rc.region), max(rc.financial_center), rc.payee_id, max(rc.employee_name), max(rc.manager_flg), max(rc.lic_code), max(rc.job_code), rc.period_id, sum(rc.volume), sum(rc.cp_spread), sum(rc.ytd_spread), sum(cp_comm), sum(ytd_comm), max(prior_yr_flag)
FROM RR_FT_COMPONENT AS RC
GROUP BY rc.payee_id, rc.period_id;


2) in this next code i'm trying to sum spread information in the ft_txn_summary table for a given payee_id, market and period_id. i'm using dsum and have the appropriate joins to keys on the tables, yet this update sums ALL records in the ft_txn_summary table rather than summing the specific payees in the join. any idea on what's wrong or how to debug dsums?

code:
UPDATE rr_ft_component AS rc SET rc.cp_spread = Dsum("txn_spread","ft_txn_summary","ft_txn_summary.payee_id= " & [rc.payee_id] AND "ft_txn_summary.market= " & [rc.market] AND "ft_txn_summary.period_id= " & [rc.period_id])
WHERE rc.component_name='Total Revenue';


any help would be appreciated! thank you!

 

[Pat Hartman]

Try this:

UPDATE rr_ft_component AS rc SET rc.cp_spread = Dsum("txn_spread","ft_txn_summary","ft_txn_summary .payee_id= " & [rc.payee_id] AND "ft_txn_summary.market= " & [rc.market] " AND ft_txn_summary.period_id = " & [rc.period_id])
WHERE rc.component_name= "Total Revenue";

You had the " on the wrong side of the AND