A
Ananth
Vendor Country $K Type Count
IBM US 112 D 1
IBM UK 58 S 0
DHL US 80 D 1
DHL Spain 31 S 0
PWC US 15 D 1
PWC France 2 S 0
HP Italy 51 D 1
HP NL 20 S 0
OD NL 15 D 1
OD Belgium 14 S 0
I have a supplier database that has 200K records, culled out from the
company's Hyperion system into the MS Access DB. The table is MONTH_AP. Each
record has about 25 fields.
I have added a field “COUNTâ€, which is to be used for supplier counting. The
supplier with the high spend within the region must be flagged 1,in other
regions with 0. The above example will be illustrative.
A query is subsequently run to get the supplier count and spend in each
region.
My Question : Is there a way to force the count 1 or 0 using a macro or a
function or a module. I presently achieve this in EXCEL using sumproduct
function with the data distributed across 5 tabs. Since it is time consuming
(2hours) and my boss not understanding the effort, I am seeking a solution in
Access. Any help is considered as Divine
IBM US 112 D 1
IBM UK 58 S 0
DHL US 80 D 1
DHL Spain 31 S 0
PWC US 15 D 1
PWC France 2 S 0
HP Italy 51 D 1
HP NL 20 S 0
OD NL 15 D 1
OD Belgium 14 S 0
I have a supplier database that has 200K records, culled out from the
company's Hyperion system into the MS Access DB. The table is MONTH_AP. Each
record has about 25 fields.
I have added a field “COUNTâ€, which is to be used for supplier counting. The
supplier with the high spend within the region must be flagged 1,in other
regions with 0. The above example will be illustrative.
A query is subsequently run to get the supplier count and spend in each
region.
My Question : Is there a way to force the count 1 or 0 using a macro or a
function or a module. I presently achieve this in EXCEL using sumproduct
function with the data distributed across 5 tabs. Since it is time consuming
(2hours) and my boss not understanding the effort, I am seeking a solution in
Access. Any help is considered as Divine