How to combine summed fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Guys

I am trying to combine and sum 2 or more fields from a related (child table).

I have tried
Sundries: Sum([PurchaseInvoices.Sundry])+Sum([PurchaseInvoices.Disposable])
and
Sundries: Sum([PurchaseInvoices.Sundry]+[PurchaseInvoices.Disposable])

Both expressions give me nothing in the Sundries column.
The expression Sum([PurchaseInvoices.Sundry]) works fine but I need to add
in the disposable (and more) column.

I am using Access 2007 and designing the query through the design view.

Many thanks.
Darwood
 
Probably a problem of syntax: it is [tableName].[fieldName], not
[tableName.fieldName]


So,

SUM( tableName.field1 ) + SUM( tableName.field2 )


should do, or, if you really need your [ ] :

SUM( [tableName].[field1] ) + SUM( [tableName].[field2] )




but NOT

SUM( [tableName.field1] ) + SUM( [tableName.field2] )



Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel

Thank you for your response.
I have solved it myself now. I eventually found that the additional column
being summed had null values in it as it is a new table that has been added
since the table was created and data was entered. Surrounding the field names
with nz() has resolved the problem.

Thank you for the syntax help anyway. I will try to use your format in
future. Although the one I used before did work I am guessing that Access is
clever enough to figure out what I mean.

Regards
Darwood

Michel Walsh said:
Probably a problem of syntax: it is [tableName].[fieldName], not
[tableName.fieldName]


So,

SUM( tableName.field1 ) + SUM( tableName.field2 )


should do, or, if you really need your [ ] :

SUM( [tableName].[field1] ) + SUM( [tableName].[field2] )




but NOT

SUM( [tableName.field1] ) + SUM( [tableName.field2] )



Hoping it may help,
Vanderghast, Access MVP


Darwood said:
Hi Guys

I am trying to combine and sum 2 or more fields from a related (child
table).

I have tried
Sundries:
Sum([PurchaseInvoices.Sundry])+Sum([PurchaseInvoices.Disposable])
and
Sundries: Sum([PurchaseInvoices.Sundry]+[PurchaseInvoices.Disposable])

Both expressions give me nothing in the Sundries column.
The expression Sum([PurchaseInvoices.Sundry]) works fine but I need to add
in the disposable (and more) column.

I am using Access 2007 and designing the query through the design view.

Many thanks.
Darwood
Click to expand...
Click to expand...
 
The syntax [tableName.fieldName] works in Jet, generally, if fieldName can
come from different source in a query, and that you use that query:


SELECT table1.f1, table2.f1
FROM table1, table2


as saved query qu1.

Then

SELECT [table1.f1] , [table2.f1]
FROM qu1



but that is really weird, and it is suggested to use alias, in the first
query:


SELECT table1.f1 AS table1_f1, table2.f1 AS table2_f2
FROM table1, table2


as saved query.


Then, it is much more 'standard' :

SELECT table1_f1, table2_f1
FROM qu1



If you have another scenario than this one, can you describe it? I am
interested to know it (for personal culture). :-)



Vanderghast, Access MVP





Darwood said:
Hi Michel

Thank you for your response.
I have solved it myself now. I eventually found that the additional column
being summed had null values in it as it is a new table that has been
added
since the table was created and data was entered. Surrounding the field
names
with nz() has resolved the problem.

Thank you for the syntax help anyway. I will try to use your format in
future. Although the one I used before did work I am guessing that Access
is
clever enough to figure out what I mean.

Regards
Darwood

Michel Walsh said:
Probably a problem of syntax: it is [tableName].[fieldName], not
[tableName.fieldName]


So,

SUM( tableName.field1 ) + SUM( tableName.field2 )


should do, or, if you really need your [ ] :

SUM( [tableName].[field1] ) + SUM( [tableName].[field2] )




but NOT

SUM( [tableName.field1] ) + SUM( [tableName.field2] )



Hoping it may help,
Vanderghast, Access MVP


Darwood said:
Hi Guys

I am trying to combine and sum 2 or more fields from a related (child
table).

I have tried
Sundries:
Sum([PurchaseInvoices.Sundry])+Sum([PurchaseInvoices.Disposable])
and
Sundries: Sum([PurchaseInvoices.Sundry]+[PurchaseInvoices.Disposable])

Both expressions give me nothing in the Sundries column.
The expression Sum([PurchaseInvoices.Sundry]) works fine but I need to
add
in the disposable (and more) column.

I am using Access 2007 and designing the query through the design view.

Many thanks.
Darwood
Click to expand...
Click to expand...
Click to expand...
 
Back
Top