Count Instances

[Ananth]

I have a Supplier Payment Extract which looks as under :


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Supplier-A $10 $12 $14 $10 $11
Supplier-B $3
Supplier-C $5 $25 $50
Supplier-D

I need a query, as to how many instances of payments in a year is done for
a supplier. According to company policy, a Supplier is paid once a month (
10th Working day) even though he would have made several supplies during
previous month.

The query result should appear as under :

Instances
Supplier-A 5
Supplier-B 1
Supplier-C 3
Supplier-D 0

 

[Allen Browne]

In query design, you could type a monster expression into the field row like
this:
IIf([Jan] <> 0, 1, 0) + IIf([Feb] <> 0, 1, 0) + IIf(...

The real problem is that the table is built as a spreadsheet instead of as a
relational table, where you would have 3 fields like this:
SupplierID relates to a table of suppliers
PaymentDate when the payment was made
Amount Currency