use Qry with If net (1 Viewer)

rlassalle

New member
Local time
Yesterday, 22:13
Joined
Nov 19, 2012
Messages
4
Hi there, I have a big dBase database of more than 2G. the database has clientes accaunt and balance for each account ans subaccount. The files are cust_Id (Chart)- sub_acct (Chart) - amn_date (date) - Amount (Num). I need to open the dbase database with ODBC or import I do not know how connection is faster. Please, tell me. Then I need to make a query that, classify each account for the days lates. eg total acct (count) and money (sum) days late between 10 and 30 days, then 30 to 60, then 60 to 90, etc. and create a new database to create a report

I did some code in clipper but it is too slow. The if eg is;

Static Function GetTeamsBal(nBalance,nSubAcct)
If Dayslate >= 0 .and. Dayslate < 10
nBalT0 := nBalT0 + nBalance
nCount0 := nCount0 + 1
aAdd(aColl_Team,{"Team0",nBalT0, nCount0, nSubAcct })
Elseif Dayslate >= 10 .and. Dayslate < 30
nBalT10 := nBalT10 + nBalance
nCount10 := nCount10 + 1
aAdd(aColl_Team,{"Team10",nBalT10, nCount10, nSubAcct })
Elseif Dayslate >= 30 .and. Dayslate < 60
nBalT30 := nBalT30 + nBalance
nCount30 := nCount30 + 1
aAdd(aColl_Team,{"Team30",nBalT30, nCount30, nSubAcct })
Elseif Dayslate >= 60 .and. Dayslate < 90
nBalT60 := nBalT60 + nBalance
nCount60 := nCount60 + 1
aAdd(aColl_Team,{"Team60",nBalT60, nCount60, nSubAcct })
Elseif Dayslate >= 90 .and. Dayslate < 120
nBalT90 := nBalT90 + nBalance
nCount90 := nCount90 + 1
aAdd(aColl_Team,{"Team90",nBalT90, nCount90, nSubAcct })
Elseif Dayslate >= 120 .and. Dayslate < 150
nBalT120 := nBalT120 + nBalance
nCount120 := nCount120 + 1
aAdd(aColl_Team,{"Team120",nBalT120, nCount120, nSubAcct })
Elseif Dayslate >= 150 .and. Dayslate < 180
nBalT150 := nBalT150 + nBalance
nCount150 := nCount150 + 1
aAdd(aColl_Team,{"Team150",nBalT150, nCount150, nSubAcct })
Elseif Dayslate >= 180
nBalT180 := nBalT180 + nBalance
nCount := nCount + 1
aAdd(aColl_Team,{"Team180",nBalT10, nCount, nSubAcct })
Endif
aSort(aColl_Team)
Return
//End
The question is; how to do an Access qry that evaluete the function and get the me the total to create a database with the total by team, each team could be a field name,
I calculated the days with this function.

Static Function CalCurrDate(amn_date)
Private curr_dl_adj := 0
curr_dl_adj := (Date()-1) - amn_date
Return(curr_dl_adj)
//End

Where Date()-1 is the day which I run the qry mines one day then mines the value in the field amn_date. This function return a number between 10 to 180 to classifly the total money late by team.

Thansk for the help.:banghead:
 

Users who are viewing this thread

Top Bottom