I am working with Independent School Districts (ISD) in the state of Texas. The State is divided into 20 regions. Over 1000 ISDs are divided among those 20 regions. Each of the 1000+ ISDs has between 3 and several hundred individual school campuses. Each has a name, but as you would imagine, there is some duplication - not within an ISD, but between different ISDs. For instance, both Dallas ISD and San Antonio ISD may have a Lyndon B. Johnson campus.
We are an association that has members on most of the campuses.
I am using an autonumber ID as primary key on the member, campus, and ISD tables.
During entry/editing within the member table using the basic datasheet, I want to use a lookup field (showing campus names) to select the campus. That is not a problem by itself. But with many tens of thousands of potential campuses to choose from that doesn't work very well. What I would like to do is restrict the campus lookup to the names within the ISD, that is, filter the lookup by ISD.
However, I don't want to include the ISD in the member records because that will not be full normalization, but I am willing to do so if necessary. But if I do, I want to include only the ISD number (another autonumber primary key for the ISD table). So during member entry, I want to translate the ISD key into the related ISD name for selecting the correct ISD, before I try to select the correct campus within the ISD.
Essentially what I want to do, is member entry thru a datasheet and first look up the ISD to find it by name, then use that selection to filter the lookup on campus to limit the selections to only campus names within the chosen ISD.
I would be grateful for any assistance with this. I am not married to this approach, so if someone has a better but different idea, please suggest it. However, I am a relative novice, so bear that in mind.
Thanks in advance.
Conor
We are an association that has members on most of the campuses.
I am using an autonumber ID as primary key on the member, campus, and ISD tables.
During entry/editing within the member table using the basic datasheet, I want to use a lookup field (showing campus names) to select the campus. That is not a problem by itself. But with many tens of thousands of potential campuses to choose from that doesn't work very well. What I would like to do is restrict the campus lookup to the names within the ISD, that is, filter the lookup by ISD.
However, I don't want to include the ISD in the member records because that will not be full normalization, but I am willing to do so if necessary. But if I do, I want to include only the ISD number (another autonumber primary key for the ISD table). So during member entry, I want to translate the ISD key into the related ISD name for selecting the correct ISD, before I try to select the correct campus within the ISD.
Essentially what I want to do, is member entry thru a datasheet and first look up the ISD to find it by name, then use that selection to filter the lookup on campus to limit the selections to only campus names within the chosen ISD.
I would be grateful for any assistance with this. I am not married to this approach, so if someone has a better but different idea, please suggest it. However, I am a relative novice, so bear that in mind.
Thanks in advance.
Conor
