reset counter to zero

[chr1stoper1]

Hi Guys

Im try to make a database help me out servicing machine tools .
I can pull the quantity of products produced from a sql server and have made a report which has conditional formatting to warn when a service is coming up or has past the qty set for a required service .
My problem is how to tackle the resetting of the count as the sql will continuously count and id like to use access to rest to zero and start the count again . Any help with this is much appreciated

[Minty]

What's the query for your count?
Are there any date fields available in the source data that you could use/store to only count from that date?

Without a bit more detail it's hard to offer a more sensible answer or approach.

Perhaps a look at the source tables structure might help?

[chr1stoper1]

Hi Minty

Thanks for the reply. The information gathered from the sql is basically (part name) (Part number) (date production started ) (Date production finished ) and (qty made) these are molded parts from mold tools . The mold tools have different amounts of cavities so to find out the number of time s the tool has cycled the query runs a simple qty/cavities .This information is summed up so there is a running total in access and the report flags up when its close to service time . Once serviced how can i reset to start counting afresh ? The sql will continue to count total ammounts . I hope thats a little clearer

[Micron]

Add a date field to your table for when the service is completed. Count/calculate parts made from the Max of that date field to today and grouped by each serviceable thing. This would probably end up being a Totals query.

[Welshgasman]

You would reset the counter when the service has been completed.

How would you do it manually/on paper ?

[NoellaG]

How do you pull the data from SQL server? Is it a linked table or do you import and refresh the data on regular times? In the first case, you can't add an Access column's to the table. In any case I would create a Maintenance table in Access and link it to the SQL or import table. I would add fields partNumber, maintenanceDate and ProducedQtyOnMaintenance. All you have to do is write a query where you calculate the naw quantity as SQL_Quantity - ProducedQtyOnMaintenance, no need to reset qty to 0.

[madpiet]

Hi Minty

Thanks for the reply. The information gathered from the sql is basically (part name) (Part number) (date production started ) (Date production finished ) and (qty made) these are molded parts from mold tools . The mold tools have different amounts of cavities so to find out the number of time s the tool has cycled the query runs a simple qty/cavities .This information is summed up so there is a running total in access and the report flags up when its close to service time . Once serviced how can i reset to start counting afresh ? The sql will continue to count total ammounts . I hope thats a little clearer

I'd use a windowing function in SQL Server. Much easier that way.

Say I have a table of (Product, ProductionDate, Qty)

the query would be

SELECT Product,
ProductionDate,
Qty,
RunningTotal = SUM(Qty) OVER (PARTITION BY Product ORDER BY ProductionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM MyTable;