If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Employee Names in Table
New to Access
I have a table "customers" that has Customer name and who support them with about a 1000 records. The who supports them is in three columns with names only, i.e Account Manager, Project Manager, Product Manager. What is the best way to make it so that when an employee changes I can update the records? I have a separate table with employee ID and name. Again, the customer's table only has names. The ultimate goal is to be able to search based on Employee name rather three different searches. |
#2
|
|||
|
|||
Employee Names in Table
From your description, it sounds like you have experience using a
spreadsheet. Unfortunately, committing spreadsheet on Access only confuses both you and Access! As a relational database, Access "expects" well-normalized data for its features/functions to work well. If "normalized" and "relational" are not familiar terms, plan to spend some time coming up to speed on them. It sounds like you have customers, employees, roles, and customers-supported-by. This implies four tables in Access. Because you could (theoretically) have more than one customer with the same name (e.g., John Smith), I'd suggest you use an Autonumber field as a primary key in your [Customer] table. Similar arguement for the [Employee] table. Your [Roles] table is a lookup table, listing the valid roles your employees can support in ... and NOTE! You have only three roles today ... with your design, you have to remake the database/application if you ever add a fourth. With the [Roles] table, you only need to add a role! Your [Customers-Supported-By] table would hold the CustomerID, the EmployeeID, and the Role (or RoleID, if you decide to do that). You might want to include two date/time fields (FromDate, and ToDate), to indicate when the Customer was supported by the Employee serving in that Role. Note that one customer could have multiple [Customers-Supported-By] records, one for each "Role" of support (and for each time frame, if you include that). Confused more now?!g By the way, plan on NOT working directly in the tables. Access tables may look like spreadsheets, but they aren't. Use forms to handle adding/editing data. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Confused" wrote in message ... New to Access I have a table "customers" that has Customer name and who support them with about a 1000 records. The who supports them is in three columns with names only, i.e Account Manager, Project Manager, Product Manager. What is the best way to make it so that when an employee changes I can update the records? I have a separate table with employee ID and name. Again, the customer's table only has names. The ultimate goal is to be able to search based on Employee name rather three different searches. |
#3
|
|||
|
|||
Employee Names in Table
So for simplicity what about having the employee table have Employee ID,
Name, and Title(to match what's in the supported-by table)? So in order to make it relate, should I do an update query e.g, add this employee ID to this column where name = Mary Jane? I didn't know if it would be necessary to have three columns each containing a corresponding ID or would I? What steps would I take to make this happen? "Jeff Boyce" wrote: From your description, it sounds like you have experience using a spreadsheet. Unfortunately, committing spreadsheet on Access only confuses both you and Access! As a relational database, Access "expects" well-normalized data for its features/functions to work well. If "normalized" and "relational" are not familiar terms, plan to spend some time coming up to speed on them. It sounds like you have customers, employees, roles, and customers-supported-by. This implies four tables in Access. Because you could (theoretically) have more than one customer with the same name (e.g., John Smith), I'd suggest you use an Autonumber field as a primary key in your [Customer] table. Similar arguement for the [Employee] table. Your [Roles] table is a lookup table, listing the valid roles your employees can support in ... and NOTE! You have only three roles today ... with your design, you have to remake the database/application if you ever add a fourth. With the [Roles] table, you only need to add a role! Your [Customers-Supported-By] table would hold the CustomerID, the EmployeeID, and the Role (or RoleID, if you decide to do that). You might want to include two date/time fields (FromDate, and ToDate), to indicate when the Customer was supported by the Employee serving in that Role. Note that one customer could have multiple [Customers-Supported-By] records, one for each "Role" of support (and for each time frame, if you include that). Confused more now?!g By the way, plan on NOT working directly in the tables. Access tables may look like spreadsheets, but they aren't. Use forms to handle adding/editing data. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Confused" wrote in message ... New to Access I have a table "customers" that has Customer name and who support them with about a 1000 records. The who supports them is in three columns with names only, i.e Account Manager, Project Manager, Product Manager. What is the best way to make it so that when an employee changes I can update the records? I have a separate table with employee ID and name. Again, the customer's table only has names. The ultimate goal is to be able to search based on Employee name rather three different searches. |
#4
|
|||
|
|||
Employee Names in Table
I'm not familiar with your scenario, so I can't tell you if having an
employee record with [Title] makes sense. For instance, it may be that you have an employee named Sue Smith who was an Account Rep last year but is a Manager this year. For your purposes, do you care that she, as an Account Rep, helped customer #99 last year, and is helping that same customer this year, but as a Manager? How you design your table structure depends on what you need to do with the data. In the example above, you would NOT include [Title] in the employee record. You would include CustomerID and EmployeeID in the "customer-supported-by" record, along with the RoleID and the DateSupported. Focus first on defining the entities (things about which you need to keep data) and the relationships among those entities. Then start on the forms... Regards Jeff Boyce Microsoft Office/Access MVP "Confused" wrote in message ... So for simplicity what about having the employee table have Employee ID, Name, and Title(to match what's in the supported-by table)? So in order to make it relate, should I do an update query e.g, add this employee ID to this column where name = Mary Jane? I didn't know if it would be necessary to have three columns each containing a corresponding ID or would I? What steps would I take to make this happen? "Jeff Boyce" wrote: From your description, it sounds like you have experience using a spreadsheet. Unfortunately, committing spreadsheet on Access only confuses both you and Access! As a relational database, Access "expects" well-normalized data for its features/functions to work well. If "normalized" and "relational" are not familiar terms, plan to spend some time coming up to speed on them. It sounds like you have customers, employees, roles, and customers-supported-by. This implies four tables in Access. Because you could (theoretically) have more than one customer with the same name (e.g., John Smith), I'd suggest you use an Autonumber field as a primary key in your [Customer] table. Similar arguement for the [Employee] table. Your [Roles] table is a lookup table, listing the valid roles your employees can support in ... and NOTE! You have only three roles today ... with your design, you have to remake the database/application if you ever add a fourth. With the [Roles] table, you only need to add a role! Your [Customers-Supported-By] table would hold the CustomerID, the EmployeeID, and the Role (or RoleID, if you decide to do that). You might want to include two date/time fields (FromDate, and ToDate), to indicate when the Customer was supported by the Employee serving in that Role. Note that one customer could have multiple [Customers-Supported-By] records, one for each "Role" of support (and for each time frame, if you include that). Confused more now?!g By the way, plan on NOT working directly in the tables. Access tables may look like spreadsheets, but they aren't. Use forms to handle adding/editing data. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Confused" wrote in message ... New to Access I have a table "customers" that has Customer name and who support them with about a 1000 records. The who supports them is in three columns with names only, i.e Account Manager, Project Manager, Product Manager. What is the best way to make it so that when an employee changes I can update the records? I have a separate table with employee ID and name. Again, the customer's table only has names. The ultimate goal is to be able to search based on Employee name rather three different searches. |
Thread Tools | |
Display Modes | |
|
|