data manipulation

[clickaccess]

Hi,

I need to read the data from access table1= (example: field1=5, field2=9, field3=0)

than apply a formula (example: field1-2=X, field2-4=Y, field3-9=Z, get the total sum of each record) and store the results of X,Y,Z, and the sum in table2.

There are 4000 records in that db.

Don't know a thing about how to do this.

Rajeev

Working on a FREE email.
Feel free to sign up.

http://www.SacMail.com

 

[Tranman]

Rajeev,
You didn't say if you were particular about whether you did this with VBA or SQL, but it looked pretty simple in SQL, so here is a solution:

INSERT INTO OutputTable ( Out1, Out2, Out3, TheSum )
SELECT [field1]-2 AS Out1, [field2]-4 AS Out2, [field3]-9 AS Out3, [field1]+[field2]+[field3] AS TheSum
FROM InputTable;

You can just open a new query in SQL view and paste this logic in and update the table and field names. Of course, the intermediate field names don't have to be the same as the names in OutputTable, I just did it that way so it would be simpler to follow the logic.

This logic calculates the sum of the field values *before* the subtractions are done. If you want the sum of the results of the subtractions, you need to add [Out1]+[Out2]+[Out3]

Tranman

 

[JeremyNYC]

Tranman's solution looks solid, but I would make a recommendation: don't store the calculated fields at all, just make a select query that does the same math, and view the data in the query when you would normally think to view the data in that second table.

One of the big principles of database design is that you don't store calculated data except at very rare exceptions. You haven't mentioned what the data are, so I can't say whether or not the rule should apply here, but it almost always does.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[clickaccess]

Tranman, Yea that is the idea. I tried it in SQL and I get the desired result, but how to do in VBA?

Thanks BTW

Rajeev

Email Service Free from Banner ads.

http://www.SacMail.com

signup while you can still get your own favorite handle.

 

[Tranman]

Rajeev,
If you want to, in VBA, you can just run the query you created above:

CurrentDB.Execute "myQuery"

or you can say:

DoCmd.OpenQuery "myQuery"

or you can create two recordsets and insert rows in the first one based upon the data in the first one.

or you can create a single recordset and a SQL insert command where you calculate the values then insert them directly in the output table.

As you can see, there are several ways to accomplish what you want...

If you want to know how to do it completely in VBA with no query object in your database, let me know, and I'll send you a snippet.

Tranman