How to sum a column in a query and restart when it hits a preset target and run a report from it

[Divindunk]

Hi I am struggling with a problem for which I can see no solution but I am sure someone will have the answer! I have a query which has the following fields "Date", "ToolNo", "Description" and "Shots" the records are for mould tools and the volume of production logged by date. I need to be able to trigger maintenance levels on the tools by the number of cycles of each tool i.e number of "shots" made. There are 4 pre-set levels of maintenance for every 3000, 9000, 60000 and 150000 shots.



So I need to be able to

1. Restart counts for each of the four levels as they reach each threshold for each tool

2. Produce a report which displays which tools have reached a particular maintenance level

3. Be able to check mark and log a date, via a form, confirming when the maintenance has been carried out on the tool and thus removing them from the report until they reach the next threshold.

I would say the I am an averagely skilled database builder with some knowledge of VBA but I have to confess I am all at sea with this! Any pointers or clues would be greatly appreciated. Thanks very much in anticipation.

[ranman256]

id imagine, youd continuously have the :
tool, date, shots, inspectAt

a tMaint table to show when work was performed on each tool.
then set the next main inspection at [shot]=[shot]+3000, which sets it at: INSPECTat= 9594555.

a query would compare the [InspectAt] to the [shot] count, if getting near, display this tool for inspection.
no vba needed. But I dont see how the [shot] count is updated. (automatically via the tool??)

[Divindunk]

Hi thanks for the quick response not sure if I understand it yet but I'll take some time try! The shot data is entered at the end of each 8hr shift... no shop floor data collection yet :-( I think I get the drift of what you're saying, however the four different thresholds apply to all tools so the restart would only occur at 3000 for a basic clean down of the tool but at the other end of the scale 150000 is full tool room strip and refurbishment job and all the other intermediate maintenance levels occur during this 150000 shot period? So the count for maintenance would go 3000,3000,9000, 3000,3000,3000, ... 60000, 3000, 3000, 3000, ... 150000, so would I need four different counters for each tool? Thanks

[ssanfu]

Just an FYI....

"Date", and "Description" are reserved words (as is "Desc") in Access and shouldn't be used for object names. "Date" is also a built in function.
In addition, they are not very descriptive.
"Date" of WHAT? Purchase date??? Install date?
"Description" of WHAT?

[CJ_London]

what happens if the number of shots for a machine at start of shift is 2999 and a shift does 800 shots - is it stopped after 1 shot, maintenance carried out then continues for the other 799? or does the shift continue for all 800 and is stopped at end of shift for maintenance so 3000 maintenance occurs at 3799 shots in which case does next maintenance count from 3000 or 3799? Or is the machine actually put to maintenance at 2200 before shift starts?

[Divindunk]

Thanks for the info regarding naming conventions, I will change them accordingly! Just for further info, does using reserved words cause any issues if they are used?

Level 1 and 2 TPM are carried out while the tool is in the machine so yes it is stopped while these are carried out but for the more intensive levels 3 and 4 the tool must come out of the machine. The triggers are a guide so a couple of 100 shots either way is not an issue, (as with car servicing).

As regards my problem I am beginning to think it might be easier to link it to excel and do the calculations there unless anyone has a solution? Thanks all

[CJ_London]

does using reserved words cause any issues if they are used?

yes - primarily generating errors with misleading descriptions making them difficult to track down.

here is a list of reserved words
https://support.office.com/en-us/art...7-da237c63eabe

[Divindunk]

Thanks for that.

[ArviLaanemets]

It is a rare case where storing a calculated value into table is best solution!

Add into your log table a field named p.e ToolShotNo. Into BeforeUpdate event of form where the log is recorded, write a code to calculate current biggest ToolShotNo for tool, add 1, and write the result into text box on form linked to field ToolShotNo.

In some table you must register your tool maintenance, p.e. ToolMaintenanceLog. There you register tool id, maintenance date, maintenance level - and ToolShotNo at which the maintenance was actually made (you calculate current MAX ToolShotNo for tool from log table, and save the result into ToolMaintenanceLog). From there to calculate next maintenance threshold is an easy task.

[NoellaG]

Hi,

in the attachment you'll find an example file how you can proceed. When using the example, there is some code using the Microsoft Active X Data Object library, so be sure to check in the reference window (VBA window -> Tools -> References)

I added some tables (main table tools linked 1 to many on UsageTools, table levels with the 4 maintenance levels and a maintenance table) The main form is frmTools that has a subform that shows (not updatable, this is done automatically) the number of shots till the next maintenance/level and a subform that shows the number of shots/day.

Success
Noella