access

[ronnie h]

how do i add or subtract two or more columns in access

[Rick]

You do it in a query.

Something like:

Total: [rF1] + [rF2]

That goes on the top line. Your just creating a new field in the query named 'Total' that add together the fields rF1 and rF2 in this example.

MikeDee has something put together where he can create his own field like this and then run a module to add the field to your table and also rank it and add that field to your table.

If you get where you need something like that now you know who to ask. He is the VBA expert of the group.

[Carl]

You can also add the new field to your table by importing the query as a table.

I do this quite often, works fine.

[MikeDee]

Good suggestion Carl - I assume you include all of the records from the orginal table in the query and then after you have created the new table just rename it as the old table?

[hurrikane]

Carl,
haven't fogotten you..just been swamped. Hopefully this week buddy.

[Carl]

Mike, yes that is what I do, then just delete the old table, as the new one has all the old ones plus the calculated field as a fixed number.

Thanks Hurrikane, looking forward to it.

[ronnie h]

carl
can you go into some how to details on your way

[Carl]

1) Create a query from your MSA db with all fields in with wizard.
2) Make your calculated field as Rick suggested within the query.
3) Open the field to view to make sure the calculated field is doing what you want.

If all the three above are O.K., then you can "import the query as a database".

1) Go file>external data>Import>My Documents>[name of database you are using].
2) A wizard should pop up similiar to that you use when importing files. This time you want to import in access rather than "text" though.
3) In the "Import Objects" dialogue box click on "Queries" and specifically the query you have just made with the calculated field in it.
4) BEFORE clicking on "O.K.", click on the "options" box in the lower right hand corner, and check the "import as table" box instead of the "import as query" box in the lower right hand corner. Then click "O.K.".

And that should do it. Mike or one of the boys please clarify as need be.......

[MikeDee]

sounds good to me Carl, I've not done it that way. As is the norm in Access there are about 6 different ways to do the same thing.

Here is another.

When you are completely satisifed that your query has all fields and the calculated field is just as you want it, on the design view, change the query to a make table query. You do this by clicking on the small down arrowhead to the left of the big red exclamation point. A drop down menu will appear change the query type to make table. Access will then ask you for a table name. Name it and run it. This action query will build the table for you.

If this is a table that you plan to add new data to over time. Change the query to an append query and save it. Now when you have new data in your table that the query is built on and you want to put it in your table with the calculated field just run the append query and you have it.

There is a gotcha here if you run an append query make sure the the table you are using as the source table does not have the same data that is in the new table with the calculated field or you will get duplicate data in it.

In any table it is always a good idea to make date, track, race number and ppo number Key fields. This will prevent any table from having duplicate data, analyzing tables with dupliacte day can lead to very confusing and misleading results.
==========
Edited by Ricks: Bold word changed from table to query by Ricks

[ronnie h]

thanks guys finally broke down and going to try learn a little

[ronnie h]

WHAT I HAVE DONE IS ADDED F1+F2+F3 NOW IS THERE A WAY TO RANK THE TOTAL?

[Carl]

Reddy,

You might be able to get a ranking module from Mike. He sent me one once for something, I think I learned how to modify it for different applications (just changed field names I believe).