Aggregat Function Error

[LadyDi]

I am trying to create an Append Query and I keep getting an error that I don't know what it means. The errors says "You tried to execute a query that does not include the specified expression "Preapproval based on" as part of an aggregate fuction." "Preapproval based on" is a field in my table. I checked the query several times and this field is in the query and set to append to the corresponding field in the table. It is not part of an aggregate function (at least not that I know of). What do I need to do to get this query to run?

 

[ajetrumpet3]

I think it pops up when you leave a field out of a GROUP BY clause that should be there.

 

[Brianwarnock]

If you are running a Totals query then every field in the Select statement must have an entry from the Totals row drop down eg Group by, Max, Count etc and if any one field has one of these then the query automatically becomes a Totals query. The usual inadvertent creation of the Totals query is using something like Sum(myfield).

Post you SQL and somebody may be able to help.

Brian

P S Good to see you back Adam.

 

[MSAccessRookie]

I am trying to create an Append Query and I keep getting an error that I don't know what it means. The errors says "You tried to execute a query that does not include the specified expression "Preapproval based on" as part of an aggregate fuction." "Preapproval based on" is a field in my table. I checked the query several times and this field is in the query and set to append to the corresponding field in the table. It is not part of an aggregate function (at least not that I know of). What do I need to do to get this query to run?

Without seeing the query (you did not include it), I would say that you are either missing a Group By Statement, or missing an entry in your Group By Statement.

An aggregate SQL Query looks something like this:

[B]Selete[/B] [COLOR=green][B]Something[/B][/COLOR], Count([COLOR=purple][B]Something Else[/B][/COLOR])
[B]From[/B] ([COLOR=royalblue][B]Some Table or Group of Tables[/B][/COLOR])
[B]Where[/B] ([COLOR=royalblue][B]Any Related Conditions[/B][/COLOR])
[B]Group By[/B] [B][COLOR=green]Something[/COLOR][/B]

 

[LadyDi]

I tried adding a Group by Clause in the query, but I think I did something wrong. Now it keeps telling me "Undefined function '[PreApproved Main Table]' in expression." This is the SQL statement behind the query.

INSERT INTO [PreApproved Main Table] ( [Preapproval based on], [Group 1 Discount], [Group 2 Discount], [Group 4 Discount], [Group 5 Discount], [Group 5 (CCTV) Discount], Other, [Special Billing Terms], Salesrep, [Regional Sales Manager], [Area Vice President], [Date Approved], [Date Reviewed], [Additional Information], Autonumber, [Reference Number] )
SELECT [PreApproved Main Table].[Preapproval based on], [PreApproved Main Table].[Group 1 Discount], [PreApproved Main Table].[Group 2 Discount], [PreApproved Main Table].[Group 4 Discount], [PreApproved Main Table].[Group 5 Discount], [PreApproved Main Table].[Group 5 (CCTV) Discount], [PreApproved Main Table].Other, [PreApproved Main Table].[Special Billing Terms], [PreApproved Main Table].Salesrep, [PreApproved Main Table].[Regional Sales Manager], [PreApproved Main Table].[Area Vice President], [PreApproved Main Table].[Date Approved], [PreApproved Main Table].[Date Reviewed], [PreApproved Main Table].[Additional Information], [Autonumber]=Max([Customer Table]!autonumber) AS Expr1, [PreApproved Main Table].[Reference Number]
FROM [PreApproved Main Table]
WHERE ((([PreApproved Main Table].[Reference Number]) Like [forms]![Main Preapproved Form]![Reference Number]))
GROUP BY [PreApproved Main Table] ( [Preapproval based on], [Group 1 Discount], [Group 2 Discount], [Group 4 Discount], [Group 5 Discount], [Group 5 (CCTV) Discount], Other, [Special Billing Terms], Salesrep, [Regional Sales Manager], [Area Vice President], [Date Approved], [Date Reviewed], [Additional Information], Autonumber, [Reference Number] );


What do you suggest?

 

[MSAccessRookie]

I tried adding a Group by Clause in the query, but I think I did something wrong. Now it keeps telling me "Undefined function '[PreApproved Main Table]' in expression." This is the SQL statement behind the query.

INSERT INTO [PreApproved Main Table] ( [Preapproval based on], [Group 1 Discount], [Group 2 Discount], [Group 4 Discount], [Group 5 Discount], [Group 5 (CCTV) Discount], Other, [Special Billing Terms], Salesrep, [Regional Sales Manager], [Area Vice President], [Date Approved], [Date Reviewed], [Additional Information], Autonumber, [Reference Number] )
SELECT [PreApproved Main Table].[Preapproval based on], [PreApproved Main Table].[Group 1 Discount], [PreApproved Main Table].[Group 2 Discount], [PreApproved Main Table].[Group 4 Discount], [PreApproved Main Table].[Group 5 Discount], [PreApproved Main Table].[Group 5 (CCTV) Discount], [PreApproved Main Table].Other, [PreApproved Main Table].[Special Billing Terms], [PreApproved Main Table].Salesrep, [PreApproved Main Table].[Regional Sales Manager], [PreApproved Main Table].[Area Vice President], [PreApproved Main Table].[Date Approved], [PreApproved Main Table].[Date Reviewed], [PreApproved Main Table].[Additional Information], [Autonumber]=Max([Customer Table]!autonumber) AS Expr1, [PreApproved Main Table].[Reference Number]
FROM [PreApproved Main Table]
WHERE ((([PreApproved Main Table].[Reference Number]) Like [forms]![Main Preapproved Form]![Reference Number]))
GROUP BY [PreApproved Main Table] ( [Preapproval based on], [Group 1 Discount], [Group 2 Discount], [Group 4 Discount], [Group 5 Discount], [Group 5 (CCTV) Discount], Other, [Special Billing Terms], Salesrep, [Regional Sales Manager], [Area Vice President], [Date Approved], [Date Reviewed], [Additional Information], Autonumber, [Reference Number] );


