Counting populated fields

[bdubis]

Just started a new job and finding myself using access again for the first time in years.

The company I work for is using software that uses a foxpro database so I'm pulling a copy of the databases and linking that into access and trying to work with the existing structure.

My problem is this, there are 5 salesman fields: Salesman, Salesmn2, Salesmn3...

Salesman is always populated but the others only if more than one salesman assisted. How can I count how many of those fields are populated so I can use that count to divide the total sale by the number of salesman?

Thanks,
Bill

 

[Crowley16]

eh, off the top of my head...

SELECT *, iif(not isnull(salesman2), 
   iif(not isnull(salesman3), 
      iif(not isnull(salesman4), 
         iif(not isnull(salesman5),
           5, 4
         )
      ), 3
   ) , 2
), 1) AS Count 
FROM tblName;

not sure I've got all the brackets in the right places though...

 

[dhookom]

I thinnk the table structure is horribly un-normalized but I expect that changing it at this point might be difficult. You can use an expression like:
SELECT 5+(IsNull(Salesman2) + IsNull(Salesman3) + IsNull(Salesman4) + IsNull(Salesman5) as SMCount
FROM tablename;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bdubis]

Thanks for the tips, I'll give them a try.

Bill