Variable Field Names?

[DumbWithData]

I am trying to set up part of my database to track employee time. We have
been simply doing this on an Excel sheet. I want to be able to enter the
employees name and the amount of time that they worked on any particular work
item. Given that there are hundreds of work items, Creating a field for each
seems to be the wrong way to go. Could someone please help?

 

[Allen Browne]

You need:
a) A table of employees, with an EmployeeID primary key.

b) A table of work items, with a WorkItemID primary key.

c) A 3rd table with these fields:
- WorkID AutoNumber
- EmployeeID Number who
- WorkItemID Number did what
- StartDateTime Date/Time when employee started on workitem
- Minutes Number how long (duration in minutes)

 

[DumbWithData]

Firstly, thank you Allen. I am wondering how I would set this up in a form
that had the same look/feel of an Excel sheet. I imagine that this form
would be linked to the third table...?

 

[Allen Browne]

Interface it as a form with a subform.

For example, you might create a form with a record for each work item, and a
subform (in continuous view) bound to the 3rd table. So, you find the
relevant work item, and then add another row each time someone works on it.
You can use a combo box in the subform for selecting the employee.

Alternatively, you could use a main form bound to the employee, and a
subform bound to the 3rd table. You find the employee's record, and then add
another row to the subform each time they work on a job (with a combo for
selecting which job it is.)

If you want to print something that looked like your Excel spreadsheet, use
a crosstab query.

 

[DumbWithData]

You stated that I could use a crosstab query to print something that looked
like an excel worksheet, but would there be any way to enter data in
something that looks like an Excel spreadsheet?

 

[Duane Hookom]

The crosstab would be read only. You could create a spreadsheet type
interface but it would require some code to implement.

 

[DumbWithData]

Is there any where you would recommend that I start looking to be able to do
this?

 

[Allen Browne]

There are too many factors here to post a reply to that broad question.

Access is a relational database. To design a good database, there's quite a
bit to learn about how to create a relational schema. If you want a couple
of simple little examples, see:
http://allenbrowne.com/casu-06.html
http://allenbrowne.com/casu-23.html
If you want to read more about normalization, here's a link to some links:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

If you want to build a spreadsheet in Access, you can do it, but you are
creating more problems than you are solving. IMHO it's not worth doing, so I
am not prepared to walk you down that path.

The relational structure suggested in the previous reply does not look the
same as Excel, but it is relational and therefore queryable.

The code to create a form that gives this kind of interface assumes a great
deal of knowledge, and is well beyond what we can cover in a newsgroup
posting.

 

[Duane Hookom]

I agree with Allen. Don't get too hung up on creating a user interface that
matches a previous style.

If you really want a spreadsheet type data entry, I would probably use a
crosstab query to push values to a temporary table for editing. Then use
queries and/or code to push the data back to normalized tables.