processing a text file currently being brought into MS Access via macros and queries

[MrLee]

Hello All,

I saw your website and was wondering if you could possibly provide me with some advice. Currently I have an MS Access based system/database which is run via a macro. This macro basically uploads several text files into the database and loads them into tables. This text file is made up of fuel transaction data which contains vehicle number, amount of fuel, and odometer readings at time of fueling.

Since the uploading of the file is done only once I was wondering how I could process each and every transaction separately in some sort of loop. The reason being is that for some vehicles there will be transactions for the same day or several days.

What I am trying to do is to calculate the difference between the current odometer reading and the new one from each transaction. But given that there may be several transactions for a certain vehicle I would need to calculate the difference, load it into a table and then update the odometer reading. Then start the loop all over to calculate the difference for the next transaction.

Is there a way to incorporate VBA code for the loop into this autoexecutable?

I know this is a big question, but any advice would be appreciated,

Thanks,
Mike

 

[MrLee]

One other thing...with all that has been said, would it be simpler to modify the data after it is brought into the database?

Thanks,
MrLee

 

[r_darling]

Mr Lee,
Could you tell me how you created the macro to read in the data for the db? Thanks.
Richard

 

[Rabbie]

Hello All,

I saw your website and was wondering if you could possibly provide me with some advice. Currently I have an MS Access based system/database which is run via a macro. This macro basically uploads several text files into the database and loads them into tables. This text file is made up of fuel transaction data which contains vehicle number, amount of fuel, and odometer readings at time of fueling.

Since the uploading of the file is done only once I was wondering how I could process each and every transaction separately in some sort of loop. The reason being is that for some vehicles there will be transactions for the same day or several days.

What I am trying to do is to calculate the difference between the current odometer reading and the new one from each transaction. But given that there may be several transactions for a certain vehicle I would need to calculate the difference, load it into a table and then update the odometer reading. Then start the loop all over to calculate the difference for the next transaction.

Is there a way to incorporate VBA code for the loop into this autoexecutable?

I know this is a big question, but any advice would be appreciated,

Thanks,
Mike

This link has a worked example of a query/subquery that looks at Meter readings so you shoul be able to convert it to exactly what you want.