Auto-fill a field based on the selection in another field

BardiyaS

New member
Local time
, 22:46
Joined
Nov 21, 2011
Messages
8
Hello everyone,

I am a novice Access user. Our company is moving a lot of our logs from Excel to Access and I have been learning Access on the fly.

Just to give you an idea of my skill level, I have gotten the hang of things as far as creating tables, forms, and split databases, and I just google my questions when I get stuck and often end up on this forum. I have yet to find step-by-step instructions on how to perform this "auto-fill" function so your assistance will be greatly appreciated.

Just as a side-note, I am using Access 2010:

One of our logs includes the following 4 fields:

SE (sales executive) | Region | Tier | Sales Channel

We want to make it so that the "Region", "Tier", and "Sales Channel" fields fill out automatically depending on which "SE" was selected.

So if I select "Ron Burgundy" from the "SE" field,

"Region" should auto-fill: West
"Tier" should auto-fill: 2
Sales Channel: Inside

If I select "Marco Polo" from the SE" field,

"Region" should auto-fill: East
"Tier" should auto-fill: 2
Sales Channel: Outside

And so on.... I am working with only one table, but I am willing to create another table if this action requires multiple tables.

Please let me know how I can make my request more clear. I am not sure if I am asking this in the right section either.

Thank you so much!
 
I am guessing that you will need a few additional tables, but to help you out we need to understand more about how a the items (person, region, tier, channel) relate to one another. So here are some questions:


Can a person be responsible for more than 1 region?

Can there be more than one person responsible for a region?

What is meant by a tier? Can you provide a list?

Are their many tiers in a region?

Can a tier occur in more than one region?

What is a channel? Can you provide a list?

Does a channel relate to a tier or directly to the person? or the region?

Can a person be responsible for many channels within a region/tier?
 
Hey there, thanks for the quick reply.

I don't mind creating additional tables in order to make this work as long as ONE table pulls this information from multiple tables so that we can review it all in one place.

Before I answer your questions, here are some more details and changes to our request.

There are actually 6 fields we want.

Client: Multiple entries (over 500).
Changes to this field will affect:
-Company ID
-SE
-Region
-Tier

Company ID: A 3 to 5 digit number that is tied to the Client. This is tied directly to the entry made in the "Client" field. Selecting the "Client" should populate the correct "Company ID"

Sales Executive: Multiple entries (20-30 names), the SE can be in charge of multiple tiers and region but they operate in only one sales channel. Selecting the "Client" should populate the correct "SE".

Region: The region in which the SE works in, it can be West, East, North, South. A SE can work in more than one region. Selecting the "Client" should populate the correct "Region".

Tier: Tier is a ranking system we use to rate the size of our customers, there are 4 options: S, M, L, XL. Selecting the "Client" should populate the correct "Tier".

Sales Channel: A channel refers to whether the person works in inside sales or outside sales. The options should be ISO or OSO. This only depends on who the SE is. Once the SE is filled in, it should know to populate ISO or OSO. If this is more difficult to do, we can have this field client driven. So that the system knows that a specific client is ISO or OSO. But we would prefer to have that tied back to the entry under Sales Executive.

Here are the answers to your questions:

Can a person be responsible for more than 1 region?

Yes, the person can be responsible for more than 1 region.

Can there be more than one person responsible for a region?

Yes, multiple people are responsible for a region.

Are their many tiers in a region?

Yes, there are multiple tiers in a region.

Can a tier occur in more than one region?

Yes. We have small customers in multiple regions, so the Westcoast region might have clients that are "Small", "Medium", and "Large".

Does a channel relate to a tier or directly to the person? or the region?

The channel relates directly to the SE. A region or tier does not relate to what the channel should be. But we can have this tie back to the "Client" field if it's easier. Because a client always deals with one SE thus always being under one channel type.

Can a person be responsible for many channels within a region/tier?

No, a person can be responsible for only one channel.

Thank you so much for your help, and please let me know how I can help you by giving you more specific information.
 
OK, based on everything you have said, this is the table structure I would start with.

A table to hold the clients (assuming that they are companies)

tblClient
-pkClientID primary key, autonumber
-CompanyID (the field you mentioned)
-txtClientName
-fkTierID foreign key to tblTier (since only 1 tier is assigned to a customer)

tblTier (holds the choices you mention, each choice is a record)
-pkTierID primary key, autonumber
-txtTier

A table to hold your sales executives (and other people)

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkRoleID foreign key to tblRoles
-txtSalesChannel (ISO or OSO) You technically can use a foreign key here and have the actual ISO/OSO as records in a related table, but since you only have 2 we'll leave it as a direct field



tblRoles (a list of roles for people; assume 1 role per person; a sales executive would be a role)
-pkRoleID primary key, autonumber
-txtRole

A table to hold the regions

tblRegions
-pkRegionID primary key, autonumber
-txtRegion

Since a sales executive can be responsible for many regions, that describes a one to many relationship. Also a region can have more than one sales executive, so another one-to-many relationship. When you have 2 one-to-many relationships between the same 2 entities you have a many-to-many relationship which is captured using a junction table as follows

tblPeopleRegion
-pkPeopleRegionID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkRegionID foreign key to tblRegion


One new question came to mind. Can a customer have a presence in multiple regions? How do you handle those?

You said this:
Region: The region in which the SE works in, it can be West, East, North, South. A SE can work in more than one region. Selecting the "Client" should populate the correct "Region".

If a customer can have a presence in multiple regions, then there is no way that you can populate the 1 correct region when the client is selected. You will have to sort that out.

You will also need to join the customer to a region or regions, and then you will be able to associate the SE of that region to the customer.

