Hi there,

I have been using Excel for many years and have just got to the stage where I need to build a database. I have managed to work out how to import the data ok but within the single table that I will be using I need to have a column which will add up the contents of four other columns.

EG.

Cells in Column T to be the total of cells in the same row from columns C, E, F and G.

Please would someone be kind enough to let me know if this can be done and if so how I can go about achieving this.

I am a newbie so please be gentle with me!!

Many thanks.

Paul

Do a search on calculated values and normalisation. For effecient database design, you don't need to store calculated values (with some exceptions - historical tax rates for example) but they can be calculated as required in a query

To amplify David's comment - import the stuff that IS NOT calculated. You'll save space.

Then write a query to display everything including the calculated stuff. When you need to calculate the total you described,... let's assume the fields are named C, E, F, and G... in the query box for the computed total you would put... [C]+[E]+[F]+[G]

Of course, in your query you would use the REAL field names, not my supposed names.

Thank you both for your replies. This has certainly helped with the organisation of my data.

Moving onto my query, I wonder if I could ask you to suggest how I can obtain the following?

Cell A minus cells B, C, D and E. These are currency items but there may not always be an amount in cells B,C,D and E. In cases such as this the query output would be the same as the value in CellA

I hope that this is clear. At the moment I have my data in an Access Table and now need to start replicating the reports and functions achieved previously through Excel.

Many thanks again for such swift responses and help!

Paul

When you have possible missing values, you MIGHT import the values as NULL (depending on a lot of compex factors). In that case, you would look at a formula like...

[A] - ( NZ([B], 0 ) + NZ( [C], 0 ) + ... )

Look up function NZ in Access Help to see what it does.

If your import never produces nulls, or if you import in a way that you can set default values, you would prevent the NULL problem. To do the latter, define the table BEFORE you do the import so you can set default values for the fields that COULD be null or blank but should be numbers. Then when you go into the import dialog, you have a tedious bit of work to assure that the input lines up with your fields. But once that is done, you are home free.