Variable Field Names?

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?

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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?

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" wrote:

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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?

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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" wrote:

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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?

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?

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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" wrote:

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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?

The crosstab would be read only. You could create a spreadsheet type
interface but it would require some code to implement.
--
Duane Hookom
Microsoft Access MVP


"DumbWithData" wrote:

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?

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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" wrote:

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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?

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



"Duane Hookom" wrote:

The crosstab would be read only. You could create a spreadsheet type
interface but it would require some code to implement.
--
Duane Hookom
Microsoft Access MVP


"DumbWithData" wrote:

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?

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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" wrote:

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
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?

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/acc...abaseDesign101

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
Is there any where you would recommend that I start looking to be able
to do this?

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.

--
Duane Hookom
Microsoft Access MVP


"Allen Browne" wrote:

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/acc...abaseDesign101

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DumbWithData" wrote in message
...
Is there any where you would recommend that I start looking to be able
to do this?