Hello to all fo you,

I have one challenge task which is daunting for me . I have very large database ( 100000 records and counting)Access 2003 on my job and one table is append every day once we process the data from big American Bank Citi bank in this case .I have created a form that filter the data in the table( query in this case call qryCitiTotal2012). The qryCitiTotal2010 consists following fileds: CitiTrns,Code,DateProccessed, Debtor Name, Gross Amount,Net Amount, Comm Amount, GrossInv, FeesInv and NsfInv.


The user can run query and report accordingly by debtor name, date processed, CitiTRNS( it is 10 digits number) and code. The upper managements wants me to sum gross Amount, net amount and Comm amount based on GrossInv, feesinv and nsfinv. These fileds populated by me and they reflect name of oracle invoice that we booked in our accounting system.
I created text box on the form called txtGross and put there iif condition like this
" =iif([GrossInv]=0,0,sum[Gross Amount]))" and it returned 0.
The invoices fileds are not populated in every filed and it append accordingly the code:
code is 002 it belongs to Grossinv, if it end 003 it belong to Nsf).
For examples for one day it can be total 200 records and only 100 grossinv ,50 feeinf and 50 nsf invoice. The groosInv are the same for one day and the same with two fee and nsf fileds. They are all the same for one day.
So, I would like to sum gross amount for gross inv filed and the same with two others.
Should I create a second cross tab query to do that or there is another way to sum gross amount, net and comm amount?
Thnak you in advance,
The best regards,
BorisGomel