Really need some help with time recording system

[mrnorth]

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.

 

[Mile-O]

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]

What's going on with that third table? You don't need the project title there as it's already stored in the the Project table

Some of the field names are no good either - Name and Date are reserved words.

Anyway, it looks like you are wanting a many to many relationship but haven't defined it properly. Also, is it possible that more than one person can be assigned to a project?

This is just a quick reworking based on what you've said...

[b][u]tblJobTitles[/u][/b]
[b][i]JobTitleID[/i][/b]
JobTitle

[b][u]tblUsers[/u][/b]
[b][i]UserID[/i][/b]
Forename
Surname
[color=red]JobTitleID[/color]

[b][u]tblProjectTypes[/u][/b]
[b][i]ProjectTypeID[/i][/b]
ProjectType

[b][u]tblProjects[/u][/b]
[b][i]ProjectID[/i][/b]
ProjectName
[color=red]ProjectTypeID[/color]
DaysBudget

[b][u]tblUsersToProjects[/u][/b]
[color=red][b][i]UserID[/i][/b][/color]
[color=red][b][i]ProjectID[/i][/b][/color]

[b][u]tblUpdates[/u][/b]
[b][i]UpdateID[/i][/b]
[color=red]ProjectID[/color]
DateOfUpdate
HoursSpent

 

[mrnorth]

Do I really need a UserID in User table as there are only 5-7 of us and it is very unlikely that someone will have the same initials. I have at the moment Initials as the unique identifier in User table.

I have since taken out Project Title from Project Progress as I realised it was the reason why the Project Progress table was not updating properly.

I am going to leave out tblJobtitles and tblProjectTypes as they seem unecessary. I'm not sure how it helps for it to be set up like that?

What is the tblUsersToProjects for? Is it to link the User to the Time input (update) Could I not just have UserID in place of UpdateID? - and the UserIS is linked to UserID in tables (this is what I have currently)

More than one person can be working on the same project, but all that is really necesary is that reports can be created to show
- How much time each person has spent on a particular project in a given month/qtr/year.
- Who has spent the most time on a particular project (pie chart?)
- Whether the total time spent on a project has gone over the budgeted days for that project.
(To name but a few!)
The Manager basically wants everyones time accounted for and what projects are taking alot/alittle amount of time (yes, he trusts us to enter our times accurately/honestly)

One more thing: "Some of the field names are no good either - Name and Date are reserved words." - What do you mean by reserved words?

Thanks very much for your help so far. I'm still learning! Really appreciate it.
Need to get this done really soon and am really stressed - so will paypal a few $/£ if anyone wants!

 

[Crilen007]

Do I really need a UserID in User table as there are only 5-7 of us and it is very unlikely that someone will have the same initials. I have at the moment Initials as the unique identifier in User table.

What if someone gets married and changes initials, everything gets messed up. Always use a number.

What is the tblUsersToProjects for? Is it to link the User to the Time input (update) Could I not just have UserID in place of UpdateID? - and the UserIS is linked to UserID in tables (this is what I have currently)

You have a many to many relationship. This allows multiple users per project, which in the next sentence, you say you require.

More than one person can be working on the same project, but all that is really necesary is that reports can be created to show
- How much time each person has spent on a particular project in a given month/qtr/year.
- Who has spent the most time on a particular project (pie chart?)
- Whether the total time spent on a project has gone over the budgeted days for that project.
(To name but a few!)
The Manager basically wants everyones time accounted for and what projects are taking alot/alittle amount of time (yes, he trusts us to enter our times accurately/honestly)

Than you require multiple users per project do you not?

One more thing: "Some of the field names are no good either - Name and Date are reserved words." - What do you mean by reserved words?

Words that access uses internally. Do not use those words. Have words that are unique to access.

 

[Mile-O]

What if someone gets married and changes initials, everything gets messed up. Always use a number.

In addition, it's easier to index relationships on numbers than it is on text. Numbers, only take up a tiny bit of space compared to text.

Also, these are the Reserved Words of Access 2002 and Access 2003 - previous versions of Access vary little with respect to these. Note, however, that this list is not finite as, if you add any new reference whether they be from the Reference Dialog box (accessible from Tools -> References in any module) or via the installation of an ActiveX control then any functions, methods, and properties unique to these will also become reserved words.

 

[mrnorth]

Thanks again. I'm going to give this another bash tomorrow in the office. Is it normal that I find this so bloody complicated!?

I hope this all gives me the results I need. What if it doesn't? Is it easy to change?

Remember, keep the language simple! I'm completely useless!

 

