Query Formula

[aimless54]

I am new to the access database world- I have a database in which I have projects that each have a priority- to get the priority # there are about 10 categories that need to total up to give me that priority #

Every formula I have tried, has given me an error message of some sort! PLEASE HELP, and please let me know if I am not explaining this enough!

 

[EdFred]

A new field with

PriorityFormula: [Field1] + [Field2] + [Field3]....+ [Field10]

Doesn't work?
How many tables in the query?
All one table?

 

[aimless54]

Locks up the cells so you cannot input #'s...I am missing something from the equation-- literally!

 

[aimless54]

Just two tables- one that is important, the second doesn't really pertain its for other information. Since I am new at this- I just want to make sure I am creating the formula in the right place- under Query, Design View...under the priority field typing the formula under criteria?

 

[EdFred]

As a new field in the Query

Field
PriorityFormula: [Field1] + [Field2] + [Field3]...

Table: Blank
Sort: as you wish
Show: shouldn't matter if checked or unchecked
Criteria: Blank

However, I think the problem is attempting to input data to two tables in one query. You said you are entering records IN the query?

 

[aimless54]

Ok, thanks, I will give that a try and see what happens!

 

[aimless54]

They are inputing the information in a seperate form, but I was told that the formula itself should go under the query. I have tried it several different ways and nothing seems to do the trick! Will give what you wrote a try and see

 

[neileg]

However, I think the problem is attempting to input data to two tables in one query. You said you are entering records IN the query?

What's wrong with this? Sure you should enter data in a form, not a query, but you can do it in the query if you insist.

 

[Dennisk]

I suspect our poster is confusing calculated fields in queries and reports, as ther has been a number of posts on the subject recently.

To recap,
don't store the result of a calculation in a table, you only need to calculate when it is required for viewing be it in a query a form or a report.

 

[EdFred]

What's wrong with this? Sure you should enter data in a form, not a query, but you can do it in the query if you insist.

Well, I took two tables and made a query with them, and was unable to input any data via the query with or without the formula. With a single table, I was able to input data even with the formula. The tables were not linked however. I know that in my database I sometimes ran into recordset not updatable errors depending on the tables and relationships. The problem isn't with the formula in the query, but the construction of the query itself it would seem. Not knowing the construction of the query, I can only speculate.