Question Date Time Calculations

[liam9911]

Not sure if im in the right section here but will give it a go.

I am developing a Rota system that i want to be able to time manage my staff. I have a in and out for days monday - sunday and i want to be able to add the man hours that i have uploaded onto the system. I get it to work if i insert data into all the days but my staff dont work everyday so some of the fields are blank. is there a way to miss that day if the field is blank?

Expression for minutes worked :-

=DateDiff("n",[monday in],[monday out])+DateDiff("n",[Tuesday In],[Tuesday Out])+DateDiff("n",[Wednesday In],[Wednesday Out])+DateDiff("n",[Thursday In],[Thursday Out])++DateDiff("n",[Friday In],[Friday Out])+DateDiff("n",[Saturday In],[Saturday Out])+DateDiff("n",[Sunday In],[Sunday Out])

Expression for hours worked :-

=[text61]\60 & Format([Text61] Mod 60,"\:00")

Thank you

 

[plog]

You need to drop what you have and take 2 steps backward. Your tables are not properly structured. You need to read up on normalization: http://en.wikipedia.org/wiki/Database_normalization

You need to set up your tables properly to handle your data and getting the information you want out of it. Instead of a field for every day of the week (and then trying to avoid those fields without data), you need a seperate table to capture every start/end date/time.

The data in those 14 fields need to go into a table structured like so:

ShiftTimes
ShiftID, ShiftStart, ShiftEnd
1, 7/12/2014 11:00AM, 7/12/2014 12:00 PM
2, 7/13/2014 11:00AM, 7/13/2014 12:00 PM
3, 7/15/2014 11:00AM, 7/13/2014 12:00 PM
etc.

Those 14 fields (Start/End for Monday - Sunday) would become 7 records in the ShiftTimes table (or less if you need to miss a date.) Then when you need totals, you take the differences between the ShiftEnd and ShiftStart values (using DateDiff) and add them all up in a query (using SUM).

Again, 2 steps back and properly structure your tables.

 

[liam9911]

Thank you for your responce .
Im sorry i dont think that would be the best way around it. I am NOT creating a time sheet that monitors the times that they have clocked in or out because your correct having a more simple form that creates the day and the time in and out for each day would be correct.

I am creating a rota to be printed and pinned on the wall that will have the staff name and the shifts they are doing on the right hand side. because of this that is why i have created the table with fields for different days. This will made it easier to understand for my staff.

Again thank you for your quick response

 

[burrina]

Looks like your in good hands.

 

[plog]

This will made it easier to understand for my staff.

I said nothing about the output of your database. You're not in Excel any more: data storage is not data output is not data input. You can still generate any report you want in the format you desire.

You need to correctly structure your tables to hold the data you want (Step 1). Then you build the reports you want (Step 2), then you build input forms (Step 3). Like I said, take 2 steps back. Get your structure right and the other things will fall into place.

 

[MarkK]

I agree with plog that the structure you show in post 1 is exceedingly cumbersome. I recommend his approach for tables, and then learn how to show that data in a tabular form, possibly using a crosstab query.

Unless this is just for display, and then I wouldn't use Access at all. It doesn't make sense to me to push a sloppy data structure into Access just to print it in a particular format.

 

[llkhoutx]

I'm a firm believer in the maxims, "if it ain't broke, don't fix it" and "(KISS) keep it simple stupid."

Elegant, complex code is great for the purists, but a nightmare to understand, let alone fix, after it's created.

No offense intended.

 

[MarkK]

Elegant code is small and simple. Elegant <> complex.