Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Timesheet Reporting Database Design Advice Needed

Posted on 2006-05-11
10
Medium Priority
?
738 Views
Last Modified: 2012-08-13
Hi
I need to come up with a database for my organisation that basically does the following:
1) Allows selection of month and year - preferably from a dropdown - this could be say May 2006
2) This then opens to another form that allows selection of an employees name.
3) This then opens a third form that allows the input of hours data worked on a number of different services for that employee over the month selected. Different employees work on different days but have a fixed number of hours.

The purpose of this database is so we can provide reports that will allow us to show who worked at what service, when and the total number of hours for each service worked in a month or a quarter.

Trouble I'm having is deciding on the relationships and tables.

I have tblemployee
EmployeeNo - PK
Emp_firstname
Emp_lastname
ContractHours = No of hours the emploee works per week

tblDate
DateID - PK
Date
UnitID - FK
EmployeeNo - FK

tblUnit
UnitID -PK
UnitCode
UnitName
EmployeeNo - FK

When the data is inputted it's going to be much easier for the inputter to select a month and year, then an employee - then fill in by tabbing across a form the hours for each date worked at each site.

I've posted a sample here of the timesheet that data will be inputted from if that helps make what I'm on about any clearer.
http://www.ghost-watch.co.uk/timesheet.pdf

I guess what I'm struggling with is the date and how the relationships can be setup. One employee can work at any number of units in a month. One unit can have many number of employees working at it per month. I'm thinking I need a junction table for this but where does the date fit in? I'm an access newbie and I seem to have a mental block with this. Please help!
Thanks
Deb :))
0
Comment
Question by:Debsyl99
  • 5
  • 4
10 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16657050
If you have a many to many relationship then u do need a junction table

this table links the two primary keys and can also hold additional info appropriate to that relationship

e.g.

tblEmployeeUnit
EmployeeUnitID - PK   (this is optional u can have if u want, always best to is what I believe)
EmployeeID
UnitID
SomeDateField?


this now holds info about what employess are in what units and vice versa

now u drive any queries from there, linking in the employee and unit tables to get the further info
0
 
LVL 2

Expert Comment

by:wobblynut
ID: 16657073
What you have not presented above is the table where you will store the inputted data.
You need to consider how you will store the data against each day in each month.
I have not given you a solution - as a newbie i think clues are better!



0
 
LVL 20

Author Comment

by:Debsyl99
ID: 16657531
Hi
Thanks Wobblynut for the guidance - but I'm short on time - I need to get this done for work and would really like a solution which is why I posted a question for 500pts and why the answers tend to get called solutions here! The requirements have changed slightly as I now find that I only need to report on the total of hours spent by each member of staff in each unit for each month. And similarly for each unit. I must be having a dense day because my brain's shorted out and I don't know how to do it.

I need to store the date - I only need month plus year. For every date, x number of have staff have visited y number of units and have spent z number of hours at each. I need a form where I can enter month and year. On a subform or linked form I can then enter an employees name. On another subform I can then type in the various unit codes and hours worked at each for that employee in that month. All this can then get stored in the tables from this form. Maybe in my next lifetime I'll be an Access developer but in this one I'm sticking to being a network admin. It's just sometimes I need to be a jack of all trades and this is one of them. Please someone just tell me what I need to do before I throw this pc out of the window.

Thanks
Deb :))
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16658832
to store specifc info about a employee/unit

like I said before, create your junction table with the keys then store any additional info u want
i.e.

tblEmployeeUnit

EmployeeUnitID   (make this autonumber)
EmployeeID
UnitID
TotalHours
VisitDate



so your query to display x number of staff visiting y number of units spending z number of hours

select e.Emp_firstname, e.Emp_lastname, u.unitcode, u.unitname, j.totalhours, format(j.visitdate,"MMM-YY")
from tblEmployeeUnit j, tblEmployee e, tblUnit u
where j.employeeid = e.employeeno
and j.unitid = u.unitid



0
 
LVL 20

Author Comment

by:Debsyl99
ID: 16668022
Hi rockiroads
Thanks for your help so far. I've now got:
TblEmployees
EmpID - PK
Emp_firstname
Emp_lastname
EmpNo

TblUnits
UnitId - PK
UnitCode
UnitName

