Tried to execute a query that doesn't include the specified expression 1

[zach028]

I'm trying to run a query and am getting the error message

"You tried to execute a query that doesn't include the specified expression as part of an aggregate function or grouping"

I've been all over the net this morning and can't find something that will help. Here's the scenario:

I have two tables, we'll call them MAIN and PAYMENTS, which have one-to-many relationship, based on field ACCTNUM (Account Number). I want to update field MAIN.CURBAL (current balance) to equal MAIN.AMTCLSD (Amount closed) minus Sum(PAYMENTS.AMT) (payment amount).

This should be really easy but I can't seem to get around this message. Any help would be greatly appreciated.

 

[jdegeorge]

Zach:

What's the code you put in the expression? That's what appears to be the problem.

It should be formatted as EXPR1:[FIELD1]-[FIELD2].

Hope this helps.

Jim

"Get it right the first time, that's the main thing..."

 

[jdegeorge]

Zach:

Update...

I just saw that you were taking values from 2 tables.

Modify the expression code to be:

EXPR1: [TABLE1]![FIELD1]-[TABLE2]![FIELD2]

Jim

"Get it right the first time, that's the main thing..."

 

[dodge20]

If you do a select that contains regular fields, and aggregate functions, you have to put the regular fields in a group by clause in order for the statement to work correctly.

select field1, field2, field3, sum(field4)
from table
where .....
group by field1,field2,field3

Dodge20

 

[zach028]

UPDATE [MAIN] INNER JOIN [PAYMENTS] ON [MAIN].acctnum = [PAYMENTS].acctnum SET [MAIN].curbal = Expr1 WHERE Expr1 = [MAIN].[amtclosed]-Sum([PAYMENTS].[pmt]);

Here's the SQL. I added the table name to the where clause (I didn't have it in before). Now it tells me "Can't have aggregate function in WHERE clause".

 

[jdegeorge]

Zach

If you have an expression (aggregate function), you can't put any WHERE conditions on it.

"Get it right the first time, that's the main thing..."

 

[zach028]

OK, so I can't do it that way. Here's what I originally had as my SQL statement:

UPDATE [MAIN] INNER JOIN [PAYMENTS] ON [MAIN].acctnum = [PAYMENTS].acctnum SET [MAIN].curbal = [MAIN].[amtclosed]-Sum([PAYMENTS].[pmt]);

I still can't see why this won't work! I appreciate you sticking with me on this one...

 

[jdegeorge]

Do your tables/fields look like this?

MAIN
ACCTNUM
CURBAL
AMTCLSD

PAYMENTS
ACCTNUM
AMT

Can you let me know a few values for these tables and what your expected items should look like? This should be relatively simple to do.

Jim

"Get it right the first time, that's the main thing..."

 

[dodge20]

You may be missing a set of () on your equal statement. Try using this.

UPDATE [MAIN] INNER JOIN [PAYMENTS] ON [MAIN].acctnum = [PAYMENTS].acctnum SET [MAIN].curbal = ([MAIN].[amtclosed]-Sum([PAYMENTS].[pmt]));



Dodge20

 

[zach028]

Well, I just pulled two of our progammers over here to look at this and they couldn't figure it out. When I first set this up (about two years ago), I had a make-table query and then an update from that query. I couldn't figure out why I ever would have done that, but I guess it was because I couldn't get around the error.

I have tried the parenthesis as well and it did not change anything.

Yes, Jim, you've got the structure right. Any values could work for samples. Here are a few.

MAIN
ACCT AMTCLSD CURBAL
1234 $100.00 $100.00
4321 $200.00 $80.00
1111 $300.00 $300.00

PAYMENTS
ACCT PMTS
1234 $20.00
1234 $30.00
4321 $50.00

 

[jdegeorge]

Zach:

Your code didn't work. If you get out of SQL VIEW and look at DESIGN VIEW, you'll see a field called [EXPR1] with a WHERE clause that's your formula. This isn't how EXPRESSIONS work. It should be something like "EXPR1: [MAIN].[AMTCLSD]-Sum([PAYMENTS].[PMTS]). But, don't try this, it doesn't work either. Just trying to point out that you're not using EXPRESSIONS properly.

In any event, I was able to create 2 queries that will DISPLAY your current balance, but not update the MAIN table.

Name your queries Query1 and Query2.

Query1 SQL is:

SELECT MAIN.ACCT, MAIN.AMTCLSD, Query2.SumOfPMTS, [AMTCLSD]-[SumOfPMTS] AS Balance
FROM MAIN LEFT JOIN Query2 ON MAIN.ACCT = Query2.ACCT;

Query2 SQL is:
SELECT PAYMENTS.ACCT, Sum(PAYMENTS.PMTS) AS SumOfPMTS
FROM PAYMENTS
GROUP BY PAYMENTS.ACCT;

If you run Query1, you'll get a display of the current balance but the MAIN table won't have an updated value in the CURBAL field. With more time I'm sure we can work this out, but I have to get back to my work.

You can use Query1 as the basis for reports and forms and get the desired effect until we can figure out how to get CURBAL updated.

Jim

"Get it right the first time, that's the main thing..."

 

[zach028]

I have a whole lot of forms, queries, and reports already set up to look to that field, so for the time being I'll switch to the make-table query to get it done. I look forward to seeing any solutions you have to offer! thank you!

 

[jdegeorge]

Zach

Good luck. Let me know how it goes.

Jim

"Get it right the first time, that's the main thing..."