Re: Summary query sum from different tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

In the query grid if i enter the Field: Sum([prior month].[Cost]) and
criteria: = Sum([current month].[cost] and leave total row blank, Access
automatically changes it to field: Cost, Table: prior month, and Total: Sum

Query returns 0 rows

if I set the Field: test:sum([prior month].[cost]), Access reverts to Field
test:Cost and sets Total to Sum and Table to prior month

Query returns 0 rows

if i enter the Field: Sum([prior month].[Cost]) and criteria: = Sum([current
month].[cost] and Total: Expression, Access, again, automatically changes it
to field: Cost, Table: prior month, and Total: Sum

I have tried using teh SQL view to add HAVING clause:

HAVING (sum([prior month].[cost]) = sum([prior month].cost])) => which
returns a "syntax error in HAVING Clause" and also

HAVING ([avgofcost] = sum([avgofcost1]) (field names of the aggregate values
in Select portion of the query)=> Which asks for input of avgofcost and
avgofcost1 and returns 0 records if I just enter

Appreciate your help here, I can't believe this is that complicated

BAC

"Michel Walsh" wrote:

If you click on the summation symbol to get an additional line, Total, in
the grid, then, anything you type in the criteria line goes in the HAVING
clause unless the total line choice is WHERE (instead of GROUP BY or an
aggregate). If the Total line has WHERE as selection, the criteria goes in
the WHERE clause instead of the HAVING clause. So, if the Total line shows
SUM, then the criteria is applied to the SUM, and the SQL statement produced
will show the criteria in the HAVING clause.

You can also type it in the SQL view, but that is not necessary.




Vanderghast, Access MVP.




"BAC" <BAC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3B6980D1-CCF0-45B0-828B-E1F5E0A61D9D@xxxxxxxxxxxxxxxx
But there is no "Having" option in the query grid..does this mean I have
to
do a pass through SQL Specific query to run this? If so, where does the
HAVING clause go relative to the Where?

"Michel Walsh" wrote:

Put the criteria involving an aggregate in the HAVING clause. The WHERE
clause is considered before making any group/aggregate while the HAVING
clause is considered after. Clearly, a SUM cannot be involved in a WHERE
clause, since the SUM has not occurred, yet, at that moment.


Vanderghast, Access MVP


"BAC" <BAC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5CAF8899-29C7-4ABA-B742-BAA2F4EE78AD@xxxxxxxxxxxxxxxx
VISTA Enterprise/Office 2007 Plus

situation:

Two identically structured tables, [Prior month] and [current month]

tables consist of contracts and assets with costs. there is a field
that
indicates the unit count (i.e. the number of units on the contract)

Occassionally, the number of assets may increase from last month with
no
increase in contract cost

Record:
[current month]
Contract Asset Cost units
1 1 5.00 3
1 2 4.00 3
1 3 3.00 3
[prior month]
1 1 8.00 2
1 2 4.00 2

objective:

Identify those items where the number of units assigned to a contract
has
increased, but total contract cost has not.

I can isolate the contracts where the unit cnt has changed and get the
relevant cost numbers:

SELECT [current month].CONTRACT, [current month].ASSET, Max([prior
month].[UNITS]) AS [MaxOfUNITS], First([current month].[UNITS]) AS
[FirstOfUNITS], Avg([prior month].[COST]) AS [AvgOfCOST], Avg([current
month].[COST]) AS [AvgOfCOST1]
FROM [current month] INNER JOIN [prior month] ON [current
month].CONTRACT
=
[prior month].CONTRACT
WHERE ((([current month].[UNITS])>[prior month].[UNITS]))
GROUP BY [current month].CONTRACT, [current month].ASSET;


What I can't seem to figure out is how to limit on sum([prior
month].[cost])
= sum([current month].[cost]). How do I add this limit without getting
some
sort of "Aggregate in Where clause error? Now I'm getting all contracts
where
the number of units has changed regardless of whether or not costs have
changed


TIA

BAC






.

Relevant Pages

  • Re: Summary query sum from different tables
    ... To get the contract where the sum of the cost is the same, ... I have tried using teh SQL view to add HAVING clause: ...
    (microsoft.public.access.queries)
  • Calculate a list based on prior result
    ... I have a group of data that needs to calculate based on prior line ... The sum of C2+D3+E4 must equal to the Cost. ... Figures in C to G column are calculated fields. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: newbie problem with analytic function
    ... I think order by clause dont cause for cummulative sum or running ... for the purpose of calculationg the partition results. ... sumover (partition by deptno order by empno) tot_dept_sal ...
    (comp.databases.oracle.misc)
  • Re: Sub Form Formula
    ... The problem with doing this is that ExtCost isn't a field. ... This would be easy in a report using the Running Sum option of the ... While we made the calculation work in the subform one record at a time using ... > getting the extended cost correct. ...
    (microsoft.public.access.forms)
  • Re: formula
    ... to Sum. ... "JulieS" wrote: ... You note that having a field multiplying task cost by 1.1 is "not ... that include some of tasks and summarize the costs of its subtasks. ...
    (microsoft.public.project)