Report: time sort based on night shift

I created a form showing an employee's work at every hour through his shift. There is two shifts - 6am-2pm, 2pm-10pm, and 10pm-6am. The hours for the night shift were in correct order ie. 22:00, 23:00, 00:00, 01:00, 06:00. Then I created a query for a report to ask me to select which day I wanted. The report showed the hours of 6-2 and 2-10 in correct order, but for 10-6 hours, it showed the night and morning hours the other way round. All those shifts were based on a same day date. How can I get the correct order?

If I understand you correctly You want to have the next morning hours (part of yesterdays Night shift) show up as part of yesterdays print out.

To do this you will need to field

Field One
WorkDate -> The date the shift started (for date selection)

Field Two
ShiftHour -> the Actutal Date and time of the shift (for sorting)

This way when you select the date of the work you will get the right date and will still be able to sort and get it to show up 23:00, 24:00, 01:00, 02:00, etc

S-

Thanks for the reply. But I was not sure how to apply it to my database. Maybe if you look at the attached and see if the problem can be solved. Run the report.

Thanks.

Paul

This is what I would do:


In Dss Detail

Make the primary key a combiniation of:
DSS on Duty
ShiftDate (Not Date and TIme)

-if you need to keep the shift start time keep it in a seperate field
-The reason for the concatenated key is becuase you current design only allow one person to be logged into a shift at a time and that may not always be the case in the future. Plus you allow this system to be eventually be used of other purposes down the road by makeing it flexible (For example, right now you are only tracking DDS personnal, why not track the people under them as well or other personnal groups?)



In Job Detail
Make the following concatenated primary key
DSS on Duty
ShiftDate
JobDateAndTime



Now when you want to print you detail report for a particualar day of the year

You will specify the ShiftDate and question (once) and sort the report by JobDateAndTime


What even you do with your design, make it flexible, and make it work properly. There isn't a way to get your current design to print the report the way you want it. You have to change your design to whatever meets your system needs and you know what they are so change it to meet those needs. If you have any what-if or other questions ask away.



Off the subject, by word of advise, you shouldn't have a table without a primary key (Access Doesn't like that and it will give you problems later down the road). And even though Access can handle spaces in the TableNames and FieldNames, don't. It makes the DB harder to use as it becomes more complex in programming as your needs become more complex.

S-

Thanks for the information.

I think I loosely applied your instructions and report produced the results I wanted. Now is there any way to avoid having to type the date every a job is carried out? It would be a great pain if a person carried out 20+ jobs during his shift.

See attached file and advise me.

Many thanks!

Paul

Here is two options

1
Create a text box that you will enter the current data in automatically. Then as you update the time in your sub form, after the users is done entering in the time, automatically add the date to the time for th user using VBA code in the AFter Update Event of the text box. The will work well for every shift but the night shift. (they will have to change that date to reflect the date that the time is for)


2 (my preference, but maybe not yours)

Don't use bound forms
I prefer to use Unbound forms with VBA code. Asuming that you are capturing the time when the finish the project or shift. I would create form (driven by barcodes) the they woudl scan the employee ID, and the job then were performing and have the system determien the current time and date and automatically add the information. if you can't use barcode, they can key their employee id and job/process ID have the system do the same thing. (this process may not fit you manufactuing process but it will give you an idea of other ways to do this)

S-