Member Avatar for jared.geli

I want to know how can I trigger an event or at least display it in a Datagrid
from database. Here's the scenario:

DateFile  Plate  Driver  EmpID  OdometerBefore OdometerAfter
9/25/2013 ABC123 Jared   00369  4000           8000
9/27/2013 DEF456 John    00246  3000           8000
10/9/2013 ABC123 Jared   00369  8000           14000
10/9/2013 DEF456 John    00246  8000           13000
10/9/2013 BRS345 Noa     00678  5000           10000

I want the 2 cars whose plate are ABC123 and DEF456 to trigger an event where
every 10,000 odometer has been added in they're previous record they are
required to change their cars' oil. Then the next limit will add 10000 to their
previous goal. I was thinking of using "MAX" in Select query but it will add
all odometer records I want only the cars that reach or past the maintenance
requirement which is "for every 10,000 Odometer". I'm using MS Access and Datagridview
I'm curious if it can be done? Even if it just highlighting the latest record
of the car that reached the limit.

Edited by pritaeas because: Moved to VB.NET.
  • 1 Contributor
  • 2 Replies
  • 187 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jared.geli

Dani AI

Generated

As discovered, using SUM on a plate-level will not show which record crossed a maintenance boundary. Two reliable patterns eliminate that ambiguity: 1) store the last service odometer (preferred), or 2) compare each record to the immediately previous record for the same plate and test whether the 10,000-multiple changed.

Practical, fast approach in the app (VB.NET + DataGridView): bind a DataTable sorted by Plate then DateFile, keep a per-plate "next goal" in a dictionary and mark rows that meet or exceed the goal. Initializing the goal from the last-known service odometer (if present) avoids false positives on a vehicle's first logged entry.

' dt: DataTable sorted by Plate, DateFile asc. Columns: Plate, OdometerBefore, OdometerAfter
Dim nextGoal As New Dictionary(Of String, Integer)()
If Not dt.Columns.Contains("Due") Then dt.Columns.Add("Due", GetType(Boolean))
For Each r As DataRow In dt.Rows
  Dim p = r("Plate").ToString()
  Dim odoAfter = Convert.ToInt32(r("OdometerAfter"))
  Dim odoBefore = If(r.Table.Columns.Contains("OdometerBefore"), Convert.ToInt32(r("OdometerBefore")), 0)
  If Not nextGoal.ContainsKey(p) Then
    nextGoal(p) = ((odoBefore \ 10000) + 1) * 10000   ' initialize from OdometerBefore or last-service Odo
  End If
  If odoAfter >= nextGoal(p) Then
    r("Due") = True
    Do While odoAfter >= nextGoal(p)
      nextGoal(p) += 10000
    Loop
  Else
    r("Due") = False
  End If
Next
' After binding, highlight rows where Due = True by setting row.DefaultCellStyle.BackColor

SQL options: modern RDBMS (SQL Server, PostgreSQL) can use LAG() to get the previous OdometerAfter per plate and compare FLOOR(current/10000) > FLOOR(prev/10000). In MS Access (no LAG), use a correlated subquery (SELECT TOP 1 ...) to fetch PrevOdo and then an expression like IIf(Int([OdometerAfter]/10000) > Int(Nz([PrevOdo],0)/10000), True, False). Correlated subqueries can be slower; if performance matters, compute PrevOdo into a temp table or do the logic in the application.

Notes and cautions: keep records ordered by DateFile, validate for odometer rollbacks or big jumps, and decide policy for large skips (generate multiple service events or mark overdue). The cleanest long-term solution is a Maintenance table (Plate, ServiceDate, OdometerAtService) so the next due = OdometerAtService + 10000 and queries become straightforward.

Member Avatar for jared.geli

tried using sum in a select query and where clause the result all my odometer on a specific plate has been computed but it can't distinct the record specifically like from 9/27/2013 -10/5/2013 a certain vehicle has reach the maintenance requirement "for every 10,000 odometer", that vehicle has already change its oil and from 9/27/2013 -10/5/2013 or earlier than 9/27/2013 should not be included in the computation

Member Avatar for jared.geli

to all who are reading this post can you give me some ideas how I can do this I was thinking of having a counter but I have no idea where to start

Edited by jared.geli
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.