multiple relationships on a single form

[Jeff @ CI]

I am migrating a form from a spreadsheet to a database. The spreadsheet
lists employee names and two cells each for performance data entry - repeated
at four times in a day - all in column format. A third field will be
calculated with each column's entries. A fifth column is used to display the
max numbers in one column.

Does anyone know of a way to populate a form from a query that sorts out
active employees and provides for data entry for the four collection times in
a day? I want to be able to display all employees and the five columns at
once - referenced by date. The data collected is in its own separate table
and contains existing data which has been manually entered from the
spreadsheet form. I am seeking a single entry method versus the multiple
entry system in place.

Employee attendance varies from day to day and not every employee will be
working the entire day (may only report performance 2 or 3 times of the day.)

I do have a copy of the spreadsheet form I am trying to replicate. I
already use a form that allows me to enter in data one employee at a time,
but this does not provide the solution that I need. I have basic knowledge
of queries and almost no experience in cross tab queries.

Thanks for the help anyone can provide in advance.

Jeff

 

[Albert D. Kallal]

Often, it is simply much better to list all employees, but then you click on
a employee and launch a new details form that lets you edit employee
information (phone numbers, address, etc).

And, on this employee details form, you can also thus display a sub-form in
which you can enter the performance information.

You *can* also build a form in which you display the employees on the left
side, and as you navigate up/down through the list you an have a sub-form on
the right side display (and edit) the performance information.

here is some screen shots, and note the VERY LAST screen shot where I have
people on the left side making a donation and on the right side, you can
break out the donation to diffent accounts.

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

In either case, you need to use a sub-form to allow editing of relational
data (the performance data is no doubt a related table to employees, or
perhaps a table of "days", and you set the employee id, and the data
numbers -- you have a choice as to your design here). You might consider
making a table for the "day".. that way, you don't ever have to repeat the
date over and over for each data point of that day. It really much depends
on how your UI is going to look, and how you track/report on the data. So,
you might relate the performance data to a "day" table. This would make
sense especially if there is certain other factors for a given day (like
template, weather...whatever). In addition, this day table would mean that
you never type the date over and over for each data point.

However, If there is JUST the date, then you might consider relating the
performance table to employees, and then you have to repeat the date over
and over as you enter each data point. Only you can decide which is a better
approach based on your particular circumstances.

 

[Jeff @ CI]

Often, it is simply much better to list all employees, but then you click on
a employee and launch a new details form that lets you edit employee
information (phone numbers, address, etc).

And, on this employee details form, you can also thus display a sub-form in
which you can enter the performance information.

You *can* also build a form in which you display the employees on the left
side, and as you navigate up/down through the list you an have a sub-form on
the right side display (and edit) the performance information.

here is some screen shots, and note the VERY LAST screen shot where I have
people on the left side making a donation and on the right side, you can
break out the donation to diffent accounts.

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

In either case, you need to use a sub-form to allow editing of relational
data (the performance data is no doubt a related table to employees, or
perhaps a table of "days", and you set the employee id, and the data
numbers -- you have a choice as to your design here). You might consider
making a table for the "day".. that way, you don't ever have to repeat the
date over and over for each data point of that day. It really much depends
on how your UI is going to look, and how you track/report on the data. So,
you might relate the performance data to a "day" table. This would make
sense especially if there is certain other factors for a given day (like
template, weather...whatever). In addition, this day table would mean that
you never type the date over and over for each data point.

However, If there is JUST the date, then you might consider relating the
performance table to employees, and then you have to repeat the date over
and over as you enter each data point. Only you can decide which is a better
approach based on your particular circumstances.

 

[Jeff @ CI]

Often, it is simply much better to list all employees, but then you click on
a employee and launch a new details form that lets you edit employee
information (phone numbers, address, etc).

And, on this employee details form, you can also thus display a sub-form in
which you can enter the performance information.

You *can* also build a form in which you display the employees on the left
side, and as you navigate up/down through the list you an have a sub-form on
the right side display (and edit) the performance information.

here is some screen shots, and note the VERY LAST screen shot where I have
people on the left side making a donation and on the right side, you can
break out the donation to diffent accounts.

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

In either case, you need to use a sub-form to allow editing of relational
data (the performance data is no doubt a related table to employees, or
perhaps a table of "days", and you set the employee id, and the data
numbers -- you have a choice as to your design here). You might consider
making a table for the "day".. that way, you don't ever have to repeat the
date over and over for each data point of that day. It really much depends
on how your UI is going to look, and how you track/report on the data. So,
you might relate the performance data to a "day" table. This would make
sense especially if there is certain other factors for a given day (like
template, weather...whatever). In addition, this day table would mean that
you never type the date over and over for each data point.

However, If there is JUST the date, then you might consider relating the
performance table to employees, and then you have to repeat the date over
and over as you enter each data point. Only you can decide which is a better
approach based on your particular circumstances.

Thanks for the ideas.

I looked at the screenshots you linked in your reply. Not what I am looking
for. I can send you screenshots of my relationships (w/ fields listed) and
the spreadsheet to more fully communicate the concept via email. My plan was
to query for the records based on date to retrieve the days performance in a
report format. I can also default the date field to =now() to solve the
problem of manually entering the date for each sub record.

I think my solution is in a many to many relationship form which brings the
tables together and then need to use queries (crosstab???) to actually
populate the names on the form. Making the report seems simple enough using
a query. But how to do this without any data and in a form format is the
problem.

Jeff
(e-mail address removed)

 

[Albert D. Kallal]

My plan was

to query for the records based on date to retrieve the days performance in
a
report format.

Right, but the above is taking about a report AFTER we entered data...is it
not? Your original question was about some ideas for data entry. The
reporting side should not be difficult at all once you built a good data
entry system.

I can also default the date field to =now() to solve the
problem of manually entering the date for each sub record.

Do NOT use Now(), as that also logs the time portion of your computer into
he field (your queries will then have to all include a time part to work).
Your default should be date(), not now().

Further, while you can default the date value, why would you store the same
date over and over if you don't have to? (all boys and girls: do you need to
store the same data over and over? answer why would you?).

It not a big deal on this repeating date. However, it just a good
suggestion, and one that reflects good data modeling. By modeling your
relationships correctly, then you not only eliminate repeating data, but
also often increase flexibility of the appcation.

for example, what happens if the data can't be entered today, and you have
to enter tomorrow? The suggested design allows you to enter a date once, and
then not have to repeat it. So, you could for example enter yesterdays data
today. And, further, if you realize that you entered the wrong date, then
you ONLY have to change it in once place, and all of the data points will
reflect the change. Again, this is a MINOR point, and often the effort to
gain this extra flexibly is not worth the time. So, defaulting to date() is
likely fine in your case. As I said, if there is ADDITIONAL data that needs
to be logged for that day (like weather, temperature, whatever..then yes I
would in fact build a day table).

I think my solution is in a many to many relationship form which brings
the
tables together

No, not really.
It don't matter if it is "many to many". When design tables, it always that
you have a parent table, and then a child table. That ALL you need to think
about.

Making the report seems simple enough using
a query. But how to do this without any data and in a form format is the
problem.

I don't understand the above???? You have to build and design your tables.
You then build and design your forms to enter the data. You then build your
reports on that data....