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
|
|||
|
|||
simple (?) Access 2007 question
I need help with planning and creating a good data base in Access 2007 for
the following situation. There are approximately 50 employees. On each day they work they are assigned to any of 3 locations. (Location A, Location B, Location C). The assignments change every day, because the company wants to keep rotating them. I need to be able to see, for example, who was not sent Location C during last 7 days. What is the best approach to this task? Thank you. Mark |
#2
|
|||
|
|||
simple (?) Access 2007 question
3 tables:
a) Employee table: one record for each staff member (EmployeeID primary key) b) Location table: one record for each location (LocationID primary key) c) Assignment table: one record for each staff member assigned on each date. Fields: - EmployeeID which staff member - LocationID what location - AssignDate when they were assigned. You can then use a subquery to select the employees who have not been assigned to a location in a period: http://allenbrowne.com/subquery-01.html#NotThere -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "mark" wrote in message ... I need help with planning and creating a good data base in Access 2007 for the following situation. There are approximately 50 employees. On each day they work they are assigned to any of 3 locations. (Location A, Location B, Location C). The assignments change every day, because the company wants to keep rotating them. I need to be able to see, for example, who was not sent Location C during last 7 days. What is the best approach to this task? Thank you. Mark |
#3
|
|||
|
|||
simple (?) Access 2007 question
Thank you Allen very much.
For data entry purposes - it would be easier to enter info into Assignment table if Assignment table had fields: EmployeeName, LocationName, AssignDate. Will it work if I create field EmployeeName instead of EmployeeID and LocationName instead of LocationID? Mark "Allen Browne" wrote in message ... 3 tables: a) Employee table: one record for each staff member (EmployeeID primary key) b) Location table: one record for each location (LocationID primary key) c) Assignment table: one record for each staff member assigned on each date. Fields: - EmployeeID which staff member - LocationID what location - AssignDate when they were assigned. You can then use a subquery to select the employees who have not been assigned to a location in a period: http://allenbrowne.com/subquery-01.html#NotThere -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
#4
|
|||
|
|||
simple (?) Access 2007 question
In Assignment table I have Employee, Location and AssignDate. After using
lookup feature for Employee and Location, their data type was changed to Number. I can't change it because it says that it is a part of relationship. When I open Relationship window there is nothing there. Mark |
#5
|
|||
|
|||
simple (?) Access 2007 question
You're on the right track, but it looks like that Lookup Wizard messed you
up. Just skip the wizard, and create your own tables and relationships. Presumably you can give each location a unique name, so create a Location table with one field like this: - LocationID Text (24 char) primary key Then enter the names of each location. (You can have other fields as well if you need them.) Employees are not quite so clear. There could conceivably be 2 employees with the same name, so you probably can't use names as the primary key. I suggest an Employee table with fields: - EmployeeID AutoNumber primary key - Surname Text family name - Firstname Text Christian name - Inactive Yes/No a box to check when the person leaves. Your main table with then have an EmployeeID (number) field, and a LocationID (text) field. To select the employee, you can use a combo to select the employee. To do that, you need a query to use as the RowSource for the combo. Create this query: SELECT EmployeeID, Surname & ", " + FirstName & IIf([Inactive], " (inactive)", Null) AS FullName FROM tblEmployee ORDER BY Inactive DESC, Surname, Firstname, EmployeeID; Save the query as (say) qryEmployee. So, the combo's properties will be: Row Source qryEmployee Bound Column 1 Column Count 2 Column Widths 0 The combo has 2 columns, but the first one (the number) is zero-width, so it displays the name to choose from, but saves the correct number in your table. Don't forget to create the relationships between your 3 tables. (Relationships window.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "mark" wrote in message ... Thank you Allen very much. For data entry purposes - it would be easier to enter info into Assignment table if Assignment table had fields: EmployeeName, LocationName, AssignDate. Will it work if I create field EmployeeName instead of EmployeeID and LocationName instead of LocationID? Mark "Allen Browne" wrote in message ... 3 tables: a) Employee table: one record for each staff member (EmployeeID primary key) b) Location table: one record for each location (LocationID primary key) c) Assignment table: one record for each staff member assigned on each date. Fields: - EmployeeID which staff member - LocationID what location - AssignDate when they were assigned. You can then use a subquery to select the employees who have not been assigned to a location in a period: http://allenbrowne.com/subquery-01.html#NotThere |
Thread Tools | |
Display Modes | |
|
|