Loss time query

[MWads4]

I have created a database based on our sickness and absence, it all runs correctly but need to add another report.

Basically I want to produce a report on loss time hours.

For example if I had someone on a 40 hour contract and they are off for 10 working days the loss time would be 440 hours for that person.

I need a report by a person and for all employees for a time inputted in by the user.

But what if they work mon - fri but the business is a 7 day operation and one person only works Thursday - Sunday.

Is any of this possible in access, I have done this in excel with someone which uses a load of pivit tables and uses networking time but cant figure how or even if its possible in access.

Does someone have a database that does this or explain to me what I would need to do and what formula example to use to put me on the right path.

I hope it is clear on what I am trying to do.

 

[Minty]

You would need to have your data stored correctly, so can you give us some sample data to work with that is in exactly the same format as your real data.

You can post a spreadsheet with the sample data.

 

[MWads4]

I can supply them but they work differently.

On the spread sheet its stored in different sheets, each sheet is a week.

In the database its all in one table with the data all being the same and I run querys on those dates.

 

[Minty]

That's fine - it's the database example data we need to see.

 

[MWads4]

My database is 5 meg and the site will only allow 2mb, I have tried zipping it but it still only goes to 4.

Any ideas on what I can do

 

[MWads4]

All I can do is upload a the tables that are in the database with test records, how can someone help me with a query on this data.

Thank you minty

 

[Minty]

Okay - your data doesn't appear to have all the information you would need to get the results you want, unless I'm making assumptions incorrectly.

How do you know what days an employee is supposed to work?
How do you know how long they were off for (What does the Number field represent?) and Number is a poor choice of field name - I think its a reserved name.

The queries can certainly be written to produce the results you want, but only if you have the data stored in the first place.

Edit - Also it appears that the emp name is being stored in the sickness record - not the employee ID - this isn't correct.

 

[MWads4]

Some keys points from yourself.

The days off they had I was going to add in a return date field in the tblSicknessRec.

I was going to add a table with employees work pattern and putting in a relationship to link them.

Number is a reserved name and this was put in by error and was going to change at a later date I did the same with name and changed that, this was just to create a working demo. I have not done access in over 10 years when I was at uni and become very rusty.

So if I do the following i.e a return date in tblsicknessrec, work patterns will would you be able to help me on this query or be able to provide an example. Or what else would I need?

I really am very thankful for this!, I have looked for youtube videos on this topic but nothing!

 

[MWads4]

why cant you have the name in two fields. They are linked by oracle number, was originally using oracle as primary key as this is unique number within the company.

Number is contracted hours, don't asked me why I was using that field name for that, rushing to much.

Thank you again minty

 

[Minty]

Basically if you can write down the sums on paper, and all the information you need to do the sums is in Access then yes, you can do it Access.

If you can't then Access can't invent missing information. Start with the pen and paper model, this will be much quicker, and often highlights a missing information point or a data structure issue.

Once the model is right the rest becomes simple.

It might be worth reading up on normalisation - to make sure your data gets stored sensibly.

 

[MWads4]

maths was never my strong point but thank you for your advice, still none the wiser on getting this last part of my project demo finished but really appreciate you commenting and replying back to my questions very quickly.

 

[Minty]

Date calculations are slightly entertaining in most programming languages, but have a go with some basic queries, look up the DateDiff and DatePart functions and play with their results.