sum of all the dynamic fields in query

[MeU&Us]

Hi!
i have a query having ID(auto number) field, Name(text) field, and many other field having field property of (Currency)
now what i want to do, is to sum all the fields having Data type of (currency).
the problem is that Currency Type fields are added every month, and i want them added automatically in the sum value (sum of all (Currency) fields)
i already have the code to add currency type fields to the table
i simply want a code or something to run in access 2007 to add all these field, and update itself when there is a new field inserted in the table on which the query is based(it adds that field too)
in other words, a code or sql statement etc to sum all those fields in the query which have Currency data type
thanks!

 

[Uncle Gizmo]

>>>update itself when there is a new field inserted in the table<<<

It may well be possible to do what you suggest, and I'm sure someone has done it at some stage. However it's not the best way to do it, the best way is to change your database structure so that this new data is added to an existing field, and not added as a new field.

 

[MeU&Us]

well Uncle Gizmo!
my database is like, that i have to store each Quarter`s record separately, and then i want the sum of all those values to use in different report.

 

[Uncle Gizmo]

So at the moment you are adding a new field for each quarter?

If that's the case this is a fine if you have a spreadsheet or a flat file database, however with MS Access, you can take advantage of its advanced features by making a slight change your database construction, adding the new quarter as a new set of records with an extra field (a permanent one) that records the particular quarter the data refers to.

For more information on this different approach see this thread here:
Excel in Access (Part 1)

 

[Uncle Gizmo]

Extract from the above Link:
(the first two problems you are already experiencing, and with in a short space of time you may well be experiencing the last problem)

Uncharted Territory
The first most common problem I have noticed on forums for people with similarly designed tables imported from a spreadsheet is that they find it difficult to extract useful information, and this is one of the major reasons that you should consider constructing your new database in a different manner to your spreadsheet, no doubt you will be able to come up with your own unique methods of extracting the data, however you will find there is little help available, not because no one wants to help you, but because you are embarking into uncharted territory, “where no man has gone before”.

Maintenance
Another reason, (not in any particular order of importance, the significance will change from project to project) what happens if you need to add an extra subject? Let’s say the school starts offering French lessons, in the spreadsheet all you would do is add a new column “French” and indeed in your MS Access database you could also add a new column “French”, however if the construction of your database is well advanced, in other words you have many queries and forms based on the table you are adding a new field to, then you will have to modify every single query and form that extracts information from this table, not something you want to do often!

Column Limit
Many modern spreadsheet programs can handle thousands of columns; however MS Access has a 255 limit to the number of fields in any particular table. So in the student table example, if you were at the 255 limit and you needed to add another subject then you would find yourself in a difficult position.

 

[MeU&Us]

so Uncle Gizmo
are u suggesting any better way to do my job
here is the thing!
i have a relational database and my database is related to calculating the depreciation on fixed assets
now there are two things
1- depreciation for the year\half year
2- addition af all the previous depreciations to calculate accumulated depreciation.
what i am doing right now is having a complex query to calculate depreciation for the current year.
now i want to calculate the accumulated depreciation by adding all the previous years depreciation based on those current year`s depreciations calculated in previous years
and one more thing, i have hundreds of assets, and the depreciation is to be calculated for each of them
so can u suggest any better idea to tackle this?
regards

 

[Uncle Gizmo]

As I have already indicated it is a bad idea to add a new field to your database with this extra data in.

I'm not saying you can't do that, that is entirely up to you, you are entitled to build your database in any way you see fit.

However if you decide to follow your own unique path, then you will run into problems which have not yet been solved, and will require a solution. You may be lucky and find someone who is interested in solving a unique puzzle for some reason, indeed I am one such person, in the past I have spent quite a lot of time solving exactly these sort of problems just for the fun of it.

However your best approach in my opinion is to follow the normal database construction techniques used by everyone, for which there are known problems, however you also have the benefit that these problems have known solutions.

 

[MeU&Us]

well appreciated UG

but this suggestion seems impracticable for me, because like your sample transpose database, i do not have such fields like names, etc.
i have calculated financial amounts and figures, which need to be stored permanently.
so any one please help me in this regard
thanks

 

[ArtMiles]

I agree with Gizmo. It is a bad idea to add fields.

One way you could get the work done is to have a date field and then you can group by the month, quarter or anything else that you need without having an endless amount of new fields.

Hope this helps.

Art