error message

[buckwheat]

does anyone know what this error message means please? in my query, I attempted to sum two fields: total:=sum([field1]+[field2]) both field types are numbers.

error message: "You tried to execute a query that does not include the specified expression "Emp#" as part of the aggregate function"



any help would be appreciated.

[pbaldy]

Sure, you need a GROUP BY clause in your SQL, and it needs to include that field. Clicking on the "E" Totals icon will add a row to the design grid that may make it more obvious.

[buckwheat]

Thanks Paul, I tried the Group By, but then got another error message saying I cannot have a aggregate function by in Group By clause (emp# is in the query). I'm kicking around the best way to add some calculations. I'd like to put them in a form so users can see the calculation as they are entering values, instead of in a query, but I need the form to open blank so I set it as "data entry," doing this doesn't run the calculation. If I don't set as "data entry" the formula only works when I exit and reenter the form. Any suggestions please?

[pbaldy]

Well, a field is either in the GROUP BY clause or has an aggregate function. For a form, doesn't a textbox in the footer like:

=Sum([field1]+[field2])

work? It does for me.

[buckwheat]

oh yeah, I forgot about the footer . . . been several years. problem is my form is set up as a tab control and each tab has several formulas

for the query, not sure why the simple code doesn't work. The code is simple: Total:=sum([Clinical FTE]+[Medical Administration FTE]) both are number type fields and Emp# is in query. The query will return results if I do not put in the calculation.

[ssanfu]

Total:=sum([Clinical FTE]+[Medical Administration FTE])

When you use the Sum() function, Access expects it to be in a totals query. If you are not using a totals query, you should not use the sum function in the calculation..

If you are trying to add two fields in a query in the same record, use Total:= [Clinical FTE]+[Medical Administration FTE]


I've seen people use "=sum([A1]+[B2])" as a formula in Excel (.... but this is redundant) but you can't use that formula in a non totals query in Access.

This is different than using "=Sum([field1]+[field2])" in a control on a form. (still shouldn't use sum() because of the "+" operator)

[buckwheat]

Thanks Steve.

[buckwheat]

Steve,

I have another question on this subject please. I have my data entry form set with tab controls, the source is table1. I then have a query using table1 along with the "total" fields as you described above, this will generate my report with calculations. What I'd like to accomplish is having the formulas for the same query fields in my data entry form that populates table1. I can get this to work properly on the first "tab" only. When I select the second tab and have to reference a field from tab 1 as part of the formula, it errors out. . . is there any way to do this. . . reason is, I believe the client will want to see the calculations as they enter the data.

[ssanfu]

Anytime I have to do calculations, I try to put them in the query. Then I can use a bound control for display. Of course, summing to get a total in the footer is a different matter.

The tab control is a way of separating controls on a form.
What are the control names and the calculations? Maybe some sample data?

[buckwheat]

just as example: one tab is call clinical and had a field clinical FTE with a number value. Second tab is called Performance with a field commission. on second tab "commission" would have a formula to add the "clinical FTE" from tab one and the "Performance" field from tab two.

[ssanfu]

You should have a query for the record source for the form. In the query, in a blank column, add the following formula:

Commission: [clinical FTE] + Performance

Then add the commission field to the 2nd tab of the tab control.

[buckwheat]

Thanks. That I understand if using a query, my problem is the tab control source is a table . . . causing the conflict. So if I'm understanding correctly, I can have the tab control source be a query . . . and set as data entry form, with results returning to a table?

[ssanfu]

my problem is the tab control source is a table

Tab controls do not have a control source (that I can find). The controls on the tab have a control source... but the tab control itself does not.

The form has a record source. Are you saying the form record source is a table? If so, change the form record source to a query.
With rare exceptions, all of my forms/reports have a query as the record source. And my queries are usually saved queries, not a SQL string in the record source. That way I can use the same query for a form and report.

My reasons for using saved queries for form/report record sources:
A query is a "virtual table".... (looks like a table, but the query has advantages a table doesn't)
A query allows you to have multiple, (usually) related tables joined to create a new dataset (a "virtual table") on the fly.
A query allows you to sort the records... a table does not.
A query allows you to limit the returned records.... a table does not.
A query allows you to do calculations( in effect create new "fields" on the fly.... a table does not... (or should not since its function is to store the data)
With a saved query, you can use it as the record source in forms subforms and reports.... just by changing the criteria.

[buckwheat]

ok thanks, I'll use this approach.

[buckwheat]

Ok, I'm finally back to this project. I've changed my "tab control" form's record source to a query so I would have my calculated fields . . . I need to be able to enter data in various fields that will populate in a table. . .I am not able to do this using a query . . . is there a setting I need to update?