Calculating Overtime over a Range.

[Rickster66]

I have a database where order numbers are entered by a group of 25 users. The Access database is split. As each order number is entered, a time stamp using a general format is used on the table. Now, I have first been asked to generate a report based on production over time. That's fine. I can do a sql query using > and < in the where section (because it shows hours minutes and seconds, I use this ) Now, my bosses want reports where they can choose a start date/time and an ending date/ time for a specific time frame. for example, they would like a report for Monday, September 10 to Tuesday, September 19 but only for the period of 6:00AM to 8:00AM only, with totals. I don't know how to go about it.

Any help is appreciated

Richard

[Scott Price]

I have a database where order numbers are entered by a group of 25 users. The Access database is split. As each order number is entered, a time stamp using a general format is used on the table. Now, I have first been asked to generate a report based on production over time. That's fine. I can do a sql query using > and < in the where section (because it shows hours minutes and seconds, I use this ) Now, my bosses want reports where they can choose a start date/time and an ending date/ time for a specific time frame. for example, they would like a report for Monday, September 10 to Tuesday, September 19 but only for the period of 6:00AM to 8:00AM only, with totals. I don't know how to go about it.

Any help is appreciated

Richard

Is that a period of 6:00AM to 8:00AM each day?

Write up a quick sql query using your dates and including the fields you want to total (and you'll likely need >=, and <= on the date fields to get the right results) and post back with the sql code (please also wrap this in the code tags by selecting the sql text and clicking # at the top of this reply window, then manually edit the first tag to look just like this: [code=sql] )

Thanks!

Regards,
Scott

[Rickster66]

Hi Scott, Here is the sql statement, note that this statement is only for one day, I need to be able to select a range, say a week.

SELECT queues_members.members_id, queues_members.username, queues_members.initials, queues_members.queue, queues_members.cell, queues_members.status, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS BIBr, Count(queues_members.MRD) AS MRD, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.WF) AS WF, Format([date],"m/d/yyyy") AS datet
FROM queues_members
WHERE (((queues_members.date)>=#8/16/2007 5:30:10 AM# And (queues_members.date)<=#8/16/2007 8:2:10 AM#))
GROUP BY queues_members.members_id, queues_members.username, queues_members.initials, queues_members.queue, queues_members.cell, queues_members.status, Format([date],"m/d/yyyy");
 

Thanks, Richard

Is that a period of 6:00AM to 8:00AM each day?

Write up a quick sql query using your dates and including the fields you want to total (and you'll likely need >=, and <= on the date fields to get the right results) and post back with the sql code (please also wrap this in the code tags by selecting the sql text and clicking # at the top of this reply window, then manually edit the first tag to look just like this: [code=sql] )

Thanks!

Regards,
Scott

[Scott Price]

What you will need to do is separate out the date and time sections from your queues_members.date field into two separate fields. It's a good idea to rename your field something other than Date also, since this is a reserved word in Access/VBA. Then you can run a simple parameter through for the date range, and do the same with the time range as well.

I suggest something like MemWorkDate for the date, and MemWorkTime for the time field.

You can right-click on the query window and choose Parameters. Place four parameters: [Start Date], [End Date], [Start Time], [End Time] all with data types of Date/Time.

Back in the query design grid, in the criteria for your MemWorkDate type in Between [Start Date] And [End Date], and in the criteria for you MemWorkTime: Between [Start Time] And [End Time].

This will pop up the parameter windows to prompt you to enter the date range you want, as well as the time range you want.

Regards,
Scott

[Rickster66]

Thanks Scott, I will try it out.

What you will need to do is separate out the date and time sections from your queues_members.date field into two separate fields. It's a good idea to rename your field something other than Date also, since this is a reserved word in Access/VBA. Then you can run a simple parameter through for the date range, and do the same with the time range as well.

I suggest something like MemWorkDate for the date, and MemWorkTime for the time field.

You can right-click on the query window and choose Parameters. Place four parameters: [Start Date], [End Date], [Start Time], [End Time] all with data types of Date/Time.

Back in the query design grid, in the criteria for your MemWorkDate type in Between [Start Date] And [End Date], and in the criteria for you MemWorkTime: Between [Start Time] And [End Time].

This will pop up the parameter windows to prompt you to enter the date range you want, as well as the time range you want.

Regards,
Scott

[Rickster66]

It works!

Thank You Scott!!

[Scott Price]

It works!

Thank You Scott!!

You're quite welcome! Pleased it's working for you...

Regards,
Scott