A few months back I inherited a Time Recording System database. Actually there wasn't much there in terms of doing anything useful but it meant there was alot of mess to sift through and sort out. I have little experience with Access and have little time to get this system up and running. I would really appreciate it if I could get a bit of help and a few tips. Maybe I could even e-mail the db to someone to have a look at?
Basically there are three main tables recording the times a user has spent on a particular project and then displaying reports based on where people have spent there time on which projects, as well as general reports on listings of which projects are part of which section/department.
Table 1 - USER
Fields - Initials (primary key) (only 8 users so initials are all unique)
- Name
- Job Title
- Telephone No.
Table 2 - PROJECT
Fields - Project Ref No. (primary key)
- Project Title
- Project Type
- Budgeted Days for Project
- Report needed?
- etc.. etc..
Table 3 - PROJECT PROGRESS
- Date (primary key)
- Initials (primary key) <---[one to many relationship with user table]
- Project Ref No. (primary key) <---[one to many relationship with project table]
- Project Title
- Hours [spent on each audit]
There are three further lookup tables (one field only) to allow the db user to input either the project ref no, project title, project title from a drop down box.
After hours of playing around I noticed the original author had created a form for time entry based on a query which took date, initials, project ref no, and hours from project progress table and project title from project table. This allows the database user to enter the project ref no. and the project title fills itself in automatically. Great! only problem is that all the information entered ends up in this query and not the Project progress table which is what all the reports are created from. What's going on!? Do I have to redo the form? I don't understand how to utilise using dates with this form and how it all stores data and whether it will create problems with queries, etc..
I hope what I've written makes sense. Thanks in advance for any assistance. If what I've written above about the time entry form makes no sense is the wrong way to do it, how would I go about doing one properly from scratch? I want the db user to be able to enter their initials and a date - bringing up any time entries for that day. And then the person can enter a Project ref no. - the project title pops up next column to confirm that it was the right reference, and then the user can enter how much time they spent on it. All these entries are then stored nicely in a table I can refer to later and create reports and queries from. Most of the Reports/queries appear to be in place already (and seem to work) so I don't really want to alter the name/structure of the PROJECT PROGRESS table too much.
Basically there are three main tables recording the times a user has spent on a particular project and then displaying reports based on where people have spent there time on which projects, as well as general reports on listings of which projects are part of which section/department.
Table 1 - USER
Fields - Initials (primary key) (only 8 users so initials are all unique)
- Name
- Job Title
- Telephone No.
Table 2 - PROJECT
Fields - Project Ref No. (primary key)
- Project Title
- Project Type
- Budgeted Days for Project
- Report needed?
- etc.. etc..
Table 3 - PROJECT PROGRESS
- Date (primary key)
- Initials (primary key) <---[one to many relationship with user table]
- Project Ref No. (primary key) <---[one to many relationship with project table]
- Project Title
- Hours [spent on each audit]
There are three further lookup tables (one field only) to allow the db user to input either the project ref no, project title, project title from a drop down box.
After hours of playing around I noticed the original author had created a form for time entry based on a query which took date, initials, project ref no, and hours from project progress table and project title from project table. This allows the database user to enter the project ref no. and the project title fills itself in automatically. Great! only problem is that all the information entered ends up in this query and not the Project progress table which is what all the reports are created from. What's going on!? Do I have to redo the form? I don't understand how to utilise using dates with this form and how it all stores data and whether it will create problems with queries, etc..
I hope what I've written makes sense. Thanks in advance for any assistance. If what I've written above about the time entry form makes no sense is the wrong way to do it, how would I go about doing one properly from scratch? I want the db user to be able to enter their initials and a date - bringing up any time entries for that day. And then the person can enter a Project ref no. - the project title pops up next column to confirm that it was the right reference, and then the user can enter how much time they spent on it. All these entries are then stored nicely in a table I can refer to later and create reports and queries from. Most of the Reports/queries appear to be in place already (and seem to work) so I don't really want to alter the name/structure of the PROJECT PROGRESS table too much.