A
aw
Existing I have file A & B.
File A is the summary (pivot table summary from other sources)
File B is the invoices master to store all invoice information.
What I hope to obtain is to generate file C (listed below).
3 Criteria needs :
a). File C should select only data from file A for outstanding amt not
equal to zero (0).
b). File C’s information should be refreshable based on information from
file A & B stored in diff. location.
c). Field “Age (days) – calculation field†is equal to number of days
outstanding. i.e. today() – inv date
As this report should be generated daily, I don’t want to prepare this file
manually.
I am now thinking SQL + pivot table + macro to perform this task. Could you
please let me some hints / ways to solve this problem.
Thanks a lot!!
======================================
File A (Amt outstanding)
invoice debtor outstanding amt
IV004 ABC 140
IV002 BQE 0
IV001 DEF 60
IV003 ABC 0
IV005 ABC 20
File B (invoice master)
invoice inv date
IV004 2007/05/02
IV002 2007/07/09
IV001 2007/11/03
IV003 2007/08/02
IV005 2007/09/11
File C (merge from file A & B)
debtor invoice inv date AGE (days) outstanding amt
ABC IV004 2007/05/02 236 140
ABC IV005 2007/09/11 104 20
DEF IV001 2007/11/03 51 60
File A is the summary (pivot table summary from other sources)
File B is the invoices master to store all invoice information.
What I hope to obtain is to generate file C (listed below).
3 Criteria needs :
a). File C should select only data from file A for outstanding amt not
equal to zero (0).
b). File C’s information should be refreshable based on information from
file A & B stored in diff. location.
c). Field “Age (days) – calculation field†is equal to number of days
outstanding. i.e. today() – inv date
As this report should be generated daily, I don’t want to prepare this file
manually.
I am now thinking SQL + pivot table + macro to perform this task. Could you
please let me some hints / ways to solve this problem.
Thanks a lot!!
======================================
File A (Amt outstanding)
invoice debtor outstanding amt
IV004 ABC 140
IV002 BQE 0
IV001 DEF 60
IV003 ABC 0
IV005 ABC 20
File B (invoice master)
invoice inv date
IV004 2007/05/02
IV002 2007/07/09
IV001 2007/11/03
IV003 2007/08/02
IV005 2007/09/11
File C (merge from file A & B)
debtor invoice inv date AGE (days) outstanding amt
ABC IV004 2007/05/02 236 140
ABC IV005 2007/09/11 104 20
DEF IV001 2007/11/03 51 60