Summary query sum from different tables

[BAC]

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

 

[Michel Walsh]

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]

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?

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

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

 

[Michel Walsh]

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.

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?

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

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

 

[BAC]

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

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.

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?

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


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

 

[Bob Barrows [MVP]]

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?

It's almost in the order of execution. Here is the correct syntax order:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...

As Michael says, you can add a Totals row to the query grid to enable
Group By and Having clauses

If the following was the correct syntax, then it would be in order of
execution
(http://groups.google.com/group/comp.databases.ms-sqlserver/msg/ec194ab2
109662c8):

FROM ...
WHERE ...
GROUP BY ...
HAVING ...
SELECT ...
ORDER BY ...

 

[Bob Barrows [MVP]]

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

Well, that's because there is a syntax error: you left out a bracket -
in addition, didn't you want to compare to current month?

HAVING sum([prior month].[cost]) = sum([current month].[cost])

 

[BAC]

Thanks for the input, but the SQL I submitted in the original post came from
the SQL view after I built the grid for the initial query to retrieve
contracts with more units in the prior month than in the current month. I
rarely wrire SQL when I can get the results I want from the grid.

Nonetheless, I haven't figured out how to get the grid to give me the
results I already have, but only when the contract cost remains the same
(i.e. an asset was split as opposed to a new asset added to the contract).

Even when adding a criteria to a "SUM" on the "TOTAL" line which generates a
HAVING clause in SQL view, I'm still not returning the appropriate rows (see
my last post)

BTW the link returned: We're sorry, but we were unable to find the message
you were looking for.


Thanks again
BAC

 

[BAC]

Thanks
That's what happens when I type instead of cut & paste

My HAVING clause is identical to yours, but when I run it prompts for prior
month.Cost and current month.cost and returns 0 records if I leave those
blank..

BAC

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

Well, that's because there is a syntax error: you left out a bracket -
in addition, didn't you want to compare to current month?

HAVING sum([prior month].[cost]) = sum([current month].[cost])



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

BTW the link returned: We're sorry, but we were unable to find the
message you were looking for.

That's because the url got broken into two lines

 

[Bob Barrows [MVP]]

That means it can't find a field called "cost" in those tables. I'm not
sitting at your computer so I have little hope of finding your error for
you.
I think your whole approach to this is wrong. I'm working on another
post in which I will try to better understand your problem.

Thanks
That's what happens when I type instead of cut & paste

My HAVING clause is identical to yours, but when I run it prompts for
prior month.Cost and current month.cost and returns 0 records if I
leave those blank..

BAC

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

Well, that's because there is a syntax error: you left out a bracket
- in addition, didn't you want to compare to current month?

HAVING sum([prior month].[cost]) = sum([current month].[cost])

 

[Michel Walsh]

I answered to the problem of syntax, but that was not the only problem,
indeed.

To get the contract where the sum of the cost is the same, try:


SELECT contract
FROM [current month] AS a
GROUP BY contract
HAVING SUM(cost) = (SELEC SUM(b.cost)
FROM [previous month] AS b
WHERE b.contract = a.contract)






Vanderghast, Access MVP

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

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.

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?

:

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


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

 

[BAC]

Of course! Who'd a thunk?

That's because the url got broken into two lines
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

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

By "items" do you mean "contracts"? Or "assets"?

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

does the number in the units field refer to the number of assets?
Or is the total cost calculated by multiplying cost * units? I'm trying
to get a handle on these questions by reading your sql statement, but it
just raises more questions.
To start with, given the data you've shown us above, what would your
desired result be?

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

I don't understand why Asset is not part of this join

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 you expect to get the total cost for the contract when you are
grouping by asset?

I think you need to start with two saved grouping queries:

qGroupedCurrent:
select contract,count(asset) totalassets,sum(cost) totalcost
from [current month]
group by contract

qGroupedPrior:
select contract,count(asset) totalassets,sum(cost) totalcost
from [prior month]
group by contract

Now join the saved queries in a new query:

select c.contract,c.totalassets, c.totalcost
from qGroupedCurrent c join qGroupedPrior p
on c.contract=p.contract
where c.totalassets > p.totalassets and c.totalcost = p.totalcost

Try that

 

[BAC]

Yeah, this was..Thanx, I used this code to get the list of contracts then
input that into a second query to get those whose unit count was greater in
the current month.

We were looking at the potential necessity to run this is 2 steps, your
solution came in just before we did!

Thanx again
BAC

I answered to the problem of syntax, but that was not the only problem,
indeed.

To get the contract where the sum of the cost is the same, try:


SELECT contract
FROM [current month] AS a
GROUP BY contract
HAVING SUM(cost) = (SELEC SUM(b.cost)
FROM [previous month] AS b
WHERE b.contract = a.contract)






Vanderghast, Access MVP

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

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.




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?

:

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


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

 

[BAC]

Well, I read it but I don't understand it...Nonetheless, Michael got me an
answer, but I appreciate your input

Thanx

BAC