"John W. Vinson" wrote:

On Mon, 6 Jul 2009 21:01:01 -0700, laskowv
wrote:

I have a table which holds the ID, DivisionID, Division term (ex: 2007-2009,
2009-2011), followed 10 fields (type = number) for the 10 officer positions.
I have 52 divisions and 178 terms that have to be tracked (1921-2099). Each
10 Division Officers need to be maintained; for example here are the 10
offices and the EmployeeID numbers:

ID DivID Term Term Pres 1VP 2VP Chaplain RS Reg. Treas. Hist.
CS Lib Parl
40 29 88 4/30/2009 26694
29776 35877 32121 36207 34987 34985 36942 34863 36497 26088
41 29 90 4/30/2011 29776
35877 2461 36311 36053 36498 36575 37209 33860 36207 26694

This allows me to only have about 5000 records.

Why's that?

If were to make the table
just have a field called officer position and the the employeeid; it would
cause the file to hold over 92,000.

Certainly a better design.

My issue is two- fold:
1) I have a combo box on the form that uses the employee number from the
field to each of the 10 fields. It works fine, but the form is very slow to
open -- but once open it works great.

It's got to populate all ten combos, so yes, it'll be slow.

2) I need to generate reports from this table. I need to list all of the 10
officers names. I started creating the query and linked the President field
to the Employee table to get the name; but when I went to link the 1st VP
that's when Access "yelled" at me.

You would need to add the officer table to the query TEN TIMES. Access will
alias the names, e.g. Officers, Officers_1, Officers_2, Officers_3 and so on.

How do I link to the Employee table for each of the 10 officers in order to
get their names? Is there another way?

The other way is... to normalize your data. You have repeating fields (one
field for each type of officer). The proper design would have three tables:

Officers
OfficerID
LastName
FirstName
etc etc, I'm sure you have this

Offices
OfficeID autonumber, Primary Key
Office Text, e.g. President
any other info about the office, e.g. its statuatory term

OfficeHolders
OfficeID link to Offices
OfficerID link to Officers
StartOfTerm date/time
EndOfTerm date/time
any other info about this person with regard to this office

A Form based on Officers, with a Subform based on OfficeHolders (using
OfficerID as the master/child link field and a Combo Box based on Offices)
will let you easily enter data for a person; the opposite structure, with a
Form based on Offices with a subform based on OfficeHolders, using OfficeID as
the master/child and a combo for the name, will let you see a history of who
has held which office.

A Crosstab Query can recreate your current view of the data if that would be
useful. Your Report would be very easy to create, just join the three tables.
--

John W. Vinson [MVP]
Thank you so much. I just wanted to verify that on the OfficeHolders table -- I should let the "system" auto-generate a "key" for me (for example: ID (autonumber)?

I had originally built the file this way, but I think the confusion comes
from not ever using the continuous forms before.

Also, on the combo boxes, when linking master/child, I end up having to
"manually define" it because the text exceeds the message box and I can't
expand the window to see all 3 options. Have you encountered this at all?
I've kept short form names for this reason but it always scrolls past the
right side of the window.

Valerie