Is doing this even possible (1 Viewer)

[jason2885]

hello

I have a database that keeps track of things that the mileage of changes on a monthly basis each month the mileages are updated unsually about 20 to 30 a month have significant differeneces in mileage. So what I have done to keep track of it and so that the information can be kept correct is that I have created a table that is updated every month with the new mileages and the previous months data is placed in a hist table. I have this working well and it will shows the difference in mileage and allow the people to put the correct information with the correct things.

Well here is the problem what happens if one month there is no check done and it updates automatically and the mileages in the hist folder is then lost and replaced with the previous months mileages.

Is there anyway to possible not allow it to forget the differences in the circuit. Creating almost like a backlog of each months report or something like that or prevent the mileage from updateing until after it has been checked and the mileage updated.

 

[pbaldy]

The simple answer is to either automate the check, or de-automate the updating. The more complex answer is actually a question. Why do you keep wiping out the mileages? Why not just keep adding the new data into the same table, leaving the history intact so you can always check against it?

 

[jason2885]

The simple answer is to either automate the check, or de-automate the updating. The more complex answer is actually a question. Why do you keep wiping out the mileages? Why not just keep adding the new data into the same table, leaving the history intact so you can always check against it?

How would you got about automating the check. If this sounds like a stupid question Im sorry.

I dont know why they keep on wiping out the mileages. We are dealing with over 6000 different things but I really dont see why they couldnt just record everytime that a mileage changes and keep that as a record.

Ok so is there away to automatically in access run a report and then send and email out to the people responcible for updating it with that months changes in mileage, saying hey you need to look at this update your information.

 

[pbaldy]

Not a stupid question at all. I have no idea how to automate the checking, or even if it can be done at all. I was simply giving potential alternatives. I don't know enough about your actual process to offer a more specific answer.

You can certainly run/email a report automatically. The 2 common ways are a timer event on a form checking all the time, but that would require it to be running all the time, which I wouldn't bother with. What I would do is create a "utility" mdb that would email the report when it opened, then close itself. Then I'd fire that mdb off from Scheduled Tasks at the appropriate date/time. DoCmd.SendObject would be the simplest tool to send the report with.

 

[jason2885]

Not a stupid question at all. I have no idea how to automate the checking, or even if it can be done at all. I was simply giving potential alternatives. I don't know enough about your actual process to offer a more specific answer.

You can certainly run/email a report automatically. The 2 common ways are a timer event on a form checking all the time, but that would require it to be running all the time, which I wouldn't bother with. What I would do is create a "utility" mdb that would email the report when it opened, then close itself. Then I'd fire that mdb off from Scheduled Tasks at the appropriate date/time. DoCmd.SendObject would be the simplest tool to send the report with.

Does anyone have any examples of how to do those two ways.

 

[ByteMyzer]

The best way, for your purpose, is to maintain ONE table, a history table, from which you could run a query to get the last checked date and last recorded mileage for each car. Then, allow the end users to add new entries, but never change or delete existing existing.

Assume a table like the following:

tblCarHistory
-------------
CarID      Text
CheckDate  Date/Time
Mileage    Number

Assume then, some history data in the table like the following:

CarID | CheckDate | Mileage
---------------------------
Car1  | 1/3/2007  |   1,000
Car2  | 1/3/2007  |   2,500
Car3  | 1/3/2007  |   1,723
Car1  | 2/1/2007  |   1,700
Car3  | 2/1/2007  |   2,900
Car1  | 3/1/2007  |   2,125
Car2  | 3/2/2007  |   4,300
Car3  | 3/1/2007  |   3,035
Car2  | 4/2/2007  |   4,410
Car3  | 4/2/2007  |   3,270

The following query will show you the last checked date and last recorded mileage for each car:

SELECT T1.CarID,
  T1.CheckDate AS [Last Checked Date],
  T1.Mileage AS [Last Recorded Mileage]
FROM tblCarHistory T1 INNER JOIN
 (SELECT CarID, MAX(CheckDate) AS xCheckDate
  FROM tblCarHistory
  GROUP BY CarID
 ) T2 ON T1.CarID=T2.CarID AND T1.CheckDate=T2.xCheckDate

Based on the sample data above, the query output would look like this:

CarID | Last Checked Date | Last Recorded Mileage
-------------------------------------------------
Car1  | 3/1/2007          |                 2,125
Car2  | 4/2/2007          |                 4,410
Car3  | 4/2/2007          |                 3,270

Optionally, if you choose to automate the updating for each month for cars not checked that month, your code would only have to add an entry to the table. Simple, no?