Crosstab Query report

[ineedahelp]

I have a table with a field called [FeeAmount] which represents the cost of a business trip. At month end [FeeAmount] or a portion of [FeeAmount] will be assigned an accounting treatment in field [AcctAction]. For eg, [FeeAmount] could be $1000 and at month end $500 of it will be assigned the [AcctAction] of “E” and the other $500 will be assigned the [AcctAction] of “C”. My ultimate objective is to create a report that is ‘grouped’ by [AcctAction] and sums up all “E”s and “C”s by month with YTD totals as the 13th column. I have a successful crosstab query if I didn't have to "split" my original [FeeAmount]. My only thought here is to create a duplicate record at the time that the business trip is added and when the allocation takes place at the end of each month, I would ‘Update’ the fields with the correct [FeeAmount]s and [AcctAction]s. I know that creating an extra record is probably not “good programming” but I can’t think of any other way to do it. Thanks for any help!

 

[dhookom]

Creating multiple records for multiple AcctActions (or anything else) is the proper way to structure your databases.

There is an FAQ in this forum on creating monthly crosstabs and an FAQ in the queries forum on creating multivalue crosstabs if that is what you need.

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Thanks for your guidance and your suggestions!