[mrnorth]

You have a many to many relationship. This allows multiple users per project, which in the next sentence, you say you require.

Than you require multiple users per project do you not?
.

but it seems to work at the moment. I have UserID instead of UpdateId in the updates table and the tables contains hundreds of entries. Each entry is unique because the UserID, Date,Timespentonproject, ProjectID cannot happen more than once. If the User needs to add some more time to the same project on the same day then they just put their UserID in and date and it brings up the datasheet table - and they can change the 4.3 hours corresponding to ProjectXC2321 (for example) to 5.8 hours or whatever.

Am I missing something very obvious and silly that could go very wrong further down the line?

 

[Mile-O]

Interesting that you only want to add how long they spent per day on the project. Personally, I'd want to know what they did on the project during that time. Hence, each Update in my suggested table lets you add this in, you can break a day down per person per activity per project.

Allthough a new table would need to be added for activities (I don't like free type - it encourages storytelling.)

 

[mrnorth]

Well each project is budgeted for between 5 and 40 days each year and there are about 50 projects - 20 that need to be done every year, 20 every other year, and another 10 whenever they can be fitted in.

There are so many 'activities' that are carried out during a particular project that it would be much too much hassle for the user to define and remember what sort of activity they did on a particular project within a particular day. At the moment they have a hard enough time remembering what they were working on last last wednesday.

Would this sort of setup still work for me? - with the following adjustments of the tables you listed earlier?

tblJobTitles
JobTitleID
JobTitle

tblUsers
UserID
Forename
Surname
JobTitleID

tblProjects
ProjectID
ProjectName
ProjectTypeID
DaysBudget

tblProgress
UserID
ProjectID
DateOfUpdate
HoursSpent

At the moment the user is also able to enter for example; 36 hours on a particular day for a particular project which could represent the time spent on it the entire week (they enter it at the end of the week - as users are too lazy to enter times everyday). The reports still appear to work as we mainly want to see how much time was spent in a particular quarter / month rather than see what happened on a particular day. If a day shows more than 7-8 hours then we know that person must have entered the time they spent on something from another day too. Does this method pose any other problems.

Thanks again. Eagerly await your response!

Sorry - I have one more dilema. What if I had TWO types of project (ie. one for X organisation and one for Y organisation).They should have the same fields and same reference codes - but one has Y in front (Y-1234) and the other has X (X-1234) in front. Can I put all this in the same table. In this case would it be better to bring back in the tblProjectTypes where I have ProjectTypeID - X - Y
Project Type - Xavier Corp - Yellow City Council

 

[Crilen007]

for calculation sake, have a field that indicates if its a daily or a weekly update.

Also, the user should type in SOMETHING to indicate what they did.

They don't have to give every tiny detail.

 

[mrnorth]

for calculation sake, have a field that indicates if its a daily or a weekly update.

Also, the user should type in SOMETHING to indicate what they did.

They don't have to give every tiny detail.

Would you be able to elaborate on how to do this/what this would look like.

I have plenty of ideas but don't know how to implement it and whether or not it will all come crashing in wards at a later date when there is more information in the db - like what is happening now.

I really don't have much of a clue in Access - Thanks.

 

[Crilen007]

Just add another field to the table where they enter their hours, call it "EntryTypeID" (Number)

and make another table



tblEntryType
-----------
EntryTypeID (Autonumber, PK)
EntryType (text)

and link it using the ID

add to this table the following:

"Weekly"
"Daily"

thats it.

 

[mrnorth]

Just add another field to the table where they enter their hours, call it "EntryTypeID" (Number)

and make another table



tblEntryType
-----------
EntryTypeID (Autonumber, PK)
EntryType (text)

and link it using the ID

add to this table the following:

"Weekly"
"Daily"

thats it.

Would this primarily to see at a glance which records/entries have times entered on the basis of a day and of a week? Would it not be another variable to juggle when I come to creating queries and reports?

 

[Crilen007]

You could ignore it if you don't need it. Just allows you to beable to differentiate between the two, where if you didnt, you couldnt. But if you don't want to, you can exclude it from queries you don't need it in. Just better to have it done now, than if later, if you ever needed it.

In other words, it doesnt hurt, and you can ignore it if the query does not need it. But if it does, its there.


For anyone whose seen AVP.

"Its better to have it and not need it, than to need it and not have it"

 

[mrnorth]

Gotcha. Thanks

Just need to wait for SJ McAbney's reply now!

 

[mrnorth]

Gotcha. Thanks

Just need to wait for SJ McAbney's reply now!

Wheres he gone!?