Expression not a part of an aggegrate function

[DNASok]

I have a query that will be assisting me find pricing based upon quantity ranges of specific equiment for a given FY.

I'm no access expert, and I keep getting "You tried to execure a query that does not include the specified expression...as part of an aggregate function".

I have tried several things, but cannot seem to figure this one out.

The SQL of my query is as follows:

SELECT IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 1 And 4,[04b Pricing Products]![01-04],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 5 And 10,[04b Pricing Products]![05-10],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 11 And 25,[04b Pricing Products]![11-25],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 26 And 50,[04b Pricing Products]![26-50],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 51 And 100,[04b Pricing Products]![51-100],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])>100,[04b Pricing Products]![101+])))))) AS Price
FROM [Current Orders], [04a Pricing Fiscal Years] INNER JOIN [04b Pricing Products] ON [04a Pricing Fiscal Years].[Fiscal Year] = [04b Pricing Products].[Fiscal Year]
WHERE ((([Current Orders].Status) Like "Awaiting" & "*") AND (([Current Orders].Nomenclature) Like "*" & [Forms]![04c Test Query for ROM Support]![Nomenclature] & "*") AND (([Current Orders].[Part Number]) Like "*" & [forms]![04c Test Query for ROM Support]![Part Number] & "*") AND (([04b Pricing Products].[Fiscal Year]) Like "*" & [Forms]![04c Test Query for ROM Support]![Fiscal Year] & "*"));

 

[pr2-eugin]

If you go into the Query Design Grid, Do you see a Row called Totals?

If not add it from the Query design option on top..

By default the Total will be Group By change it to Expression..

 

[DNASok]

If you go into the Query Design Grid, Do you see a Row called Totals?

If not add it from the Query design option on top..

By default the Total will be Group By change it to Expression..

I have 5ea columns:
1. Price: IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 1 And 4,[04b Pricing Products]![01-04],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 5 And 10,[04b Pricing Products]![05-10],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 11 And 25,[04b Pricing Products]![11-25],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 26 And 50,[04b Pricing Products]![26-50],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 51 And 100,[04b Pricing Products]![51-100],IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])>100,[04b Pricing Products]![101+]))))))
- Total: Expression
2. Status
- Total: Where
- Criteria: Like "Awaiting" & "*"
3. Nomenclature
- Total: Where
- Criteria: Like "*" & [Forms]![04c Test Query for ROM Support]![Nomenclature] & "*"
4. Part Number
- Total: Where
- Criteria: Like "*" & [forms]![04c Test Query for ROM Support]![Part Number] & "*"
5. Fiscal Year
- Total: Where
- Expression: Like "*" & [Forms]![04c Test Query for ROM Support]![Fiscal Year] & "*"

I attempted to change all of these to "Group By" with the same Expressions, but this still spits out the same error.

 

[pr2-eugin]

How about writing a function to handle the complicated IIF?

 

[DNASok]

How about writing a function to handle the complicated IIF?

Don't know how to do that!

 

[Brianwarnock]

The second ) is in the wrong place in every iif it should be before the Between. However I don't think you need the Sum as you effectively say A+B in each case

Brian

 

[Brianwarnock]

On reflection I think that the first Nz and Sum are wrong , each iif should be like

IIf(Sum(Nz([Current Orders]![Quantity]) +Nz([forms]![04c Test Query for ROM Support]![Quantity])) Between 5 And 10

Brian

 

[DNASok]

The second ) is in the wrong place in every iif it should be before the Between. However I don't think you need the Sum as you effectively say A+B in each case

Brian

Sorry, but I don't follow why you feel the second ")" is in the incorrect place. I assume you are talking about the IIf(Nz(Sum([Current Orders]![Quantity])). However, I thought the first was for the "Nz", and the second for the "Sum" expressions.

"Sum" is required as it is summing the total orders of a given product which the status is Like "Awaiting" & "*".

Perhaps I am misunderstanding what you mean, but I need to:

• Have the total number of a given product which is not closed; i.e. awaiting something
• Add this total to what is currently being requested on the form

 

[DNASok]

On reflection I think that the first Nz and Sum are wrong , each iif should be like

