Advice on Form Design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stockwell43 - 13 Jul 2007 14:00 GMT

Morning Everyone!

I need some advice on how to go about a database I am trying to design.

There are about 6 employees and each one has about 10 tasks they are
responsible for doing weekly and/or monthly. The two ways I thought about
this (being my knowledge is limited) is:

1. Put all 60 tasks in a table and then place them on a form. The user would
selct their name from a cbo and check the task they completed. So I would
four checkboxes for week 1 week 2 and so on and place all of the in a query
to show on a report.

2. Create a form for each individual with only their assigned tasks with the
checkboxes as above with each form assigned to it's own table. (unless I can
somehow link all the forms to the same table). Then I need to know how I
would create a query to pull from the different tables to show on a report.

3. Use Excel(last option) and create workbook with a seperate sheet for each
individual with their assigned tasks and link it to a master sheet to show
everyone as a group.

Please help with any advice possible. Also, if you know of a database that
can do this or similar please let me know so I can at least have a start and
modify it to my needs.

Thanks!!!

Reply to this Message

Klatuu - 13 Jul 2007 15:02 GMT

This can be done with three tables, one form, one subform, and two queries.
It does take some VBA coding, but nothing we can't help with.
Let's start with the tables. First, you will want an employee table. If
your application already has one, great. If not, you will need one. Next,
you will need a table of assigned taskes. It should include the primary key
from the Employee table, The task code and description, the frequency the
task is required to be performed, the date the task should be done for the
first time ever. And, you will need a table to record the completion of the
tasks. It should be related to the task assignment table.

Now, on your form, you will want a combo box to select the employee. This
should be an unbound control. You will not be updating anything with it.
You subform should be a query based on the task assignement table and the
tasks completed table.
The query should return a list of all tasks for the selected employee that
are due. When the user selects the employee and checks a task as complete,
the completion date should be entered into the query to show the task has
been completed and when. The tendancy here would be to include a Yes/No
field to say whether the task is completed. That is redundant. If the
record exists in the task complete table, then it is. You just want to know
it has been done.

The one question I would have is whether it is possible Fred could complete
Joan's task? Say, if Joan is on vaction and Fred does the task, do we show
it completed by Joan or Fred?

I am sure you willl want more detail, so post back with detailed questions
if you have them.

Signature

Dave Hargis, Microsoft Access MVP

> Morning Everyone!
>
[quoted text clipped - 23 lines]
>
> Thanks!!!

Reply to this Message

Stockwell43 - 13 Jul 2007 15:44 GMT

Thank you for your quick response Klatuu!

To answer your question, if Fred di Joan's task while she was on vacation
the Fred would get credit.

I will most likely have questions as I understand to a degree what you
explained. I am starting this database from scratch so I am going to follow
your instructions and hope I can complete it without too much confusion
before having to go further. Once I complete the instructions you gave me, I
will post back for additional information to continue.

One question I do have now though, I can make the query that relates to the
two tables how and where am I attaching this to the form? I am use to
creating a form and attaching it's record source to the main table so that
part threw me off a bit.

Thanks!!

> This can be done with three tables, one form, one subform, and two queries.
> It does take some VBA coding, but nothing we can't help with.
[quoted text clipped - 52 lines]
> >
> > Thanks!!!

Reply to this Message

Klatuu - 13 Jul 2007 15:56 GMT

Okay, let's talk about subforms. There are two elements to a "subform".
They are often confused.
The first is a control on a form. It is a subform control. It is not a
form. It does have a property called Source Object. The Source Object
property of a subform control is a form. Thi form is often is often referred
to as a subform, but it is not. There is really no difference between a form
and a form used as a subform. You will, in fact, find applications where a
form is used stand alone and as a subform.
The way you relate the data in the main form and the data in the subform
form is by using the Link Master Field(s) and Link Child Field(s) property of
the subform control. You put the name of the field in the main (Master)
form's record source that relates the record source of the sub (Child) form
in the Link Master Field property and the name of the field in the child
form's recordset that relates to the record source of the master form. It is
the same as relating tables.