What do you suggest?

• The Code marked in Red is not necessary as a part of the Group By Function. Removing it will help.
• I am not sure exactly what the code marked in Brown is supposed to be doing.
• The Code marked in Green is Redundant and can either be removed or left in as you see fit.

In addition, The names of your Table and Columns contain characters (spaces) that are non stanadard. Removing these is also a good idea

 

[LadyDi]

Re: Aggregate Function Error

The part of the code highlighted in brown is because I am trying to run two append queries consecutively. The form that I have the append query on contains fields from two tables. I have a customer table that houses the customer name, city, and state. Each customer in the Customer table is assigned an autonumber to link back to the table containing the discount information. I created this table so that the customer information only has to be entered once. However, with the discounts, if one customer has a subsidiary then the subsidiary will recieve the same discount as the parent company. The subsidiary company needs to have its own record in the database. I am trying to make it so that the person entering the information does not have to enter the same information for every subsidiary of one company. I can get append queries to work when I am just trying to use one table. However, I am not having much luck when I am trying to use two tables. Is there a better way to go about doing this? The code that is highlighted in brown is telling (or at least trying to tell) the computer to pick up the biggest autonumber in the Customer table and connect it to this record.

 

[ajetrumpet3]

holy cow this is complicated, even for MY deep mind! Congrats to the Rookie.

 

[LadyDi]

I'm afraid, this still is not working. I took out wording that you recommended. Now I am getting an error message stating "Circular reference caused by alias 'Autonumber' in query definition's Select list."

Is it possible to create an append query with two tables?

 

[MSAccessRookie]

I'm afraid, this still is not working. I took out wording that you recommended. Now I am getting an error message stating "Circular reference caused by alias 'Autonumber' in query definition's Select list."

Is it possible to create an append query with two tables?

The Circular Reference usually refers to an Alias pointing to an ambiguous column (more that one Table in the query having a column with the same name). Try changing [Customer Table]!autonumber to [Customer Table].autonumber and see what happens.

 

[LadyDi]

Now I get an error message stating "You tried to execute a query that does not inlcude the specified experssion '[Autonumber] = Max([Customer Table].[Autonumber])' as part of an aggregate function."

 

[LadyDi]

Re: Aggregate Function Error

I added the Customer table to my query and it is working better. However, I can't seem to get the query to show me the biggest autonumber in the Customer Table in addition to the fields for a certain record in the Preapproved table. I even tried a DMax function (Expr2: DMax("autonumber","Customer Table")) and that doesn't seem to work either. The maximum autonumber in the customer table is 16, but the query keeps returning 2.

 

[Brianwarnock]

I think that this
[Autonumber] = Max([Customer Table].[Autonumber])
should be
Max([Customer Table].[Autonumber]) as Autonumber

and you don't need to group by Autonumber.

Brian

 

[LadyDi]

Re: Aggregate Function Error

I entered the code as you suggested. However, the query is still returning a 2 instead of a 16. Could it have something to do with the fact that I am trying to use an Outer Join query?

 

[Brianwarnock]

What are you joining on? The only data you appear to need from the Customer table is the Max of Autonumber, I must admit to not liking that as a field name, however that as may be, I would have thought that you would not need to join the tables.

Brian

 

[LadyDi]

Re: Aggregate Function Error

The tables are joined on the Autonumber field (I actually didn't assign this field that name, I am working with someone else on this project and that is the name he assigned it. Would it work better if that field had a different name?). I tried taking the Customer Table out of the query, but then the query can't seem to find that table at all.

 

[Brianwarnock]

I cant get my head round the idea of joining tables on a field that you want the max of from one of the tables, maybe I'm missing the point.

Brian

 

[LadyDi]

Maybe I don't need to join the tables, but if I take the one table out, my query doesn't work at all. How should I write the query, to get the maximum number in the Customer Table and all other information from another table?

 

[Brianwarnock]

Just don't join the tables, you will have what is known as a cartesian join, normally this causes an excess of records to be selevcted but I don't think it will in this case, however if it does one option might be to create a query to get the Max of customer.autonum and input that query, again with no join and it will of course only have one entry, actually that might be a better performance option.

Brian

 

[Brianwarnock]

I forgot to answer your question regarding naming a field Autonumber, I would not use any ACCESS reserved words Integer, Date, NOW , Autonumber etc but you can always prefix them if it gives a meaningful name and you don't mind the extra typing.

Brian