counting fields?

[jumpyhu]

Each record in my table (called Brooks) is a separate order. Each order/record includes a store number, as well as a bunch of name fields (name1, name2, name3, etc).
The idea is that a store can enter a bunch of names into the table and we can process an order (each record).

I would like to be able to COUNT the number of names present in each record (meaning, the number of name1/2/3/4/5/6/7/8/9/10 fields actually filled) and spit that number back in a report.
It would read something like:
Store# 4536 Number of Products Ordered: 129

You see, there are no numbers used currently to actually total. I need something to look at how many name fields have been filled and total the used fields to a number stating number of products purchased (obviously, each name entered represents a single product ordered.)

Thanks so much!
Kelly Warner

 

[Atomic Shrimp]

A query like:

SELECT [tablename].store, (IsNull([name1])+IsNull([name2])+IsNull([name3])+... )*-1 AS count
FROM [tablename];

should do what you're asking for, however, It sounds like your table design is going to come back to bite you in the future (you have, for example name1 to name10, but if you decide that you now need name11, you have to add a new column to your table and redesign all of your queries).

could you not store the names as separate records (i.e. a list) in a single field 'name' and then count/summarise them?

Mike

 

[jumpyhu]

Fortunately, I never will need additional name fields, but YES- you are absolutely correct. I realized that my architecture was faulty when I got to this point. You know how it is sometimes (as a newbie); you build a db only to realize that your core logic is not up to par ;[

Well, now I've just got to figure out where to put this code of yours in the query... I hate being an amateur...

Kelly Warner

 

[jumpyhu]

AH! SQL view!
New to me.

I feel like a plumber now, actually working with the pipes and fixtures behind the scenes for once

 

[jumpyhu]

Thanks alot for your help. It took me a bit to figure out that your code basically adds up the Null Fields, so (as another half-baked move by me) I created another field in the query which does the math to give me my positive values per store.

You're a life saver :]

Kelly Warner

 

[Atomic Shrimp]

Oops, yes, it is adding up the nulls!; if you change the 'isnulls' to 'Not(isnull' it will add up the filled fields, i.e.

IsNull([name1])

becomes:

Not(IsNull([name1]))

should do it.

Mike