TblLink
LinkID - PK
EmpID - FK
UnitID - FK
Hours - (Hours spent per person at each unit)
VisitDate - Date visited

The problem I'm having now is getting data into the tables in a simple way through forms. If I create a query based on everything then I need to populate the link table with a value ie VisitDate or Hours first or the foreign keys won't populate. I can enter the data ok but I have to have a new record for every single contact with every unit and this makes data input arduous. Ideally I want to be able to enter the date in a form - then open a linked form and choose the employee name. Then in a subform just choose the units and add the hours. From this everything should get populated properly in the tables. However I'm struggling with this. Can you offer any pointers on this please?
Thanks for any help
Deb :))

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16668779
ok, lets try this trying what u want to do

say u got a form, lets say for arguments sake its called frmRecordHours

ok, u enter a date, u can have a prompt for employee as well - here is where your linked form comes in, u can have a button called Find Employee which does what u want, finds a employee and returns you back to frmRecordHours, and populates the employee name

Now you have a date and employee filled in

You could then display a listbox which shows for the selected date (your first prompt), what that employess current recorded hours are
So after user enters a date and employee, the afterupdate functions on both of these, will call a funciton - this verifies both a date and employee id is recorded on the form, and set the rowsource of the form to display all info (that first query I gave u using the junction table)

--

How many units are we talking about here? You could create it as a listbox or combobox ?
If its possible to place in this type of object then do this

Add in that object plus a prompt for number of hours then a button called cmdRecord - this will prompt you to say
"Do you really wish to record the hours ... spent on this employee ..."
If they answer yes, then add a record into your junction table
You know the two id's plus date and hours

--

If u are talking lots of units and want a subform, then how do u propose to display the units? same principle, whatever u decide, have a prompt for hours and record button

--

Using the idea of a listbox that displays a employee hours, u can create a edit form, to modify the number of hours recorded or to delete an entry
User clicks on listbox, on the click event, populate some textboxes, one of which is editable
Also have a delete button

0
 
LVL 20

Author Comment

by:Debsyl99
ID: 16670714
Hi
I've made quite a bit of progress on this now. The trick seems to be using the junction table as the basis for the subform and linking the empID fields. One last question before I close this out if you can help. I've now got a form. From a combo box I can select an employee, then due to some code I've added to the after update property it'll now load another form with all the correct employee details listed. In the subform attached to this second form I can then add the unitcode from another combo box, add hours, add dates. It all updates each table fine. The only problem that I now have is that the subform is showing all the records previously added for this employee. Is there any way I can get the subform to open in add mode - ie so an input clerk can just get on with entering data rather than have to scroll through the existing records in the subform till they get to the last one?
Thanks again
Deb :))
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16671308
Hi Deb, it looks like your got the idea now. The junction table is your main source now when it comes to looking at employee/unit relationships. Everything has to drive from here

If u want a form to go straight into add mode, there is one of two ways depending on how you open the form

This line emulates u clicking the add
    DoCmd.GoToRecord , , acNewRec

This line opens a form straight for add
    DoCmd.OpenForm "myform", acNormal, , , acFormAdd


It may seem complicated at first, but once u get the idea, u realise how easy it is
0
 
LVL 20

Author Comment

by:Debsyl99
ID: 16673811
Hi
Hmm - it's not working as I already tried that. Thanks for your help though. I still have plenty of things to sort out but I'll be exceeding the limits of my original question if I carry on with that here so I'll close this out now. Plus I have unlimited points so I may as well use them!

I hate Access - I get given a job to do with it, then I don't go near it for 6 months. When I come back to it it's like staring from scratch almost. Moan moan moan! Thanks again - you got me there with the tables so this is well deserved.
Best wishes
Deb :))
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16673920
Ok Deb, best of luck

I didnt think much of Access either, some projects required me to clean some up. Since I come from a 3GL background, I found Access very easy.

When u say, its not working, r u talking about the add new record bit?
tell u what, try this
use the toolbox wizard, add a button, select record operations, add new record
then see what code it produces


I tried adding the goto record in my test form and it went straight to add mode (note this works only for bounded forms)

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
End Sub

If u have an unbounded form, u have to call your add button method on Form_Load


0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question