access tables, modify, then re-upload data (1 Viewer)

[MrLee]

Hello,

is there a way to read the data in an access table, perform calculations on a field based on other fields then re-enter the field into the database?

Thanks,
MrLee

 

[Jack Cowley]

Yes. Do the calculations on a form that is based on the table.

 

[David R]

You've now got answers going in two different forums on the same topic. Please only post a question once.

 

[MrLee]

Alirght,

what I really need to do is to put a list of transactions through a loop to calculate the difference...then update the column back in the database. Does this sound possible?

Mike

 

[David R]

Why do you need to store the calculation? Will the other columns involved change over time?

You can do exactly what you're asking with a query field, and the results will always be current. A calculated field has the possibility for being out of date, incorrect formulas, or just not updated on time.
Unless the data used in the calculation will not be present later on, there is no reason to store it in your table. Use a blank field in a query to do your calculation, then base everything off of that query. It will look like you are storing it, and you'll save space and your data will always be accurate.

If this is not the case, then an update query is going to be your best bet.

 

[MrLee]

I thought about using an update query, but in my database there are vehicles listed. Among other data that is listed are the odometer readings for each vehicle. When the macros and queries are run, new transactions for some of the vehicles are brought in. As I thought, an update query would do just the thing I wanted... but the problem is that there is a possibility of more than one transaction for a vehicle being brought in. Also, they can be from the same day or from different days.

Therefore I was thinking that if I exit the database and then pull off the transactions, specifically the odometer readings. Then on the form with some code I can calculate the difference between the old and new odometer readings and then update the transaction in the database. This would have to be done for every transaction based on vehicle number, date and time.

Does this make any sense?

MrLee

 

[David R]

Give me an example of the data you're getting from an outside source. I'm not sure I understand what's going on here. You do have these Transactions in a different table from your Vehicles table, right?

Just a few lines should do...

 

[MrLee]

Below is an example of three transactions for three different vehicles. I had to break it up into four seperate lines, because of the limitations of this window. So when these transactions are brought in, they are put into a final table. In order to get the current odometer reading I can run a query that takes the newest transaction for each vehicle and just records the current odometer reading. Instead what I am trying to do is to process each transaction to calculate the difference. It is necessary to put it into a loop because on any given day, there could be more than one transaction for the same vehicle. Therefore I have to update the odometer reading from the earliest transaction to the newest. This way I can obtain the distance travelled rather just the current odometer reading.

Thanks,
MrLee

transno driver vehicle year month day
064 DELL 01-136 2002 04 27
035 CROSSMAN01-066 2002 04 27
055 VEEKE 01-075 2002 04 27

time transcounter keyno2 prod pump
11:11 6549 3381000778 02 03
05:37 6520 3381000806 01 01
09:55 6540 3381000807 01 01

quantity price total odometerreading
155 00554 000000859 075000
236 00275 000000649 007485
4 00275 000000011 071275

mpg checkdigit retrievdate
000 59 2002-04-30
025 62 2002-04-30
000 79 2002-04-30

 

[David R]

So you've got a lot of information there, why can't you just put it into the tables you want with update queries? For example...

your table that records odometer readings, probably has fields for [Vehicle], [Driver] (maybe, depending on your structure), [TransactionDate/Time] (concatenate the fields in your text file) , [Pump], [Quantity], and [OdometerReading]. You've got all that here in this file so I'm not sure what the question is. Upload the file into a temporary table, use an Update Query to pull the fields you need into the tables you need, then delete/empty the table.

Odometer differences can be calculated from this using the last date record before your update, then the last odometer reading from today, and calculating the difference. You shouldn't have one field in a main table responsible for ALL odometer readings; that belongs in a subtable.

 

[MrLee]

I can update the odometer readings for each vehicle when there is only one transaction for a particular vehicle. But what about if there are two, three, four or more transactions for a particular vehicle?

Is it even possible to run a query until there are no more transactions for a particular vehicle?

As I understand it, a qeury when run via a macro can only be run once. This would simply take the last known odometer reading and I could calculate the difference and plug the value back into the table. I don't know if it's possible with queries. I am currently looking into VBA.

Thanks,
MrLee

 

[David R]

Again, transactions should be stored in a subtable, linked to the vehicleID, NOT in the main vehicle table. If you do it this way then any number of transactions can be recorded for any vehicle.

The data you get, does it only include new data or is it the entire dataset back to <whenever> in text form?

 

[MrLee]

The data only includes new data...ie. transactions that are new. However the data for the last 45 days are stored in the same Access system.