Totals within database

[James Russell]

I would like to add all the values in a database lookup in my worksheet.

Field A1 is doing =('Lighting Device'.'Fixture ID') and B1 is =('Lighting Device'.'Fixture Cost'), where Fixture ID is a name and Fixture cost is the cost. 10 lamps are listed with various costs within A1.1 and A1.10 and B1.1 and B1.10 respectively. How can I in B2 add all the cost field to give a total cost.

I would have thought =sum(B1.1:B1.10) would work but the past searching of previous answers would suggest no.

Any help?

J

[michaelk]

Have you tried =B1 ?

michaelk

[Assembly]

B1 is the value of the sum of b1.1-B1.10

When DB headers are on you see this.

As Michalek said =B1 will do it.

I don't think you can get independent access to any of sub fields. IE 1.05. You can change the order of the field, VW want 'track' where the field moves.

The work sheets are not 'smart' like that- IE in excel if you drag a value in one cell to another, a formula that references the cell will update to follow the change.

Hence we in our spread sheets we use long entry for database quires.

What I mean by that is in your example.

A1 is =('Lighting Device'.'Fixture ID') and

B1 is =('Lighting Device'.'Fixture Cost'),

We would not use C1= A1*B1

We would use C1 = ('Lighting Device'.'Fixture ID')*('Lighting Device'.'Fixture Cost')

(I know you would not actually use this formula, but it shows what I mean).

[James Russell]

I have tried B1 and many variables of it, the data base headers are not totalling the contence of the column only counting the units within.

This is the latest attempt, 28 lamps, as you can see the headers are =('lighting device'.'fixture ID') showing the letter reference for the lamp type and B shows =('lighting device'.'fixture cost').

Both A1 and B1 only show the count of the number of fields below, not the added total of.

Am I doing something wrong?

[michaelk]

James

I think you need to change the field data type of Lighting Device . Fixture Cost to numeric.

hope that works....

michaelk

[James Russell]

Thank you guys, it was numeric but upon checking I found that the default value was nothing instead of 0, the moment I changed this it totaled perfectly.

Cheers,

J

[Assembly]

I have notice the default settings for the values has

Text left justified in a cell

Number right justified.

[ashot]

B1 is Header for the Database

to show rows of information it is correctly done, so

B1 has been entered with this formula to call for the record information

='Filed.Record' that is:

=('Lighting Device'.'Fixture Cost')

If you replace it with anything else, like

=B1

I can't see how your database rows are remain valid?

I am still puzzled how do you summarize on top at B1 cell

in my case the formatting didn't work, it always shows 28 no matter what formatting

I use for the cell: General, Decimal with so many digits etc.

Please advise what was the solution.

Tx,

[Pat Stanford]

You are correct that you put the formula in the first row of the database. It then automatically generates the subrows based on the database criteria (1.1, 1.2, etc.)

If you have a numeric field (or calculation) in a column the value in the header row ("B1") is the sum of everything in the sub rows.

If you have a non-numeric field the header row gives the count of the number of objects in the subrows.

Be careful, you can put numeric looking data in non-numeric (text) fields and not be able to do math (or get a sum) on them.

You don't have to replace the formula with =B1, you can just use that formula elsewhere in the worksheet to get the total.

Example.

Create a database row in row three

Database Criteria Type is Rectangle

Header row formulas

A3 - =Width()

B3 - =Length()

C3 - =A3*B3

D3 - =L {this wil give the layer the object is on}

A3 will be the sum of the X projection of all the rectangles.

B3 will the the sum of the Y projection of all the rectangles.

C3 will the the area of the bounding box of all the rectangles

D3 will be the count of the number of rectangles

If you go to cell A1 and put in a formula of =C3/D3 you will get the average area of the bounding box of the rectangles.

[ashot]

As far as I know in order to show on top header the summarize count of all subrows

the correct formula will be

=Value(('Lighting Device'.'Fixture Cost')

After which you can use any format you want.

It works for any linear or area measurement.

I could not find the solution for Angle calculation.

In your case for summing any number it works.

Tx.

[Sandy KOKON]

On 2/18/2010 at 2:27 AM, michaelk said:

James

I think you need to change the field data type of Lighting Device . Fixture Cost to numeric.

hope that works....

michaelk

How to do this?

[Sandy KOKON]

I have a similar problem. I am trying to sum the positive outcomes of an if-statement. The subrows each show '1' (if true) or '0' (if false) correctly, but the sum-option won't show the sum of all the '1''s, instead it shows a count of the number of objects in the database. How can I fix this?

 

PS. the field data type is set to decimal ( I also tried the other settings, none of them work)

 

[michaelk]

By putting single quotes around the 1 and 0 you are telling the worksheet to treat them as text.  If you remove them it will treat them as integers and do what you want.

 

It's a worksheet so there are probably many ways to get what you want.  Post back if you suspect there might be a better way 🙂 

[Sandy KOKON]

Works like a charm, thank you!