Aggregate Sum

  • Thread starter Thread starter Sandy H
  • Start date Start date
S

Sandy H

Hi
I have a query where I want to calculate the sums of money received and also
the sums of money not received. I need to do both these sums in the one
query.

I am using the following expressions in the field rows:

Exp: Sum(IIf([Received]=True,[Cost]))
Comm: Sum(IIf([Received]=False,[Cost]))

The field Received is a boolean (Yes/No) and Cost is a currency field.
These expressions don't product the figures I should be seeing.

Could someone please tell me what I am doing wrong.

Thanks in advance
Sandy
 
Sandy said:
I have a query where I want to calculate the sums of money received and also
the sums of money not received. I need to do both these sums in the one
query.

I am using the following expressions in the field rows:

Exp: Sum(IIf([Received]=True,[Cost]))
Comm: Sum(IIf([Received]=False,[Cost]))

The field Received is a boolean (Yes/No) and Cost is a currency field.
These expressions don't product the figures I should be seeing.
Click to expand...


By themselves, there is nothing wrong with those
expressions. Although it is recommmended that you provide
the "else" value, Sum(IIf([Received]=True,[Cost], 0)), it
is not required in this particular situation.

To figure out what's "wrong", we will have to look at a
larger context. For example, perhaps the query's GROUP BY
clause is grouping on too many fields, or ???

Post back with a Copy/Paste or your query's SQL view along
with a small set of sample data and an explanation of why
you say the result is incorrect.
 
Hi Marsh
Thanks for the reply. This is the SQL for the query I am using.

SELECT tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.Leader,
tblProjects.Objective, Sum(nz([DBudget],0)) AS DelBudget,
Sum(IIf([Received]=True,[Cost])) AS Exp, Sum(IIf([Received]=False,[Cost]))
AS Comm
FROM (tblProjects LEFT JOIN tblDeliverables ON tblProjects.ProjectID =
tblDeliverables.fkProjectID) LEFT JOIN tblSuppliers ON tblProjects.ProjectID
= tblSuppliers.fkProjectID
GROUP BY tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.Leader,
tblProjects.Objective;

There is very little data in tblSuppliers .
tblSuppliers SupplierID fkProjectID SupplierName SupplierContact
PurchaseOrderNo PurchaseDate Product Cost Received
1 3 AA Absolute Access Sandy Hayman 123456 20/11/2005 Database
$3,000.00 No
2 3
AAAA 12567 21/11/2005 Database $200.00 Yes
3 3
Other 125877 22/11/2005 Other $2,750.00 Yes


As you can see, the result I should be getting for Exp is $2950 but for some
reason I am getting $5900. The result I should be getting for Comm is $3000
but I am getting $6000. I know this makes no sense at all but I can't work
out why it's wrong.

Thanks.

Sandy


Marshall Barton said:
Sandy said:
I have a query where I want to calculate the sums of money received and
also
the sums of money not received. I need to do both these sums in the one
query.

I am using the following expressions in the field rows:

Exp: Sum(IIf([Received]=True,[Cost]))
Comm: Sum(IIf([Received]=False,[Cost]))

The field Received is a boolean (Yes/No) and Cost is a currency field.
These expressions don't product the figures I should be seeing.
Click to expand...


By themselves, there is nothing wrong with those
expressions. Although it is recommmended that you provide
the "else" value, Sum(IIf([Received]=True,[Cost], 0)), it
is not required in this particular situation.

To figure out what's "wrong", we will have to look at a
larger context. For example, perhaps the query's GROUP BY
clause is grouping on too many fields, or ???

Post back with a Copy/Paste or your query's SQL view along
with a small set of sample data and an explanation of why
you say the result is incorrect.
Click to expand...
 
Sandy said:
SELECT tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.Leader,
tblProjects.Objective, Sum(nz([DBudget],0)) AS DelBudget,
Sum(IIf([Received]=True,[Cost])) AS Exp, Sum(IIf([Received]=False,[Cost]))
AS Comm
FROM (tblProjects LEFT JOIN tblDeliverables ON tblProjects.ProjectID =
tblDeliverables.fkProjectID) LEFT JOIN tblSuppliers ON tblProjects.ProjectID
= tblSuppliers.fkProjectID
GROUP BY tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.Leader,
tblProjects.Objective;

There is very little data in tblSuppliers .
tblSuppliers SupplierID fkProjectID SupplierName SupplierContact
PurchaseOrderNo PurchaseDate Product Cost Received
1 3 AA Absolute Access Sandy Hayman 123456 20/11/2005 Database
$3,000.00 No
2 3
AAAA 12567 21/11/2005 Database $200.00 Yes
3 3
Other 125877 22/11/2005 Other $2,750.00 Yes


