R
rsoler
Guest
I have several tables in my accounting system in vb6
in the anexed zip you can see better the tables in mfcwordpad
in one there are the definitions of the accounts and type
Database1 table AplicacionCuentas
cdcta dscta AccTypeID AccTypeCID feuac
11102 Bancos Cash 12/10/2005
11201 Efectos por Cobrar Account Receivable 12/10/2005
11205
Efectos por prepa Account Receivable 12/10/2005
In my other database i use two databases by need of previous versions historical records i have the movements of then with credit an debit an date fepos
Database 2 Table Historico
ID nucom EnLibros fepos Documento nucsc cdsta tipoComprobanteID cdcta posteado cddep dsast feast feven nurf1 cddst cdsmy cdcan tildado debito credito numeroordendecarga Conciliacion Observaciones operador cdobra cdpla numeroordeendecarga
1 C1101 0 200211 1 A 0 11203 -1 ANTICIPO DE VIAJE 01/11/2002 471-XGU SO 81710844 0 Bs 60.000,00 Bs 0,00 1032200064
2 C1101 0 200211 2 A 0 11203 -1 ANTICIPO DE VIAJE 31/10/2002 350-XEG SO 7113490 0 Bs 60.000,00 Bs 0,00 1032200063
What i need is to go trought all records of year selected and filtering by fepos 200501 to 200503 the four quarters
Tehn fill the second table with the amounts of credit -debit according to the AccTypeID that is the field in the resoults table there is a direct way or what is the best way i am using visual basic can hti be done in two ways ? preparing a pre run in a query and selecting it from visual to fill the tables financials help required in order no to do record by record
thanks in advance
Database 1 Table Financials JUST THE TOTALS SELECTED BY TIPE OF TIPOCOMPROBANTE 1 0 -1 OR ALL
Company ID Statement Date Cash Account ReReceivable Inventories Other Current Assets Land Buildings Machinery etc Accumulated Depreciation Other Assets Accounts Payable Accrued Liabilities Accrued Income Taxes Notes Payable Deferred Income Taxes Preferred Stock Common Stock Retained Earnings Net Sales COGS Selling/Admin/ General Expenses Depreciation Interest Expenses Other Income Expenses Taxes
23122 31/03/2005 932694 567113 313714 5000 222909 813799 321491 0 123377 410641 28434 12364 698568 14258 230640 1905192 0 395645 165624 39537 53795 29532 17012 36589
23122 30/06/2005 1116535 353221 699555 4000 223453 813835 341549 0 143545 674973 29347 15389 685983 11479 205450 2073072 0 409698 198793 42243 62984 28820 21243 36287
23122 30/09/2005 1328181 462197 423018 5000 223541 813796 451591 0 145392 764421 30423 17415 607551 10978 262980 2158948 0 469500 272488 48517 71098 28465 12128 36261
23122 31/12/2005 505228 867503 893008 5000 223755 813797 601600 0 183468 921448 41275 20460 589132 19565 242578 2258901 0 601506 352854 68801 78754 27936 14146 35980
I HAVE SOLVED THE FIRST PART OF HAVING THE TOTALS OF THE AMOUNTS BY QUARTE IN ANOTHER TABLE HERE ANEXED NOW I NEED TO SUMM ALL THE ACCOUNTA INTO ANOTHER ONE SEPARATED BY TIPE OF VOUCHER INITIALYEAR -1 0 ALL YEAR 1 CLOSING ANY IDEA
TO ACHIEVE THIS PART I HAVE USED
SELECT cdcta, TipoComprobanteID, Sum(IIF(TipoComprobanteID = -1, IIF(IsNull(debito), 0,Debito) ,0)) AS DI, Sum(IIF(TipoComprobanteID = -1, IIF(IsNull(Credito),0,Credito),0)) AS CI, Sum(IIF(TipoComprobanteID <> -1, IIF(IsNull(debito), 0,Debito) ,0)) AS TD, Sum(IIF(TipoComprobanteID <> -1, IIF(IsNull(Credito),0,Credito),0)) AS TC, Sum(IIF(TipoComprobanteID = 9, IIF(IsNull(debito), 0, Debito) ,0)) AS DF, Sum(IIF(TipoComprobanteID = 9, IIF(IsNull(credito),0,Credito) ,0)) AS CF, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 = 1) and ( fepos - Int( fepos/100)*100 <= 3) ,IIF(IsNull(debito), 0, Debito),0)) AS MD1, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 = 1) AND ( fepos - Int( fepos/100)*100 <= 3),IIF(IsNull(credito), 0, Credito),0)) AS MC1, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 4) AND ( fepos - Int( fepos/100)*100 <= 6) ,IIF(IsNull(debito), 0, Debito),0)) AS MD2, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 4) AND ( fepos - Int( fepos/100)*100 <= 6),IIF(IsNull(credito), 0, Credito),0)) AS MC2, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 7) AND ( fepos - Int( fepos/100)*100 <= 9) ,IIF(IsNull(debito), 0, Debito),0)) AS MD3, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 7) AND ( fepos - Int( fepos/100)*100 <= 9),IIF(IsNull(credito), 0, Credito),0)) AS MC3, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 10) AND ( fepos - Int( fepos/100)*100 <= 12) ,IIF(IsNull(debito), 0, Debito),0)) AS MD4, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 10) AND ( fepos - Int( fepos/100)*100 <= 12),IIF(IsNull(credito), 0, Credito),0)) AS MC4
FROM Historico
WHERE Fepos BETWEEN 200501 AND 200512 AND POSTEADO = TRUE
cdcta TipoComprobanteID DI CI TD TC DF CF MD1 MC1 MD2 MC2 MD3 MC3 MD4 MC4
11101 -1 Bs 18.050.000,00 Bs 16.526.374,97 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00
11101 0 Bs 0,00 Bs 0,00 Bs 112.095.609,97 Bs 33.493.469,97 Bs 0,00 Bs 0,00 Bs 3.000.000,00 Bs 0,00 Bs 3.482.259,97 Bs 27.275.454,97 Bs 104.213.350,00 Bs 2.245.245,00 Bs 0,00 Bs 0,00
11101 1 Bs 0,00 Bs 0,00 Bs 190.860.449,94 Bs 274.560.462,97 Bs 0,00 Bs 0,00 Bs 38.500.000,00 Bs 39.898.800,00 Bs 72.573.154,97 Bs 48.779.959,97 Bs 62.500.000,00 Bs 168.042.353,00 Bs 0,00 Bs 0,00
ETC
THANKS AGAIN FOR IDEAS
i have a more clear view of tables definitiopns etc but is on mfcwordpad and does not copy here if anyone interested i can forward by email
ramon@soler.com
in the anexed zip you can see better the tables in mfcwordpad
in one there are the definitions of the accounts and type
Database1 table AplicacionCuentas
cdcta dscta AccTypeID AccTypeCID feuac
11102 Bancos Cash 12/10/2005
11201 Efectos por Cobrar Account Receivable 12/10/2005
11205
Efectos por prepa Account Receivable 12/10/2005
In my other database i use two databases by need of previous versions historical records i have the movements of then with credit an debit an date fepos
Database 2 Table Historico
ID nucom EnLibros fepos Documento nucsc cdsta tipoComprobanteID cdcta posteado cddep dsast feast feven nurf1 cddst cdsmy cdcan tildado debito credito numeroordendecarga Conciliacion Observaciones operador cdobra cdpla numeroordeendecarga
1 C1101 0 200211 1 A 0 11203 -1 ANTICIPO DE VIAJE 01/11/2002 471-XGU SO 81710844 0 Bs 60.000,00 Bs 0,00 1032200064
2 C1101 0 200211 2 A 0 11203 -1 ANTICIPO DE VIAJE 31/10/2002 350-XEG SO 7113490 0 Bs 60.000,00 Bs 0,00 1032200063
What i need is to go trought all records of year selected and filtering by fepos 200501 to 200503 the four quarters
Tehn fill the second table with the amounts of credit -debit according to the AccTypeID that is the field in the resoults table there is a direct way or what is the best way i am using visual basic can hti be done in two ways ? preparing a pre run in a query and selecting it from visual to fill the tables financials help required in order no to do record by record
thanks in advance
Database 1 Table Financials JUST THE TOTALS SELECTED BY TIPE OF TIPOCOMPROBANTE 1 0 -1 OR ALL
Company ID Statement Date Cash Account ReReceivable Inventories Other Current Assets Land Buildings Machinery etc Accumulated Depreciation Other Assets Accounts Payable Accrued Liabilities Accrued Income Taxes Notes Payable Deferred Income Taxes Preferred Stock Common Stock Retained Earnings Net Sales COGS Selling/Admin/ General Expenses Depreciation Interest Expenses Other Income Expenses Taxes
23122 31/03/2005 932694 567113 313714 5000 222909 813799 321491 0 123377 410641 28434 12364 698568 14258 230640 1905192 0 395645 165624 39537 53795 29532 17012 36589
23122 30/06/2005 1116535 353221 699555 4000 223453 813835 341549 0 143545 674973 29347 15389 685983 11479 205450 2073072 0 409698 198793 42243 62984 28820 21243 36287
23122 30/09/2005 1328181 462197 423018 5000 223541 813796 451591 0 145392 764421 30423 17415 607551 10978 262980 2158948 0 469500 272488 48517 71098 28465 12128 36261
23122 31/12/2005 505228 867503 893008 5000 223755 813797 601600 0 183468 921448 41275 20460 589132 19565 242578 2258901 0 601506 352854 68801 78754 27936 14146 35980
I HAVE SOLVED THE FIRST PART OF HAVING THE TOTALS OF THE AMOUNTS BY QUARTE IN ANOTHER TABLE HERE ANEXED NOW I NEED TO SUMM ALL THE ACCOUNTA INTO ANOTHER ONE SEPARATED BY TIPE OF VOUCHER INITIALYEAR -1 0 ALL YEAR 1 CLOSING ANY IDEA
TO ACHIEVE THIS PART I HAVE USED
SELECT cdcta, TipoComprobanteID, Sum(IIF(TipoComprobanteID = -1, IIF(IsNull(debito), 0,Debito) ,0)) AS DI, Sum(IIF(TipoComprobanteID = -1, IIF(IsNull(Credito),0,Credito),0)) AS CI, Sum(IIF(TipoComprobanteID <> -1, IIF(IsNull(debito), 0,Debito) ,0)) AS TD, Sum(IIF(TipoComprobanteID <> -1, IIF(IsNull(Credito),0,Credito),0)) AS TC, Sum(IIF(TipoComprobanteID = 9, IIF(IsNull(debito), 0, Debito) ,0)) AS DF, Sum(IIF(TipoComprobanteID = 9, IIF(IsNull(credito),0,Credito) ,0)) AS CF, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 = 1) and ( fepos - Int( fepos/100)*100 <= 3) ,IIF(IsNull(debito), 0, Debito),0)) AS MD1, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 = 1) AND ( fepos - Int( fepos/100)*100 <= 3),IIF(IsNull(credito), 0, Credito),0)) AS MC1, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 4) AND ( fepos - Int( fepos/100)*100 <= 6) ,IIF(IsNull(debito), 0, Debito),0)) AS MD2, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 4) AND ( fepos - Int( fepos/100)*100 <= 6),IIF(IsNull(credito), 0, Credito),0)) AS MC2, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 7) AND ( fepos - Int( fepos/100)*100 <= 9) ,IIF(IsNull(debito), 0, Debito),0)) AS MD3, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 7) AND ( fepos - Int( fepos/100)*100 <= 9),IIF(IsNull(credito), 0, Credito),0)) AS MC3, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 10) AND ( fepos - Int( fepos/100)*100 <= 12) ,IIF(IsNull(debito), 0, Debito),0)) AS MD4, Sum(IIF(TipoComprobanteID > -1 AND TipoComprobanteID < 9 AND ( fepos - Int( fepos/100)*100 >= 10) AND ( fepos - Int( fepos/100)*100 <= 12),IIF(IsNull(credito), 0, Credito),0)) AS MC4
FROM Historico
WHERE Fepos BETWEEN 200501 AND 200512 AND POSTEADO = TRUE
cdcta TipoComprobanteID DI CI TD TC DF CF MD1 MC1 MD2 MC2 MD3 MC3 MD4 MC4
11101 -1 Bs 18.050.000,00 Bs 16.526.374,97 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00 Bs 0,00
11101 0 Bs 0,00 Bs 0,00 Bs 112.095.609,97 Bs 33.493.469,97 Bs 0,00 Bs 0,00 Bs 3.000.000,00 Bs 0,00 Bs 3.482.259,97 Bs 27.275.454,97 Bs 104.213.350,00 Bs 2.245.245,00 Bs 0,00 Bs 0,00
11101 1 Bs 0,00 Bs 0,00 Bs 190.860.449,94 Bs 274.560.462,97 Bs 0,00 Bs 0,00 Bs 38.500.000,00 Bs 39.898.800,00 Bs 72.573.154,97 Bs 48.779.959,97 Bs 62.500.000,00 Bs 168.042.353,00 Bs 0,00 Bs 0,00
ETC
THANKS AGAIN FOR IDEAS
i have a more clear view of tables definitiopns etc but is on mfcwordpad and does not copy here if anyone interested i can forward by email
ramon@soler.com
Last edited:
