Cannot have aggregate function in WHERE clause : microsoft, access 2003, 2003

>I get a "Cannot have aggregate function in WHERE clause:
Correct. That would be the HAVING clause.

SELECT [Quantity]-Sum(nz([MeasureQuantity])) AS TQ, tblPO_Details.PO_DetailsID
FROM tblPO_Details
LEFT JOIN tblOrdersItems ON tblPO_Details.PO_DetailsID = tblOrdersItems.PO_DetailsID
HAVING [Quantity] - Sum(nz([MeasureQuantity])) <>0 ;

05.09.2008 at 02:14PM PDT, ID: 21536449

I tried using the HAVING and now I get a "You tried to execute a query that does not include the specified expression [quantity] - Sum(nz([MeasureQuantity])" as part of an aggregate function.

05.09.2008 at 02:57PM PDT, ID: 21536625

Rank: Genius

Now that I think of it, you may not be able to have an expression between an aggregate Sum(nz([MeasureQuantity])) and non-aggregate [Quantity] in the same SQL.

What tables to Quantity and MeasureQuantity belong in? Perhaps a subquery can be used to pull this off.

05.09.2008 at 03:17PM PDT, ID: 21536718

tblOrdersItems.MeasureQuantity

tblPO_Details.Quantity

05.09.2008 at 03:24PM PDT, ID: 21536756

Rank: Genius

Give this a whirl (air code, and it's late in the day)...

SELECT po.Quantity - foo.TQ, po.PO_DetailsID
FROM tblPO_Details po
LEFT JOIN (
SELECT tblPO_Details.YourIDField, Sum(nz([tblOrdersItems].[MeasureQuantity])) as TQ
FROM tblPO_Details
LEFT JOIN tblOrdersItems oi ON po.PO_DetailsID = oi.PO_DetailsID
GROUP BY tblPO_Details.YourIDField) foo ON po.YourIDField = foo.YourIDField
WHERE po.Quantity - foo.TQ <>0

05.09.2008 at 03:31PM PDT, ID: 21536780

What is po and foo?

05.09.2008 at 04:18PM PDT, ID: 21536981

Rank: Master

MohammedU:

They are table aliases...

05.09.2008 at 04:25PM PDT, ID: 21537036

Thats what I figured but the "foo" threw me off. What is foo?

05.09.2008 at 04:35PM PDT, ID: 21537108

Rank: Genius

GRayL:

air code: C'mon Jim, if you are going to use aliases, you must be consistent in their use throughout the query. BTW 'oi'??

05.09.2008 at 04:36PM PDT, ID: 21537111

Rank: Genius

You would need to add the Group By in jim's query to get the query working like:

SELECT ([Quantity]-Sum(nz([MeasureQuantity]))) AS TQ, tblPO_Details.PO_DetailsID
FROM tblPO_Details
LEFT JOIN tblOrdersItems ON tblPO_Details.PO_DetailsID = tblOrdersItems.PO_DetailsID
GROUP BY tblPO_Details.PO_DetailsID
HAVING ([Quantity] - Sum(nz([MeasureQuantity]))) <>0 ;

Nic;o)

05.09.2008 at 05:58PM PDT, ID: 21537426

Rank: Genius

>C'mon Jim, if you are going to use aliases, you must be consistent in their use throughout the query.
fook

05.09.2008 at 07:04PM PDT, ID: 21537556

Thanks, for your response on a friday especially!
I'm actually gonna run back to the office and put this on a flash drive and your SQL out.

<i just want to fill you in on the reasoning for the set up>

I have a continuous form where items can be assigned to installation measures. Many items can goto many measures.

ex:
total 3 plywood and 1 box of screws

2 plywood goto the door install
1 plywood and 1 box of screws goes to the window install. (hence many to many)

I have a continuous subform [sbfrmAssignItems] next to a listbox [lstMeasures] with 2 combo boxes ([cboItemID] which is locked, and [cboTempQuantity]) in sbfrmAssignItems .

The user selects the measure in the list box (which stays highlighted) and then in the subform enters the cboTempQuantitiy of items that are associated with that measure. They then Press F5 and the items are Appended to the many to many table [tblOrderItems] .

In order to allow the user to put values for multiple items before appending them to the many to many table. I had to create "temp table" so the comboboxes can have unique values in them. Without that temp table all the comboboxes assume the same values.

Measures and items are associated on the front end then F5 is pressed and then they are appended to the many to many table and are officially associated on the backend.

******Everything works up to this point******

What I want to do is limit the user to only the items that are available. If an item(s) are associated to a measure and there are no more to be associated I don't want them on the continuous subform.

Normally this would be easy, In the query for the form I would count the quantity of an item from [tblOrderItems] subtract them from the quantity in the PO_Details table and if they equaled 0 then I would exclude them via the criteria.

The problem is I can't get at the quantity in the [tblItemsAssoc] table because if I include it in my query I can no longer write values in the query (and the form). I tried using DSum it works but goes incredibly slow and is bordering unstable.

I tried one other super complex method which changes the criteria of the query that the form is based on in VBA but I can't get the form to refresh without closing and opening the form.

Most importantly what decides when a query can and cannot be written in? It is seemingly random to me why you can enter data in some querys and not in others.

How can I get that tblAssocItems included in my query to find out if there are items to yet be associated?

Or how can I repaint my subform after I change the underlying query of which it is based on (I would prefer not to go this route)?

05.09.2008 at 07:12PM PDT, ID: 21537577

I was also inconsistent in my above post. when I say tblItemsAssoc I mean tblOrderItems. I wish EE had an edit post option.

here are my 3 tables

tblPO_Details
____________
PO_DetailsID pk
Quantity
Price
ItemID fk
ExpendClassID fk

WXMeasureDetails (inconsistant naming convention I inherited a large un-normalized database, its to overused to change any time soon, but in the works)
_______________________
MeasureID pk
MeasureName (not normalized again)
[other none relevant fields]...

tblOrderItems (junction table)
_______________________
OrderItemsID pk
MeasureDetailsID fk
MeasureQuantity
PO_DetailsIID fk

This is a mouth full and may warrant another question to be fair points wise. It would be unfair for me to change my question midway through. I will try your query and post the results soon.

Feel free to comment on what I have posted so far.

Thanks a million,

JOe K.

05.10.2008 at 03:37AM PDT, ID: 21538435

Rank: Genius

Hi Joe, looks to me you have the classic Bill Of Materials problem.
Check: http://en.wikipedia.org/wiki/Bill_of_materials to get the general description.
and
http://www.mvps.org/access/modules/mdl0027.htm
for a sample.

BTW, the query still hod a flaw, try:
SELECT Sum(nz([Quantity])-nz([MeasureQuantity])) AS TQ, tblPO_Details.PO_DetailsID
FROM tblPO_Details
LEFT JOIN tblOrdersItems ON tblPO_Details.PO_DetailsID = tblOrdersItems.PO_DetailsID
GROUP BY tblPO_Details.PO_DetailsID
HAVING ([Quantity] - Sum(nz([MeasureQuantity]))) <>0 ;

As the Quantity needs to be "aggregated" too.

Nic;o)

05.10.2008 at 10:40AM PDT, ID: 21539838

Do you know what decides whether or not a query can be written too? If I knew that it would help a ton for alot of future queries. I am going to work right now to get the project.

05.10.2008 at 11:39AM PDT, ID: 21540104

Rank: Genius

Hard time to understand your statement: Do you know what decides whether or not a query can be written too?
Basically every table row processing can be done using a query.
A simple SELECT query will allow you to filter the rows and a GroupBy will allow aggregations like Sum, Count, Min, Max, etc.
For modifications the UPDATE can be used and for removal the DELETE. Even tables can be created using queries.

What's the problem you see to decide to write a query ?

Nic;o)

05.10.2008 at 11:48AM PDT, ID: 21540130

Sorry about the delay I just ran to the office.

Let me re-phrase my question. Some queries allow to me edit the data, others do not. I am in a situation where I can't edit the data and therefore cannot edit the form. It will not even allow me to enter anything into combo box.

Does that make sense?

I also tried to make a query based off of one table (a many to many table) and I couldn't add/edit records there either.

JOe K.

05.10.2008 at 12:00PM PDT, ID: 21540164

Rank: Genius

