"sum" for all fields

[gutmj]

Hi All

I have number of queries to create where I would need to summarize all the fields. Is there a way to change "total" field value in query design to "sum" in one go or do I have to do it field by field?

Similarily is there a way to change a property of all fields in a table in one go? For example to set all fields to have two decimal places.

Thanks for your help.

 

[Galaxiom]

Generally the requirement to combine values from different fields indicates an inappropriate data structure where the values should have all been stored as multiple records in the same field. The values in another field indicate the aspect of the measurement that was originally used to separate the fields.

This structure is far easier to query.

 

[gutmj]

Thanks. Got your point, it makes sense. Will re-think the data structure.

 

[RainLover]

Similarily is there a way to change a property of all fields in a table in one go? For example to set all fields to have two decimal places.

To do this would require a fair bit of code. Unless this is going to be a regular task it would be faster to do it Manually.

In either case this will have to be done in the Back End and no users can be logged on.

 

[Galaxiom]

Wanting a quick way to change the properties of "all the fields" suggests there are a lot of fields. This is also a common indicator that there are structural issues.

Having said that it is not so hard to do and here is the rudimentary code for the sake of showing it.

Private Sub DecimalPoints(NumberOfPoints As Integer) 
 
Dim db As DAO.Database
Dim fld As Field
 
Set db = CurrentDb
 
   For Each fld in db.TableDefs("MyTable").Fields
      fld.Properties("DecimalPlaces") = NumberOfPoints
   Next
 
End Sub

Of course it will fall over on fields that don't have a DecimalPlaces property but that is not hard to overcome.

Note how the DecimalPlaces property is referred to. It is one of the properties not exposed using the dot property syntax.

BTW. DecimalPlaces = 255 means Automatic.