Counter for entries per hour

[RiskIt]

Hi,



First time posting here, I have been tasked with making a database for my work. The purpose of the database is to record serial numbers from hard drives that we shred.
I have the database working and as my work want it apart from one thing that they are requesting and I am unsure of how I can achieve this.

The database has an input form which consists of the client we are doing the shredding for the date which auto fills and then a button which saves and add new record.
On this form they would like it to count how many drives have been shredded which I have done using =Count(*) but then they want to display a field that displays how many drives are being shredded per hour and then conditional format it red or green depending on the value of the field.

How is the best way to achieve this?

Thanks in advance!

[June7]

Assuming you are saving Date and Time in the auto filled field, build an aggregate query that calculates date/time to the hour and groups on that value as well as customer if desired with Count of drives.

CDate(Format([fieldname], "mm/dd/yyyy hh:""00"":""00"""))

Of course if there were no drives shredded for any particular hour, no record will display for that hour.

Best to output this in report, not on form. On form, probably need a subform to display the aggregate data or use DLookup expression in textbox.

[RiskIt]

Thanks for the info so far!
Yes, Date and time are both being saved for each record.

Would be beneficial for the counter to be on the form if possible (So the person shredding knows if they are on target or not).

I was told this could be done by using a sub-form with a calculated field but was not sure if there was an easier way.

Here is the form if its any help.



Thanks

[June7]

Yes, I mentioned subform. It would be bound to the aggregate query.

So you need count by user? Do you save user ID into record? Do you want to show the average hourly count for the current day? Average over 8 hours or the actual hours of shredding?

[RiskIt]

No, There is only one user entering data so it does not record any user.

Yes, So they want some kind of counter on the form to display how many hard drives are being shredded per hour over the 8 hour day.
So the target is 120 per hour and if they are adding 120 per hour some indicator to reflect this.

Hope this clears the goal up.

[June7]

Maybe simpler than I thought. If the main form is only showing records for current date then try expression in textbox in form footer:

=Count("*")/8

If the form is not restricted to current date records, gets complicated.

=Sum(IIf(CDate(Format([fieldname], "mm/dd/yyyy")) = Date(), 1, 0) / 8

New record must be committed to table before the calc will adjust. Record is committed when:
1) close table/query/form; or
2) move to another record; or
3) run code to save

Maybe I should have said 'operator' instead of 'user'. Do you want the average by each operator doing shredding? This would require an OperatorID in data.

[RiskIt]

Thank you!

This is perfect, I do have a question. Is is possible for the divide by 8 to be a variable or taken from another field with a set value?

Then this could be altered if the job is shorter than 8 hours, In theory?

Thanks

[June7]

Yes, the 8 can be supplied by reference to a field or control.

[RiskIt]

Thank you for your help .

Company were very happy with the end result, For now anyway no doubt there will be changes in the future.