If Fred is going to get credit for the task, you will need to consider that
in your table design. You will want to still relate it to whomever was
assigned, but record who actually performed the task.

Signature

Dave Hargis, Microsoft Access MVP

> Thank you for your quick response Klatuu!
>
[quoted text clipped - 70 lines]
> > >
> > > Thanks!!!

Reply to this Message

Stockwell43 - 13 Jul 2007 16:14 GMT

Great! I am in the process of creating the tables now and loading the
information in each table. I am following your instruction in order not to
stray away and do something I shouldn't. Now you say I should relate the
completion of task table to the Assigned task table. My employee table has
EmployeeID for primary key and Assigned task table has TasksID for primary
key. EmployeeID is set as text and TasksID is set to Autonumber. Completion
of task table will have CompletionID for primary key which will be set as
autonumber.

I have the EmployeeId in the Assigned task table and will place the
CompletionID in the assigned task table as well. Am I on the same page?
Should the EmployeeID be and auto number fied as well?

Please be patient, I have some knowledge about access but this is going to
take me into new frontier. I am copying down all your information down and
saving it for future reference as I try to expand my knowledge with each
database created and appreciate very much the help of this forum!

> Okay, let's talk about subforms. There are two elements to a "subform".
> They are often confused.
[quoted text clipped - 90 lines]
> > > >
> > > > Thanks!!!

Reply to this Message

Klatuu - 13 Jul 2007 16:26 GMT

It would not be a bad idea to use an autonumber for the Employee ID, but not
absolutely necessary IF the employee's unique ID will never change.
Both the assigned and completed tables should carry the primary key value as
a foreign key, but the assigned table should not carry the completedID. The
relation to those two is task is one to many completions. So, the completed
table should have the assigned taskID. (sure wish I could do graphics so I
could show you).
So, basically, it is:
One Employee may have many tasks assigned.
Each task may be completed many times.

Be sure to have a Completed By field in the completed table since the task
may be completed by someone other than assigned. We will discuss how to use
it when you get your tables defined.
When you have your table layouts, post them, please.

Signature

Dave Hargis, Microsoft Access MVP

> Great! I am in the process of creating the tables now and loading the
> information in each table. I am following your instruction in order not to
[quoted text clipped - 108 lines]
> > > > >
> > > > > Thanks!!!

Reply to this Message

Stockwell43 - 13 Jul 2007 16:58 GMT

Ok, here is what I have so far.

Here is the assigned task table:

TaskID - Autonumber
EmployeeID - Text
Frequency - Text
TasksCode - Text
CompletionFirst - Date/Time

Employee table:

EmployeeID - Autonumber
Employee - Text

Completion of Task Table:

CompletionID - Autonumber
CompletedBy - Date/Time
Complete - Yes/No
TaskID - Text

> It would not be a bad idea to use an autonumber for the Employee ID, but not
> absolutely necessary IF the employee's unique ID will never change.
[quoted text clipped - 124 lines]
> > > > > >
> > > > > > Thanks!!!

Reply to this Message

Klatuu - 13 Jul 2007 17:34 GMT

Looks pretty good so far. Here is my version:

tblEmployee
EmployeeID - Autonumber
Employee_First_Name - Text
Employee_Last_Name - Text

tblTaskList
TaskID - Autonumber
EmployeeID - Long (Foreign Key - EmployeeID of assigned Employee)
Frequency - Text (Annual, Monthly, BiWeekly, Weekly, Daily)
TaskDescriptioin - Text
FirstDueDate - Date/Time

tblTaskComplete
CompletionID - Autonumber
TaskId - Long (Foreign Key to tblTaskList)
CompletedDate - Date/Time
CompletedBy - Long (Foreign Key to tblEmployee)

