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
|
|||
|
|||
Basic form and subform question
Hello!
I want to make a main form where you enter the date and location and task, and then a continuous subform where you select which employees did that task. I have: tblTask: TaskIDpk and TaskName tblEmployee: EmpIDpk and LName tblEmpTask: EmpTaskIDpk, EmpIDfk, TaskIDfk, TaskDate, Task Location. I start up the forms wizard, drag the date, location, and last name fields to it. It tells me I need to set up relations first. So I relate the Task and Employee pk's to the fk's with a Join Type 1 and don't click ref integrity. I retry the forms wizard with those same fields. The problem is that it always want to put the Employee LName in the main form and the Date/Location fields in the subform, but I need it the other way around. Why does it think that the LName is always the mainform? Does it have something to do with how the relations are set up? What am I doing wrong/ I appreciate the help, because I have been stumped for hours on this. VR/ Lost |
#2
|
|||
|
|||
Basic form and subform question
Hi Lostguy,
the form setup should work if you set up like this-- Main form fields based on tblTask only. Subform fields based on tblEmpTask. Use a drop down to select the employee for each task in the subform. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Lostguy" wrote in message ... Hello! I want to make a main form where you enter the date and location and task, and then a continuous subform where you select which employees did that task. I have: tblTask: TaskIDpk and TaskName tblEmployee: EmpIDpk and LName tblEmpTask: EmpTaskIDpk, EmpIDfk, TaskIDfk, TaskDate, Task Location. I start up the forms wizard, drag the date, location, and last name fields to it. It tells me I need to set up relations first. So I relate the Task and Employee pk's to the fk's with a Join Type 1 and don't click ref integrity. I retry the forms wizard with those same fields. The problem is that it always want to put the Employee LName in the main form and the Date/Location fields in the subform, but I need it the other way around. Why does it think that the LName is always the mainform? Does it have something to do with how the relations are set up? What am I doing wrong/ I appreciate the help, because I have been stumped for hours on this. VR/ Lost |
#3
|
|||
|
|||
Basic form and subform question
I think you have the TaskDate and Task Location columns (fields) in the wrong
table. They are attributes of the Tasks entity type so should be in the tblTask table. At present you have them of attributes of the relationship type between tasks and employees, which would be appropriate if each employees on a task had different dates and locations for that task, but from your description it doesn’t sound like that's the case. You'll then be able to set up a main form based on tblTask (or better still on a query on the table, ordered by task name or by location/date or vice versa as preferred, so the main form is in a logical order) with a subform based on tblEmpTask, with the subform control's LinkmasterFields property set to TaskIDpk and its LinkChildField property to TaskIDfk. To select an employee for each task include acombo box in the subform set up as follows: ControlSource: EmpIDfk RowSource: SELECT EmpIDpk , FName & " " & LName FROM tblEmployee ORDER BY LName, FName; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert the last one. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. Note that I've concatenated the employees' first and last names in the combo box to differentiate between two or more with the same last name. Even that's not bullet-proof though; I once worked with two Maggie Taylors. Including another column in the list may help e.g. in the case of our two Maggies their Division would have differentiated between them as one was in Admin and the other in Development Control. Ken Sheridan Stafford, England Lostguy wrote: Hello! I want to make a main form where you enter the date and location and task, and then a continuous subform where you select which employees did that task. I have: tblTask: TaskIDpk and TaskName tblEmployee: EmpIDpk and LName tblEmpTask: EmpTaskIDpk, EmpIDfk, TaskIDfk, TaskDate, Task Location. I start up the forms wizard, drag the date, location, and last name fields to it. It tells me I need to set up relations first. So I relate the Task and Employee pk's to the fk's with a Join Type 1 and don't click ref integrity. I retry the forms wizard with those same fields. The problem is that it always want to put the Employee LName in the main form and the Date/Location fields in the subform, but I need it the other way around. Why does it think that the LName is always the mainform? Does it have something to do with how the relations are set up? What am I doing wrong/ I appreciate the help, because I have been stumped for hours on this. VR/ Lost -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200908/1 |
#4
|
|||
|
|||
Basic form and subform question
PS: you should enforce referential integrity in the relationships between
tblTask and tblEmpTask , and between tblEmployee and tblEmpTask. That way your data integrity is protected. If it won't let you enforce referential integrity then it would suggest that you have one or more rows in tblEmpTask without a match in either tblTask or tblEmployee, or both. You'd need to weed these out first. Ken Sheridan Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200908/1 |
#5
|
|||
|
|||
Basic form and subform question
All,
Thanks for the help, but I think that maybe I confused everyone. I want the form/subform setup to look like this: MainForn Enter the date the task happened (1-1-09, etc) Enter the location where the task happened (Home, Office, School) Enter what the task was (Cleanup the suppy room, balance the budget, etc.) Continuous SubForm Select all the people who did that task (could be 1 person; could be 50) If the same people did that same task on the same date, but at a different place, there should be different records for each of those than if they all did it at the same place. I have the table setup that I described earlier (which could be wrong). This is what I am having trouble setting up. HTH VR/Lost |
#6
|
|||
|
|||
Basic form and subform question
The only real difference in principle between what you describe and what I
described is that you have introduced an additional entity type, and therefore need a fourth table. The type of task e.g. balancing the budget, is one entity type; the execution of the tasks is a separate related entity type, i.e. each type of task can be executed multiple times on different dates by different individuals or groups of people. So the tblTask and tblEmployee tables remain as in your original model, the former representing the types of task and having one row per task type. The tblEmpTask table needs to be decomposed into two tables, however: tblTaskExecution: TaskExecutionpk, TaskIDfk, TaskDate, Task Location. tblEmpTaskExecution : EmpTaskExecutionIDpk, EmpIDfk, TaskExecutionfk. I've tried to follow your naming conventions in the above. Diagrammatically the model would be: tblTask---tblTaskExecution---tblEmpTaskExecution---tblEmployee where the and signs represent the 'many' end of each relationship. As you see TaskDate and Task Location are now attributes of the task execution entity type, so each execution of a task on the same date at the same location is represented by one row in this table. It’s the fact that a particular task type was carried out at a particular location on a particular date which defines each instance of a 'task execution'. For this reason a unique index should be set up on these three columns (in combination, that is, not individually). To create a unique index on multiple columns (fields) the easiest way of doing this is by making the columns the table's composite primary key, which you do in table design view by Ctrl-clicking on each field, making sure you click on the field selector (the little grey rectangle to the left of the field name), then right-click and select 'Primary key' from the shortcut menu. However, as you are already using another column as the 'surrogate' primary key you should index the fields uniquely by selecting indexes from the View menu. Enter a suitable index name in one row of the left column, then enter the column names on two rows of the Field Name column. With the first row (the one with the index name) selected enter Yes as the 'Unique' property. If you are using Access 2007 the interface for doing this will differ, but I imagine is probably much the same under the skin. Before decomposing your original tblEmpTask table it would have been necessary to represent each execution of a task, where this was by more than one employee, by multiple rows in the table with the same TaskDate and Task Location values repeated in each. This would have introduced redundancy, so the table would not be correctly normalized and would be at risk of inconsistent data being entered. Decomposing the table eliminates the redundancy, and consequently the risk, and both new tables are correctly normalized. The main form would now be based on the tblTaskExecution table, not the tblTask table, and would now include a combo box bound to the TaskIDfk field set up as follows: ControlSource: TaskIDfk RowSource: SELECT TaskIDpk, TaskName FROM tblTask ORDER BY TaskName; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm along with text box controls bound to the TaskDate and Task Location fields (though the latter could (and moreover should in order to protect data integrity) be a combo box drawing its list from a Locations table if there are distinct identifiable locations). The subform would be now be bound to the tblEmpTaskExecution table, and linked to the parent form by the subform control's LinkMasterFields property being set to TaskExecutionpk and its LinkChildFields property to TaskExecutionfk. Ken Sheridan Stafford, England Lostguy wrote: All, Thanks for the help, but I think that maybe I confused everyone. I want the form/subform setup to look like this: MainForn Enter the date the task happened (1-1-09, etc) Enter the location where the task happened (Home, Office, School) Enter what the task was (Cleanup the suppy room, balance the budget, etc.) Continuous SubForm Select all the people who did that task (could be 1 person; could be 50) If the same people did that same task on the same date, but at a different place, there should be different records for each of those than if they all did it at the same place. I have the table setup that I described earlier (which could be wrong). This is what I am having trouble setting up. HTH VR/Lost -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200908/1 |
#7
|
|||
|
|||
Basic form and subform question
Ken,
While I am digesting this all, let me ask this: a) if I want to print a list of tasks completed per person, it could look like this: Bob: 1-1-09 Balanced Budget at Home 1-2-09 Balanced Budget at Work 1-3-09 Balanced Budget at Home This example is what I don't want (The repeat of the Task by the same person at the same place, but on a different day). So, I can either enter this in the database and then have a message or something that says "Bill already Balanced the Budget on 1-1-09 at Home. If he repeated this, go back to the original date and update the date." So, all the database should be tracking is the most recent time that a task was performed. I don't care about the historical stuff or that it was done 50 times. Just the most recent. Or should the user just enter each and every task (even if it was repeated) and then when I do the report, just query to output just the most recent date. This would keep all the historical performances of the task, but the user would be redundantly entering the information since all I need to see is the most recent performance of the task. b) In this 4 table method, they should all be ref int/cascade/cascade with Join Type 1? I appreciate your help. I want to bang this out this weekend and there is no way I can do it on my own, so you and this group are lifesavers. VR/ Lost |
#8
|
|||
|
|||
Basic form and subform question
Sir,
Here are the steps I did. Please review this to see if there is anything else I should do (I changed "Execution" to"Do" for brevity.) 1) 4 tables tblTask: TaskIDpk (autonumber), TaskDesc(text) tblTaskDo: TaskDopk (autonumber), TaskIDfk (number), TaskDate (Date/ Time), TaskLocation (text) tblEmpTaskDo: EmpTaskDoIDpk (autonumber), EmpIDfk (number), TaskDofk (number) tblEmployee: EmpIDpk (autonumber), EmpLName (text) 2) Enter some data: tblEmployee EmplLName: Smith, Jones, Hill, etc. tblTask TaskDesc: Bowling, Fishing, Camping, Wash car, etc. 3) In relationship window, drag all the similarly named pk's to fk's. I didn't check ref in/cascade/cascade. All are Join type 1....(?) 4) Create form using wizard: From tblEmpTaskDo: TaskDate, TaskLocation, and TaskIDfk. This is the mainform. From tblEmpTaskDO: EmpIDfk. This is the subform. View by tblTaskDo 5) In the form's Design View, Change the TaskLocation, TaskIDfk, and EmpIDfk from text boxes to combo boxes. For the Task IDfk combo box, change the row source to Query: tblTask: TaskIDpk and TaskDesc, Column Count 2, Column Widths 0;2, and Bound Column 1 For the EmpIDfk combo box, do the same (change the row source to Query: tblEmployee: EmployeeIDpk and EmpLName, Column Count 2, Column Widths 0;2, and Bound Column 1 6) Use the Query wizard and select EmpLName, TaskDesc, TaskDate, TaskLocation. This is looking good, Ken. Here's what I still need help on. a) The "Bill-Bowling-1/1/09-Home" duplication thing when I run the query. b) The whole indexes thing. That went right over my head. What fields from what tables do I create the index on? Would that stop the duplicate events thing? c) Do I check the ref/cascade/cascade? Is Join Type 1 OK? A big Thank You to you! VR/Lost |
#9
|
|||
|
|||
Basic form and subform question
Taking your outstanding points one by one:
a) The "Bill-Bowling-1/1/09-Home" duplication thing when I run the query. With your present tblTaskDo design you really have no alternative but to enter one row per date. Otherwise you have no way of knowing when the task was completed, only when started. You could avoid this by changing the table design so that it has DateStarted and DateFinished columns. You'd then be defining the range of dates over which the task was undertaken. If it takes one day both dates would be the same. This does make querying by a specific date a little more complex, however, as the date may be in the middle of a 3 or more day range. Its not too difficult to cope with this as you simply query for rows: WHERE [Enter date:] BETWEEN DateStarted AND DateFinished where [Enter date:] is a parameter for which the user enters a value when prompted at runtime. It gets a lot trickier, however, if you are querying by a date range as the query then has to look for intersecting ranges. The logic for this is: (a) start date within parameter range, or (b) end date within parameter range, or (c) parameter range entirely within data range which can be directly done with an expression in a query's WHERE clause, but I use the following function: Public Function WithinRange(dtmParamRangeStart As Date, _ dtmParamRangeEnd As Date, _ dtmDataRangeStart As Date, _ dtmDataRangeEnd As Date) As Boolean ' Accepts: date at which parameter range starts ' date at which parameter range ends ' date at which data range starts ' date at which data range ends ' Returns: True if data range intersects with parameter range ' False if whole of data range outside parameter range ' return True if: ' (a) start date within parameter range, or ' (b) end date within parameter range, or ' (c) parameter range entirely within data range WithinRange = _ (dtmDataRangeStart = dtmParamRangeStart And _ dtmDataRangeStart = dtmParamRangeEnd) _ Or (dtmDataRangeEnd = dtmParamRangeStart And _ dtmDataRangeEnd = dtmParamRangeEnd) _ Or (dtmDataRangeStart = dtmParamRangeStart And _ dtmDataRangeEnd = dtmParamRangeEnd) End Function and call it in the query like so: WHERE WithinRange([Enter start date:], [Enter end date:], DateStarted, DateFinished) [Enter start date:] and [Enter end date:] being the parameters for which the user enters values at runtime. If you stick with the current table design with the single TaskDate column you can return just one row per employee/task with for example the date they started the task, by grouping the query by employee, location and task and then returning the Min(TaskDate). The problem with this, though, is that it would return only the very first day an employee started a particular task at a particular location, even if they had undertaken that task on separate occasions at the same location many moths or even years apart, which might not be what you want. There are ways in which you could return the Min (TaskDate) for each subset of the employee/location/task grouping where each subset of dates are a continuous series, but that's getting into some quite advanced querying techniques, and I think I've probably given you more than enough to chew on already. Looking at the problem from another angle you can regard the query as the intermediary between the raw data and the final form of presentation of the results as a report. The duplication in the query's results is irrelevant therefore, as you can control the format in which the results are presented in the report by grouping the report by employee, location and task and putting this data in a group header. All the dates per employee/location/task would then normally be listed in the detail section, but it would be possible to suppress all but the first data in any continuous series of dates, ignoring weekends and holidays if necessary, by writing code in the report's module. I realise that this, and much of what I've said above, will be beyond your current experience level, but there is no getting away from the fact that Access does present a learning curve which is not trivial if these sort of things are to be tackled. b) The whole indexes thing. That went right over my head. What fields from what tables do I create the index on? Would that stop the duplicate events thing? Indexes serve two purposes (a) they speed up performance (b) in the case of 'unique' indexes (no duplicates) they protect the integrity of the data by preventing two or more rows in a table from having the same value or values in a column or set of columns where this would be incorrect. In the case of your tblTaskDo table it would be incorrect to have the same values in all three of the TaskIDfk, TaskDate and TaskLocation columns in more than one row, so a unique index should be created on these three columns in the way I described. c) Do I check the ref/cascade/cascade? Is Join Type 1 OK? You should certainly check the 'enforce referential integrity' check box in all relationships as this (a) prevents unmatched rows being entered into related tables and (b) prevents a rows in one table being deleted if it would leave unmatched rows in a related table. As you are using autonumbers as the primary keys there is no point in enforcing cascade updates as an autonumber column's value is automatic can't be changed. You'd only enforce cascade updates if a 'natural' key was used, where you might change a value in the primary key column of a table, and want the values in the relevant foreign key columns in other tables to be automatically changed to the new value in all matching rows. Enforcing cascade deletes is something which requires careful consideration as it means that if you delete a row in a table n the 'one' side of the relationship all matching rows in the table on the 'many' side will be automatically deleted. Sometimes this is appropriate, sometimes not. If in doubt as to the wisdom of doing it, then don't. You'd then be unable to delete a row in a table n the 'one' side of the relationship until all matching rows in the table on the 'many' side have been deleted, so you cannot leave unmatched rows in the 'many' side table. Ken Sheridan Stafford, England Lostguy wrote: Sir, Here are the steps I did. Please review this to see if there is anything else I should do (I changed "Execution" to"Do" for brevity.) 1) 4 tables tblTask: TaskIDpk (autonumber), TaskDesc(text) tblTaskDo: TaskDopk (autonumber), TaskIDfk (number), TaskDate (Date/ Time), TaskLocation (text) tblEmpTaskDo: EmpTaskDoIDpk (autonumber), EmpIDfk (number), TaskDofk (number) tblEmployee: EmpIDpk (autonumber), EmpLName (text) 2) Enter some data: tblEmployee EmplLName: Smith, Jones, Hill, etc. tblTask TaskDesc: Bowling, Fishing, Camping, Wash car, etc. 3) In relationship window, drag all the similarly named pk's to fk's. I didn't check ref in/cascade/cascade. All are Join type 1....(?) 4) Create form using wizard: From tblEmpTaskDo: TaskDate, TaskLocation, and TaskIDfk. This is the mainform. From tblEmpTaskDO: EmpIDfk. This is the subform. View by tblTaskDo 5) In the form's Design View, Change the TaskLocation, TaskIDfk, and EmpIDfk from text boxes to combo boxes. For the Task IDfk combo box, change the row source to Query: tblTask: TaskIDpk and TaskDesc, Column Count 2, Column Widths 0;2, and Bound Column 1 For the EmpIDfk combo box, do the same (change the row source to Query: tblEmployee: EmployeeIDpk and EmpLName, Column Count 2, Column Widths 0;2, and Bound Column 1 6) Use the Query wizard and select EmpLName, TaskDesc, TaskDate, TaskLocation. This is looking good, Ken. Here's what I still need help on. a) The "Bill-Bowling-1/1/09-Home" duplication thing when I run the query. b) The whole indexes thing. That went right over my head. What fields from what tables do I create the index on? Would that stop the duplicate events thing? c) Do I check the ref/cascade/cascade? Is Join Type 1 OK? A big Thank You to you! VR/Lost -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200908/1 |
#10
|
|||
|
|||
Basic form and subform question
OK.
Here's what else I/we did: 7. Created a tblDepartment (DepartmentIDpk and DepartmentName). Put the DepartmentIDfk in the tblEmployee. Created a form off of tblEmployee where the user can enter the employee's last name and then department (via a combobox created like all the other combos (column count 2, etc.) 8. Created a tblLocation (LocationIDpk and LocationDesc). Put the LocationIDfk in the tblTaskDo. Now I can use that for the combobox like is done for all the others. 9. Clicked Enforce Ref Int for all of the relationships. Did not click the Cascades. They are all still Join Type 1 10. Created a Task Index (Opened tblTaskDo in Design View, clicked the lighting bolt (Index) toolbar icon. In the popup box, typed in "Task" as the Index Name and in the next column, selected TaskIDfk. Then on the next two rows, I left the Index Name blank and then selected TaskDate and LocationIDfk. Back up to the row with the Index name (Task), for the Index Properties, I changed Unique to Yes.) AFA the "Bill - Bowling" thing, I don't need to know when they started the task or how long it took (That's good stuff, but not for this application.) I just want to know when it was done. So the TaskDate is the date the task was finished. So, right now, on the report, it is showing two "Bill Bowling" events. So do I understand that the best way to handle this is just to let the user enter this date, and then on the query for the report, just put Max (Date) or something to display just the latest date of that task? I was just thinking that we have some tasks (CPR class, etc.) that have 25 people. So Secretary 1 could enter that task (enter the date, dropdown the task desc, dropdown the location, then use the subform to dropdown the 25 employees). But Secretary #2 may not see/know what Secretary #1 did (she did not first look at the report), and she types in all the same information for the 25 employees. Now on the report, 25 emmployees have two entries of "CPR 1/1/09" IF there was some way that the database would alert Secretary #2 that that task had already been entered, she would know not to enter it. ?? Thanks for all your help. I think that this last thing is the final hurdle. Another question: If this Access database had been set up with only 3 tables (and the one not decomposed), yes, it would have had redundant data, but then could you use that multi-field index method to prevent entry of the duplicate rows? BTW, I have tried to be specific about what steps I took to make it easier for you to catch the things I am doing wrong and maybe help somebody else like me. If you have any more suggestions, I am all ears. This is good stuff! VR/Lost |
|
Thread Tools | |
Display Modes | |
|
|