I don't mind creating additional tables in order to make this work as long as ONE table pulls this information from multiple tables so that we can review it all in one place.

You would not create a table to pull the information together since creating a table with duplicate information violates normalization rules. You would just use a query that brings the info together.
 
Thank you so much for your assistance. A few questions before I start working on this.

tblClient
-pkClientID primary key, autonumber
-CompanyID (the field you mentioned)
-txtClientName
-fkTierID foreign key to tblTier (since only 1 tier is assigned to a customer)

The "tbl" abbreviation means that I am creating a table, what does "pk", "txt" and "fk" mean, are those fields I should create? Meaning this table will have a "ClientID", "CompanyID", "ClientName", and "TierID" field?

A table to hold your sales executives (and other people)

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkRoleID foreign key to tblRoles
-txtSalesChannel (ISO or OSO) You technically can use a foreign key here and have the actual ISO/OSO as records in a related table, but since you only have 2 we'll leave it as a direct field

tblRoles (a list of roles for people; assume 1 role per person; a sales executive would be a role)
-pkRoleID primary key, autonumber
-txtRole

This is actually a great idea because we might include multiple roles in this table, but if we stick to only SEs, we can out the Role table and the "fkRoleID" from the People table.

One new question came to mind. Can a customer have a presence in multiple regions? How do you handle those?

A client can only be in ONE region. They might have presence in multiple regions but we actually go off the location of their HQ.

You will also need to join the customer to a region or regions, and then you will be able to associate the SE of that region to the customer.

How would I join the client to the regions? And how do I associate the SE to the client in that region?

You would not create a table to pull the information together since creating a table with duplicate information violates normalization rules. You would just use a query that brings the info together.

So once I have all these tables ready, how do I run this query? I have never created a query and I am a bit confused on those.

Also, once I have all this setup, where would we be making our data entry? If I type the client name in the client table, everything else will automatically populate in the other tables? If a manager needs to review the existing data, will it be so that they open one file and everything is presented to them nicely?

Thank you so much for your patience, I know I have a lot of questions!
 
My apologies for not explaining more clearly. The "tbl" does designate a table and the names following the hyphens are indeed fields. I use prefixes on the field names to help me keep the datatype of the field straight when working on forms, queries, reports and code. The prefixes I use are shown below. Using the prefixes also helps avoid using a reserved word as a table or field name. Reserved words in Access should not be used as table or field names; this site has a listing of those reserved words. Also, you want to avoid using spaces or special characters in your table and field names; I also do not use them in naming queries, forms or reports. The reason for not using them is that if you do you have to enclose field or table names with square brackets otherwise it will cause problems with Access. If I can avoid the typing that is good for me!

txt=text datatype field
pk=primary key field -- I always use an autonumber
fk=foreign key- since I use the autonumber for the primary key, the foreign key fields must be long number integer datatype fields in order to match (the autonumber datatype is a special case of a long number integer datatype)
dte=date/time datatype field
log=logical datatype
long= long number integer datatype (but not a foreign key)
sp=single precision number datatype
dp=double precision number datatype
mem=memo datatype

This is actually a great idea because we might include multiple roles in this table, but if we stick to only SEs, we can out the Role table and the "fkRoleID" from the People table.

I try to plan my databases ahead a little so as to avoid redesign as much as possible. I have found that including a role field allows for some flexibility when you start capturing groups of people.

A client can only be in ONE region. They might have presence in multiple regions but we actually go off the location of their HQ.

How would I join the client to the regions? And how do I associate the SE to the client in that region?

Since a client is tied to only 1 region, we have to add a field to the client table

tblClient
-pkClientID primary key, autonumber
-CompanyID (the field you mentioned)
-txtClientName
-fkTierID foreign key to tblTier (since only 1 tier is assigned to a customer)
-fkRegionID foreign key to tblRegions

With the addition of the region field in the client table and we already had the SE tied to a region in tblPeopleRegions, we can make a join there. To establish your relationships, you have to go to the relationship window, put in the tables and then join the key fields (primary key field joins to its similarly named foreign key field in another table). In some cases you may join two foreign key fields for an intermediate join such as the fkRegionID of the client table with the fkRegionID of the tblPeopleRegions. I am terrible at explaining things in detail, so you might check out the tutorials on this site.

So once I have all these tables ready, how do I run this query? I have never created a query and I am a bit confused on those.

You have to first create the query by bringing in all of the tables and then select the fields you want to display. You want to establish your relationships first because once you establish them there, Access will automatically carry them over when you construct the query. You only have to construct the query once (provided you save the query). You can run it any number of times when you want to see the information. A query result will look similar to a table view.

Also, once I have all this setup, where would we be making our data entry? If I type the client name in the client table, everything else will automatically populate in the other tables?

All interaction with data is done through forms. The users should never even see the tables. Typically, the table on the one side of the one-to-many relationship is used as the data source for the main form while the table on the many side is used as the data source for a subform within the main form. Access will automatically link the main and sub forms if you have your relationships established.

If a manager needs to review the existing data, will it be so that they open one file and everything is presented to them nicely?

You have a couple options here. If the manager will only review data (not change data), you can use either a form to display the data or a report to print the data. If the manager needs to make changes then they need to be presented with a form to do so.

The tutorials on the site I mentioned previously goes through form & report creation.
 
Thank you again for all the details. This is going to take me longer than I thought, so I will probably try this out once we are back from the holidays. I might post the database here, all of this might be easier if you could take a look at it.

Thanks again!
 
We can take a look at the database when you have it ready. Enjoy the holiday.
 

Users who are viewing this thread

Back
Top Bottom