Sum only working once ...

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi All

My table called TblEventCosts itemises various event costs.

My query has a calculated field to add up the various items. It works for
the first record though fails to display anything for the following records.

Help.

Statement is ...

SELECT TblEventCosts.EventIDFK,
Sum([estaff]+[epetrol]+[ehire]+[efood]+[emisc]) AS TotalCost
FROM TblEventCosts
GROUP BY TblEventCosts.EventIDFK;


TIA
 
if you have any Null values in the number fields, the addition calc will
fail. try

Sum(Nz([estaff],0)+Nz([epetrol],0)+Nz([ehire],0)+Nz([efood],0)+Nz([emisc]),0
)

btw, recommend you reconsider the design of TblEventCosts; in its' current
state, it violates normalization rules. suggest a table lists "cost
categories", and a revamped TblEventCosts, as

tblCostCategories
CostCatID (primary key)
CostCatName (one record each for "petrol", "hire", "food", "misc", and any
other categories that come up as time goes by)

TblEventCosts
EventCostID (primary key)
EventIDFK (i assume this a foreign key linking back to your events table)
CostCatID (fk from tblCostCategories)
Cost

you can use a simple query to get costs for a single event, a totals query
to get a sum of costs for multiple events, or a crosstab query to show costs
per category for multiple events.

hth
 
Brilliant Tina as always - much appreciated.

I will even recreate my tables to normalise them as you suggested.

Thanks
--
Sue Compelling


tina said:
if you have any Null values in the number fields, the addition calc will
fail. try

Sum(Nz([estaff],0)+Nz([epetrol],0)+Nz([ehire],0)+Nz([efood],0)+Nz([emisc]),0
)

btw, recommend you reconsider the design of TblEventCosts; in its' current
state, it violates normalization rules. suggest a table lists "cost
categories", and a revamped TblEventCosts, as

tblCostCategories
CostCatID (primary key)
CostCatName (one record each for "petrol", "hire", "food", "misc", and any
other categories that come up as time goes by)

TblEventCosts
EventCostID (primary key)
EventIDFK (i assume this a foreign key linking back to your events table)
CostCatID (fk from tblCostCategories)
Cost

you can use a simple query to get costs for a single event, a totals query
to get a sum of costs for multiple events, or a crosstab query to show costs
per category for multiple events.

hth


Sue Compelling said:
Hi All

My table called TblEventCosts itemises various event costs.

My query has a calculated field to add up the various items. It works for
the first record though fails to display anything for the following records.

Help.

Statement is ...

SELECT TblEventCosts.EventIDFK,
Sum([estaff]+[epetrol]+[ehire]+[efood]+[emisc]) AS TotalCost
FROM TblEventCosts
GROUP BY TblEventCosts.EventIDFK;


TIA
Click to expand...
Click to expand...
 
ah, i take it i've been of some help to you in the past; glad we hit paydirt
again this time 'round! :)


Sue Compelling said:
Brilliant Tina as always - much appreciated.

I will even recreate my tables to normalise them as you suggested.

Thanks
--
Sue Compelling


tina said:
if you have any Null values in the number fields, the addition calc will
fail. try

Sum(Nz([estaff],0)+Nz([epetrol],0)+Nz([ehire],0)+Nz([efood],0)+Nz([emisc]),0
)

btw, recommend you reconsider the design of TblEventCosts; in its' current
state, it violates normalization rules. suggest a table lists "cost
categories", and a revamped TblEventCosts, as

tblCostCategories
CostCatID (primary key)
CostCatName (one record each for "petrol", "hire", "food", "misc", and any
other categories that come up as time goes by)

TblEventCosts
EventCostID (primary key)
EventIDFK (i assume this a foreign key linking back to your events table)
CostCatID (fk from tblCostCategories)
Cost

you can use a simple query to get costs for a single event, a totals query
to get a sum of costs for multiple events, or a crosstab query to show costs
per category for multiple events.

hth


Hi All

My table called TblEventCosts itemises various event costs.

My query has a calculated field to add up the various items. It works for
the first record though fails to display anything for the following records.

Help.

Statement is ...

SELECT TblEventCosts.EventIDFK,
Sum([estaff]+[epetrol]+[ehire]+[efood]+[emisc]) AS TotalCost
FROM TblEventCosts
GROUP BY TblEventCosts.EventIDFK;


TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top