IIf(Sum(Nz([Current Orders]![Quantity]) +Nz([forms]![04c Test Query for ROM Support]![Quantity])) Between 5 And 10

Brian

I do not believe it can be that. I must turn nulls to zero on the sum/total of [Current Orders]![Quantity].

The recommended approach would not require the "sum" at all, and would not provide me the total of the [Current Orders]![Quantity].

Or am I likely missing something?

I will try it though!

 

[DNASok]

I do not believe it can be that. I must turn nulls to zero on the sum/total of [Current Orders]![Quantity].

The recommended approach would not require the "sum" at all, and would not provide me the total of the [Current Orders]![Quantity].

Or am I likely missing something?

I will try it though!

Tried it; no joy!
Gave me the same Expression not a part of an aggegrate function error!

 

[Brianwarnock]

If I understand your posts you are trying to sum a column and add other data to it at the same time. Unless 2010 has changed things in a big way you cannot do that after all the Sum of a column will only be available after the query is finished.
I thought that you were summing horizontally.
Brian

 

[DNASok]

If I understand your posts you are trying to sum a column and add other data to it at the same time. Unless 2010 has changed things in a big way you cannot do that after all the Sum of a column will only be available after the query is finished.
I thought that you were summing horizontally.
Brian

Any recommendations then?

I originally had seperate columns for:
- "Quantity Requested"
Quantity Requested: [04c Test Query for ROM Support]![Quantity]
- "Quantity Awaiting Order"
Quantity Awaiting Order: [Current Orders]![Quantity]
- "Total Quantity"
Total Quantity: [Quantity Requested] + [Quantity Awaiting Order]

I then used this "Total Quantity" in place of the (Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]).

This provded the same results, but had what I felt were unnecessary colums as I thought the calulating of values could just be in the SQL code.

 

[Brianwarnock]

I'm not sure that I fully understand what is going on but I think that you need two queries
One to do the total summing and then a second to take those totals in and do the iifs thus replacing the Nz(sum(currentorders.quantity)) in your original SQL.

Brian

 

[DNASok]

I believe I have figured this out, I had to reorder how things were being processed/equated; I have the following now working:

SELECT [Forms]![04c Test Query for ROM Support]![Quantity] AS [Requested Qt], [Current Orders]![Quantity] AS [Awaiting Qt], IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 1 And 4,[04b Pricing Products]![01-04],IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 5 And 10,[04b Pricing Products]![05-10],IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 11 And 25,[04b Pricing Products]![11-25],IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 26 And 50,[04b Pricing Products]![26-50],IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 51 And 100,[04b Pricing Products]![51-100],IIf(Nz([Requested Qt] + [Awaiting Qt])>100,[04b Pricing Products]![101+])))))) AS Price
FROM [Current Orders], [04b Pricing Products]
WHERE ((([Current Orders].Status) Like "Awaiting" & "*") AND (([Current Orders].Nomenclature) Like "*" & [Forms]![04c Test Query for ROM Support]![Nomenclature] & "*") AND (([Current Orders].[Part Number]) Like "*" & [forms]![04c Test Query for ROM Support]![Part Number] & "*") AND (([04b Pricing Products].[Fiscal Year]) Like "*" & [Forms]![04c Test Query for ROM Support]![Fiscal Year] & "*"));

Still fine tuning it, but it does nearly work the way I need it to!

 

[DNASok]

I believe I have figured this out, I had to reorder how things were being processed/equated; I have the following now working:

SELECT [Forms]![04c Test Query for ROM Support]![Quantity] AS [Requested Qt], [Current Orders]![Quantity] AS [Awaiting Qt], IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 1 And 4,[04b Pricing Products]![01-04],IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 5 And 10,[04b Pricing Products]![05-10],IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 11 And 25,[04b Pricing Products]![11-25],IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 26 And 50,[04b Pricing Products]![26-50],IIf(Nz([Requested Qt] + [Awaiting Qt]) Between 51 And 100,[04b Pricing Products]![51-100],IIf(Nz([Requested Qt] + [Awaiting Qt])>100,[04b Pricing Products]![101+])))))) AS Price
FROM [Current Orders], [04b Pricing Products]
WHERE ((([Current Orders].Status) Like "Awaiting" & "*") AND (([Current Orders].Nomenclature) Like "*" & [Forms]![04c Test Query for ROM Support]![Nomenclature] & "*") AND (([Current Orders].[Part Number]) Like "*" & [forms]![04c Test Query for ROM Support]![Part Number] & "*") AND (([04b Pricing Products].[Fiscal Year]) Like "*" & [Forms]![04c Test Query for ROM Support]![Fiscal Year] & "*"));

Still fine tuning it, but it does nearly work the way I need it to!

I was wrong, still not working! The issue appears to be when I sum/total the currently awaiting orders; works fine when I do not do this, but I need to!

 

[pr2-eugin]

If you could explain in simple English of what are the conditions and their appropriate values, we might be able to help you write a function..

Example..

• If the fruit selected is 'Banana' and quantity is between 1 and 5 then the result should be 0.5
• If the fruit selected is 'Banana' and quantity is between 6 and 14 then the result should be 0.75
• If the fruit selected is 'Banana' and quantity is over 15 then the result should be 1
• If the fruit selected is 'Apple' and quantity is less than 10 then the result should be 0.5
• If the fruit selected is 'Apple' and quantity is over 10 then the result should be 1

 

[DNASok]

If you could explain in simple English of what are the conditions and their appropriate values, we might be able to help you write a function..

Example..

• If the fruit selected is 'Banana' and quantity is between 1 and 5 then the result should be 0.5
• If the fruit selected is 'Banana' and quantity is between 6 and 14 then the result should be 0.75
• If the fruit selected is 'Banana' and quantity is over 15 then the result should be 1
• If the fruit selected is 'Apple' and quantity is less than 10 then the result should be 0.5
• If the fruit selected is 'Apple' and quantity is over 10 then the result should be 1

OK, I'll try:

• There are currently 20 bananas "Awaiting" to be ordered
• The cost quantity break for bananas is: 1-4, 5-10, 11-25, 26-50, 51-100, and 101+ based on a given FY (FY14, FY15, FY16, etc...)
• A new order comes in for 10 more bananas, so the total bananas WILL BE 30ea
• On the form the user is using to fill-out the data, he needs to know what to tell the customer how much there bananas will cost

What I basically have in my test database is:

• Table showing all orders
• Form to input new orders which has sections for: Product, Quantity, Cost (this is where the query is being used)

What I have been able to do is get this to work, but only as long as there is already an order of "bananas". If I'm ordering "apples", the calculation (Current Qts + Requested Qts) does not work.

I have two (2) queries, one which finds the current orders of the specified Product that are "Awaiting", and another which uses this query/sub-query to add it to what is the new data.

Like I said, this works as long as there is already an order of the specified product, but not if there is not any exisiting "Awaiting" orders. I believe the problem is in the sub-query where it does not just show Product - Apples, Status - "NONE", and Quantity 0.

The sql for the query I believe is the problem is as follows:
SELECT [Current Orders].Status, [Current Orders].Nomenclature, [Current Orders].[Part Number], Sum([Current Orders].Quantity) AS SumOfQuantity
FROM [Current Orders]
GROUP BY [Current Orders].Status, [Current Orders].Nomenclature, [Current Orders].[Part Number]
HAVING ((([Current Orders].Status) Like "Awaiting" & "*") AND (([Current Orders].Nomenclature) Like "*" & [Forms]![04c Test Query for ROM Support]![Nomenclature] & "*") AND (([Current Orders].[Part Number]) Like "*" & [forms]![04c Test Query for ROM Support]![Part Number] & "*"));

What I need this to do, but haven't been able to get to work, is:
If Status = Null, then "NONE"
If Product = Null, then [Forms]![04c Test Query for ROM Support]![Nomenclature]
If Qt = Null, then 0

I tried Nz for the SumOfQuantity, but that still yeilded the query being blank.

I also tried some funky stuff for Status and Product to no avail.

Greatly appriciate those looking into this for me. Thought I had it all figured out when I had success with something already "Awaiting", but fell like a brick :banghead: when it didn't work for something not "Awaiting".

I know I could use tables to store the data, and then do a query(ies) based upon them, but I do not want to do this as there is potential that the NEW order could be cancelled. Once the order is placed (i.e. funds transfered), it will be available for the next order, where the awaiting will now include the original 20ea + the last 10ea.

Thank you!