For forms I generally use a table (or a one table query) as the record source. When there's a linked table I use a linked sub form, thus Access will automatically filter the sub form and fill in the linking key of the main form in the subform new rows.
I call it "go by the flow" as it's the way Access supports by design.
When you use a query with a linked table that has a 1:N relation, then the query automatically turns into a "read-only" one as Access can't update the fields in the multiple occurrences of the "main" table.

The combo box refusal can have other reasons, but in general it's best to use a (hidden) unique first (bound) column and a description.

Getting the idea ?

Nic;o)

05.10.2008 at 12:10PM PDT, ID: 21540195

These 2 sub-queries work

qryAssignedTotals - this is the functioning aggregate sum query. This is used to "filter" the PO details
_____________________________________________________________
SELECT Sum(nz([Quantity])-nz([MeasureQuantity])) AS TQ, tblPO_Details.PO_DetailsID
FROM tblPO_Details LEFT JOIN tblOrdersItems ON tblPO_Details.PO_DetailsID = tblOrdersItems.PO_DetailsID
GROUP BY tblPO_Details.PO_DetailsID
HAVING (((Sum(nz([Quantity])-nz([MeasureQuantity])))<>0));

qryItemsToBeAssigned - TempQuanity which is part of the sysOrderItemAssoc table is a temporary table for the purposes of holding the values in a temp location so they can be seen on the front end until they are sent to the back end
____________________________________________________________
SELECT tblPO_Details.PO_DetailsID, tblPO.FileNumber, tblPO_Details.Quantity, sysOrdersItemAssoc.TempQuantity
FROM (tblPO INNER JOIN (lstExpendClass INNER JOIN tblPO_Details ON lstExpendClass.ExpendClassID = tblPO_Details.ExpendClassID) ON tblPO.PO_ID = tblPO_Details.PO_ID) LEFT JOIN sysOrdersItemAssoc ON tblPO_Details.PO_DetailsID = sysOrdersItemAssoc.DetailsLink
WHERE (((lstExpendClass.ExpendClass) In ("Reg Cont","Reg Mat","H&S Cont","H&S Mat")))
ORDER BY tblPO_Details.PO_DetailsID;

This query opens but the problem is I can't edit the records in the TempQuan field.
qryItemsToBeAssignedForm - is a combination of these two subqueries
________________________________________________________________
SELECT qryItemsToBeAssigned.PO_DetailsID, qryItemsToBeAssigned.FileNumber, qryItemsToBeAssigned.TempQuantity, qryItemsToBeAssigned.Quantity
FROM qryAssignedTotals INNER JOIN qryItemsToBeAssigned ON qryAssignedTotals.PO_DetailsID = qryItemsToBeAssigned.PO_DetailsID;

any idea why I can't write/edit the TempQuan field?

Thanks, JOe K.

05.10.2008 at 12:14PM PDT, ID: 21540208

>>Getting the idea ?

Kind of.

The combobox refusal is because the control source is the TempQuan field.

However, the 1:N relationship not allowing a query to be edited I don't understand. I have a ton of queries where there are 1 to many relationships that I write too. I don't see how someone could have a normalized database with out that.

for example: qryItemsToBeAssigned the 2nd query I have posted above I can input values into TempQuan

Thanks, JOe K.

05.10.2008 at 12:22PM PDT, ID: 21540233

>> When there's a linked table I use a linked sub form, thus Access will automatically filter the sub form and fill in the linking key of the main form in the subform new rows.

Lets say for example I have a PO_Details table :)

this table has 2 linked tables to it one called lstItem and lstClassExpend

SELECT DISTINCT tblPO_Details.PO_DetailsID, tblPO_Details.Quantity, tblPO_Details.UnitPrice, lstItem.Item, lstExpendClass.ExpendClass
FROM lstExpendClass INNER JOIN (lstItem INNER JOIN tblPO_Details ON lstItem.ItemID = tblPO_Details.ItemID) ON lstExpendClass.ExpendClassID = tblPO_Details.ExpendClassID;

The way I currently have this set up in my database is:

one form
cboItem txtQuantity txtUnit Price cboClassExpend

but is what your saying: this would be 3 forms???

parent (form) would contain PO_Details, ItemID, Quantity, UnitPrice, ClassExpendID

2 children (subforms)
one contains Item, and ItemID
one contains ClassExpend, ClassExpendID

Thanks, JOe K.

05.10.2008 at 12:57PM PDT, ID: 21540321

Rank: Genius