Counting text boxes

[nycCTM]

I have a report that looks like this:

SectorID (Sector header)

(Detail)
Partner1 Director1 Associate1
Partner2 Director2 Associate2
Partner3 Director3 Associate3
Partner4 Associate4
Partner5 Associate5
Partner6 Associate6
Associate7
Associate8
Associate9
Associate10

The fields are text. The report is based on a query. Some of the fields are
blank. I want to be able to count the number of partners, directors,
associates, etc. (the report is actually longer than this sample). How can I
do this? “Count†does not work.

I originally had the report formatted with one text box instead of the
“columns†above that had the following control source. I gave up on that
because 1) I could not get totals and 2) I could not suppress the blank from
printing
:
=[Director1] & "
" & [Director2] & "
" & [Director3] & "
" & [Director4] & "
" & [Director5]

The data needs to be in columns.

Can someone please help me either find a way to do the count, or tell me how
this report should be formatted so that I can get “column counts†as I need.
Thanks in advance.

 

[Duane Hookom]

Can you provide your table structure? I hope you haven't "committed
spreadsheet" by having 6 partner fields, 3 director fields, and 10 associate
fields.

 

[nycCTM]

Unfortunately I have because of the structure of the report. Basically the
assignments can change, today there could be 3 Associates in one Sector,
tomorrow there could be 12. The information for the names comes from another
main table. I could not figure out a better way to do the assignments because
many people are assigned to more than one thing (Sector).

I was sort of afraid that the query and underlying table where not set up in
the best way. If you have any suggestion please let me know.

 

[Duane Hookom]

I would expect "associate", "Partner", and "Director" to be values in a
field. Each value currently stored in one of the 19 fields should create its
own record in a table

tblPeoplePositions
=============
PePoID autonumber primary key
Position values like Associate, partner,..
PersonName "John", "Betty",....

 

[Duane Hookom]

I forgot to add the SectorID to the table.

 

[nycCTM]

They are titles. The problem is that a person can be assigned to more than
one sector. It would be simple if they could only be in one sector. I think
the rest of the database is fine, the relationships are good, but I can't
figure out a better way to do this because of the dual assignments.

 

[Duane Hookom]

Is there a reason a person couldn't have more than one record in the table?

 

[nycCTM]

Can anyone help me with this? The problem is that a person can be assigned to
more than one Sector. What is the best way to set up the tables? If it were
only one I would know what to do.

 

[Duane Hookom]

Did you try create a table like:

tblPeoplePositions
=============
PePoID autonumber primary key
Position values like Associate, partner,..
PersonName "John", "Betty",....
SectorID

 

[nycCTM]

Actually I did. The problem is that a person could be assigned to more than
one sector - that's where I get stuck. Not everyone is, most are assigned to
only one, but some are assigned to as many as four.

Each sector has a minimum of one person per title and a maximum of 25 (at
this time anyway).

 

[nycCTM]

FYI - the way I have it set up now may not be the best or the correct way but
it does work. I have a nice form set up so that the end user can assign
people to the sector. The only problem is that I can't tally the "columns" or
the whole group.

 

[Duane Hookom]

What is stopping you from adding the same person into the table multiple
times?

 

[nycCTM]

How do you mean? Using your example below, wouldn't each person have their
unique "PePoID"? Do you mean like this (which doesn't make sense to me)

PePoID FirstName LastName Position Sector
1 John Doe Associate M&A
2 John Doe Associate FSIP
3 John Doe Associate Quant

 

[Duane Hookom]

I would expect you to have a table of people with a primary key field like
PeopleID. The value of this primary key field would be stored in the
junction table that I suggested rather than the first and last names.

 

[nycCTM]

OK, so it would be somthing like this??:

PePoID Sector
1 M&A
1 FSIP
1 Quant
2

 

[Duane Hookom]

I would expect a table like:

PePoID PeopleID Position Sector
---------- ------------ ----------- ----------
1 123 Associate M&A
2 123 Associate FSIP
3 231 Partner M&A

tblPeople
============
PeopleID FirstName LastName
-------------- -------------- --------------
123 John Doe
231 Mary Smith

 

[nycCTM]

Tried doing a table like that, still does not work. Reason is that everything
is supposed to be grouped on Sector. Now instead of 12 records I have 169
(one per person plus the doubles and. Not what I need.

I think I'm just about to give up.

All I really want is a way to count my original "columns" so that it would
look something like this:

M&A
PARTNERS DIRECTORS ASSOCIATES
Partner 1 Director1 Associate1 M&A Total: 10
Partner 2 Director2 Associate2
Director3 Associate3
Associate4
Associate5
2 3 5

I have two versions of this report - one which has a control for each
person, one that strings them together from a query. They both look fine
except for the fact that I can't get totals.

 

[Duane Hookom]

Good luck. I wouldn't be concerned about the number of records since they
are fairly inexpensive.