Basic form and subform question

[Lostguy]

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

 

[Jeanette Cunningham]

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

 

[KenSheridan via AccessMonster.com]

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

 

[KenSheridan via AccessMonster.com]

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

 

[Lostguy]

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

 

[KenSheridan via AccessMonster.com]

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]

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

 

[Lostguy]

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

 

[KenSheridan via AccessMonster.com]

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]

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

 

[KenSheridan via AccessMonster.com]

Indexing prevents the same data being entered twice. In your case create a
unique index on the EmpIDfk and TaskDofk columns in the tblEmpTaskDo table.
Then only one row with the same EmpIDfk and TaskDofk values can be entered.
If a user does enter the same data twice it will trigger an error, they'll
get an error message and the record won't be saved. The form's (in this case
the subform's) Error event procedure can be used to trap the error and pop up
a custom message rather than the cryptic system generated one. The system
error message is suppressed by setting the return value of the event
procedure's cancel argument to acDataErrContinue.

BTW one point I omitted to answer in your earlier post was what Join Type to
set in a relationship. As far as I know all this does is set the default
join type if a query based on the two tables is created. I've never found
any cogent reason to change it from the default join type of 1, which is a
normal INNER JOIN.

Ken Sheridan
Stafford, England

 

[Lostguy]

Sir,

Continuing the saga:

11. Added an Index called "EmpTaskDoIndex" to the tblEmpTaskDo on
fields EmpIDfk and TaskDofk.

12. Added an error message to the main form's On Error Event

Private Sub Form_Error(DataErr As Integer, Response As Integer)
MsgBox "You may have entered a Date-Location-Task combination that
has already been entered previously (i.e. 1/1/09 Home Check-in).
Delete the fields in this new record, and then add any new Employee
names to that previous entry."
Response = acDataErrContinue
End Sub

13. I had to uncheck the Ref Int for the Relationship between
TaskDoIDpk in the tblTaskDo and the TaskDoIDfk in the tblEmpTaskDo.
When I tried to delete records in the mainform, it said that I
couldn't because it required deletion of related records. So, I
unchecked that relationship's ref int and I don't get that error
anymore (?)



Last thing (I think), then I will get out of your hair:

I would like to set up one of the reports by employee with their
respective department, last name, and then a list of their tasks done
and the dates.

So I have in the report Section Header: Department
In the LName header: LName
In the Detail Section: TaskDesc, TaskDate.

I tried moving the page break from the toolbar around to different
places, but it never works out.

What I see on the first page:

Headquarters
Smith
Fishing 1/1/09

But then Bowling 1/2/09 for the same employee (Smith) is at the top of
the next page.

I can't see a way to keep all each own employee's task records
together on their own single page (so they can't see each other's
business.)

??

VR/

Lost

 

[KenSheridan via AccessMonster.com]

The error handling for the index violation will pick up that error but bear
in mind that by applying it unconditionally you'd get the same message for
any other kind of data error. You should really make it conditional on the
value of the event procedure's DataErr argument. You can find out what this
is by temporarily putting the following line in the Error event procedure;

MsgBox DataErr

and deliberately triggering the error by entering the same data twice.

Not enforcing referential integrity is a bad idea. You'll end up leaving
unmatched errors in tblEmpTaskDo. If you want any matching rows in that
table to be automatically deleted when you delete a record in the main form
then enforce referential integrity and cascade deletes. You'll need to weed
out any unmatched rows from tblEmpTaskDo first before you can reinstate
referential integrity. BTW you might see some people recommend that cascade
deletes are never enforced. That's nonsense, they are a perfectly valid (and
valuable) part of a development strategy when used appropriately.

As regards the employees report you should group the report by first by
Department, then by LName, then by EmployeeID, giving the EmployeeID group a
group header and footer, but not the Lname. The employee's name goes in the
EmployeeID header . This caters for the possibility of two employees in the
same department with the same last name (it happens more often than you might
think!). In the EmployeeID group footer's properties sheet set its
ForceNewPage property to 'After Section'. You shouldn't need a page break
control anywhere in the report as the change from one employee to the next
will force the page break.

Ken Sheridan
Stafford, England