As you can see, the result I should be getting for Exp is $2950 but for some
reason I am getting $5900. The result I should be getting for Comm is $3000
but I am getting $6000. I know this makes no sense at all but I can't work
out why it's wrong.
Click to expand...


Note that your query does not tell me what table the fields
DBudget and Cost are coming from so I can't be certain of
this.

Check your data in table tblDeliverables. I'll bet there
are two records with fkProjectID = 3

When you left join a parent table to a child table you get a
record for each matching record in the child table (in this
case 3). This is fine until you add a left join a child2
table, because you now get a result record for each matching
combination of records in the child and the child2 tables
(i'm betting it's 6).

To get what you want, you need to aggregate the values in
each table before joining them to the project table. While
this can be done with subqueries, I think it's better to use
three queries.

query SupplierCost:
SELECT fkProjectID,
Sum(IIf([Received]=True, Cost, 0)) AS Exp,
Sum(IIf([Received]=False, Cost, 0)) AS Comm
FROM tblSuppliers
GROUP BY fkProjectID

query DeliverablesCost:
SELECT fkProjectID,
Sum(DBudget) AS DelBudget
FROM tblSuppliers
GROUP BY fkProjectID

Since those queries only return a single record for each
project, you can left join those to the projects table:

SELECT tblProjects.ProjectID,
tblProjects.ProjectName,
tblProjects.Leader,
tblProjects.Objective,
DeliverablesCost.DelBudget,
SupplierCost.Exp,
SupplierCost.Comm
FROM (tblProjects
LEFT JOIN DeliverablesCost
ON tblProjects.ProjectID = DeliverablesCost.fkProjectID)
LEFT JOIN SupplierCost
ON tblProjects.ProjectID = SupplierCost.fkProjectID
 
Thank you Marsh. You were 100% correct and I have now fixed the problem.

Sandy

Marshall Barton said:
Sandy said:
SELECT tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.Leader,
tblProjects.Objective, Sum(nz([DBudget],0)) AS DelBudget,
Sum(IIf([Received]=True,[Cost])) AS Exp, Sum(IIf([Received]=False,[Cost]))
AS Comm
FROM (tblProjects LEFT JOIN tblDeliverables ON tblProjects.ProjectID =
tblDeliverables.fkProjectID) LEFT JOIN tblSuppliers ON
tblProjects.ProjectID
= tblSuppliers.fkProjectID
GROUP BY tblProjects.ProjectID, tblProjects.ProjectName,
tblProjects.Leader,
tblProjects.Objective;

There is very little data in tblSuppliers .
tblSuppliers SupplierID fkProjectID SupplierName SupplierContact
PurchaseOrderNo PurchaseDate Product Cost Received
1 3 AA Absolute Access Sandy Hayman 123456 20/11/2005 Database
$3,000.00 No
2 3
AAAA 12567 21/11/2005 Database $200.00 Yes
3 3
Other 125877 22/11/2005 Other $2,750.00 Yes


As you can see, the result I should be getting for Exp is $2950 but for
some
reason I am getting $5900. The result I should be getting for Comm is
$3000
but I am getting $6000. I know this makes no sense at all but I can't
work
out why it's wrong.
Click to expand...


Note that your query does not tell me what table the fields
DBudget and Cost are coming from so I can't be certain of
this.

Check your data in table tblDeliverables. I'll bet there
are two records with fkProjectID = 3

When you left join a parent table to a child table you get a
record for each matching record in the child table (in this
case 3). This is fine until you add a left join a child2
table, because you now get a result record for each matching
combination of records in the child and the child2 tables
(i'm betting it's 6).

To get what you want, you need to aggregate the values in
each table before joining them to the project table. While
this can be done with subqueries, I think it's better to use
three queries.

query SupplierCost:
SELECT fkProjectID,
Sum(IIf([Received]=True, Cost, 0)) AS Exp,
Sum(IIf([Received]=False, Cost, 0)) AS Comm
FROM tblSuppliers
GROUP BY fkProjectID

query DeliverablesCost:
SELECT fkProjectID,
Sum(DBudget) AS DelBudget
FROM tblSuppliers
GROUP BY fkProjectID

Since those queries only return a single record for each
project, you can left join those to the projects table:

SELECT tblProjects.ProjectID,
tblProjects.ProjectName,
tblProjects.Leader,
tblProjects.Objective,
DeliverablesCost.DelBudget,
SupplierCost.Exp,
SupplierCost.Comm
FROM (tblProjects
LEFT JOIN DeliverablesCost
ON tblProjects.ProjectID = DeliverablesCost.fkProjectID)
LEFT JOIN SupplierCost
ON tblProjects.ProjectID = SupplierCost.fkProjectID
Click to expand...
 
Back
Top