Signature

Dave Hargis, Microsoft Access MVP

> Ok, here is what I have so far.
>
[quoted text clipped - 146 lines]
> > > > > > >
> > > > > > > Thanks!!!

Reply to this Message

Stockwell43 - 13 Jul 2007 18:22 GMT

Oh, very nice!

Ok, I changed mine to match yours so we are on the same page. Only thing
was, on the CompleteBy and EmployeeID fields your data type is Long. I didn't
see that choice in the dropdown next to the name and when I tried to type it
in it told me to make a selection from the list. What data type did you
select from the list and where are you placing Long?

Also, I made a form with no links to any table (didn't use the wizard just
created new form) and place an unbound cbo on there. That's where I am at so
far.

Thanks!!!

> Looks pretty good so far. Here is my version:
>
[quoted text clipped - 166 lines]
> > > > > > > >
> > > > > > > > Thanks!!!

Reply to this Message

Klatuu - 13 Jul 2007 23:00 GMT

grrr! gotta rewrite my post. network pooped on me.

In table design, you select Numeric then at the bottom you choose from the
numeric types. I choose Long because autonumbers are long. Also, long is
faster than integer because long aligns on a word boundry and executes faster.

You combo needs a row source:
SELECT EmployeeID, Employee_First_Name & " " & Employee_Last_Name As EmpName
FROM tblEMPLOYEE;

Make the column count 2
make the bound column 1
make the column widths property 0";1.5" (This makes the EmployeeID invisible)
The form's record source should be the employee table or a query based on it.
Now to make the employee selected in the combo's record the current record
for the form, use the combo's After Update event:

Private Sub cboEmployee_AfterUpate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmployee
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

Get this working and we can move on come Monday.
Right now, it is almost Logical EOF

(End Of Friday)

Signature

Dave Hargis, Microsoft Access MVP

> Oh, very nice!
>
[quoted text clipped - 180 lines]
> > > > > > > > >
> > > > > > > > > Thanks!!!

Reply to this Message

Stockwell43 - 16 Jul 2007 13:52 GMT

Ok, I followed the instructions and completed them. My Control Source for the
cbo is =[tblEmployee] is this correct? and of course the Record Source is the
code you gave me.

> grrr! gotta rewrite my post. network pooped on me.
>
[quoted text clipped - 212 lines]
> > > > > > > > > >
> > > > > > > > > > Thanks!!!

Reply to this Message

Klatuu - 16 Jul 2007 14:28 GMT

No, the combo box should not be a bound control. Using a bound control as a
look up will cause probelms. When you update the value of the combo, you are
updating the current record. Most often, you will get a key violation error.

Signature

Dave Hargis, Microsoft Access MVP

> Ok, I followed the instructions and completed them. My Control Source for the
> cbo is =[tblEmployee] is this correct? and of course the Record Source is the
[quoted text clipped - 216 lines]
> > > > > > > > > > >
> > > > > > > > > > > Thanks!!!

Reply to this Message

Stockwell43 - 16 Jul 2007 14:58 GMT

I'm sorry, my error. I miss read your comment and placed it in the cbo
instead of the form. I have the forms record source showing tblEmployee and
took the control out of the cbo. Sorry about that.

> No, the combo box should not be a bound control. Using a bound control as a
> look up will cause probelms. When you update the value of the combo, you are
[quoted text clipped - 220 lines]
> > > > > > > > > > > >
> > > > > > > > > > > > Thanks!!!

Reply to this Message

Klatuu - 16 Jul 2007 15:00 GMT

Not a problem

Signature

Dave Hargis, Microsoft Access MVP

> I'm sorry, my error. I miss read your comment and placed it in the cbo
> instead of the form. I have the forms record source showing tblEmployee and
[quoted text clipped - 224 lines]
> > > > > > > > > > > > >
> > > > > > > > > > > > > Thanks!!!

Reply to this Message