Re: Aggregate Sum

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" <marshbarton@xxxxxxxxxx> wrote in message
news:mae6o19mvdnqmabjp28l202j6lhh4knume@xxxxxxxxxx
> Sandy H wrote:
>>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.
>
>
> 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.
>
> --
> Marsh
> MVP [MS Access]


.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)