Count Instances

A

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate percentage 8
Function Problem 6
Excell Formula 2
Excell Formula 2
Parameter in Qry 2
Formula to get 3
Formula to get the Ans 4
% of callers repeat/unique 1

Top