Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / November 2005

Tip: Looking for answers? Try searching our database.

Aggregate Sum

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Sandy H - 22 Nov 2005 13:58 GMT
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
Marshall Barton - 22 Nov 2005 15:38 GMT
>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
[quoted text clipped - 7 lines]
>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.

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.

Signature

Marsh
MVP [MS Access]

Sandy H - 22 Nov 2005 23:10 GMT
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

>>I have a query where I want to calculate the sums of money received and
>>also
[quoted text clipped - 21 lines]
> with a small set of sample data and an explanation of why
> you say the result is incorrect.
Marshall Barton - 23 Nov 2005 00:35 GMT
>SELECT tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.Leader,
>tblProjects.Objective, Sum(nz([DBudget],0)) AS DelBudget,
[quoted text clipped - 20 lines]
>but I am getting $6000.  I know this makes no sense at all but I can't work
>out why it's wrong.

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

Signature

Marsh
MVP [MS Access]

Sandy H - 23 Nov 2005 01:09 GMT
Thank you Marsh. You were 100% correct and I have now fixed the problem.

Sandy

>>SELECT tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.Leader,
>>tblProjects.Objective, Sum(nz([DBudget],0)) AS DelBudget,
[quoted text clipped - 73 lines]
> LEFT JOIN SupplierCost
> ON tblProjects.ProjectID = SupplierCost.fkProjectID
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.