Can I use a form to change a part of a calculated field in a table? (1 Viewer)

JYeoman

Registered User.
Local time
Tomorrow, 08:44
Joined
Aug 28, 2018
Messages
17
In my tables I have some calculated fields which look like [WeeklySales]/60000
where [WeeklySales] is input via form. This calculated form returns a percentage.

There are also a couple of other areas which use this number, such as a query and a report, but 1 step at a time. The point is, these numbers might change from time to time (the 60000 part) and I am after a user friendly way of changing it in a calculated field in a table.
Is it possible to make a form where the user inputs the number and it updates a calculated field's formula?
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:44
Joined
Jan 23, 2006
Messages
15,364
I recommend you do not store the calculated fields in a table.
Use query(s) and do the calculation where and when required.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
I don't use calculated fields either. I think one would be fine in a situation like concatenating first and last names into a full name, which you'd often need. In this instance, you're trying to apply business logic would can change over time. I'd also do it in a query, and you could get the value from a one-record table joined to the transaction table. Then you provide a form for the user to maintain that value.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:44
Joined
Feb 28, 2001
Messages
27,001
Despite the fact that Microsoft makes it possible, using a calculated field in a table is possibly one of the worst things you can do to yourself because of the implications of trying to update that field, particularly if there is a JOIN involved.

By far the best method is to build a table with everything EXCEPT that computation. Then build a query that performs the computation.

Now, as to how to do this with a form to change values? Well, depends on how many of those special values you have. But if it is not TOO many, build a table with a SINGLE RECORD and each field gets named according to something about what "constant" that field holds. So you might have a field named [WklyPctDvsr] (weekly percent divisor) or you could name it George for all it matters. (As in, "Let George do it.")

Build a form that ONLY updates the conversion factors. Call the table FudgeFactors.

Now that query? Make it a special kind of JOIN. Then compute what you want as

Code:
SELECT W.WklySales, W.WklySales/FF.WklyPctDvsr AS WklyPct, etc
FROM main-table W, FudgeFactor FF
etc.

This is TECHNICALLY a Cartesian JOIN which is normally frowned upon. But if there is only one record in the fudge factor table, the deleterious effects of a Cartesian JOIN are negated. So you build a form to edit the factors in the FudgeFactor table and then build that wonky query to compute what you want with NAMED fudge factors.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
That looks like the long version of what I suggested. ;)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:44
Joined
Feb 28, 2001
Messages
27,001
Yep, on reflection, it does. I missed your last sentence, Sorry for stepping on the toes, Paul.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
No worries Doc. You gave the "how".
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:44
Joined
Jan 20, 2009
Messages
12,849
Despite the fact that Microsoft makes it possible, using a calculated field in a table is possibly one of the worst things you can do to yourself because of the implications of trying to update that field, particularly if there is a JOIN involved.

Join or not there is no way to update a calculated field.

I've never used them in Access but I do in SQL Server. I have multiple databases which need to query each other with joins between columns that have unfortunately different datatypes. The calculated column provides the alternative datatype without having to Convert in the join.

In another case I have a list of Like criteria that need to work with both Access and SQL Server. The values are maintained and tested through a linked table in an Access application but the server does the heavy lifting utilising a calculated version of the criteria adjusted accordingly.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:44
Joined
Feb 28, 2001
Messages
27,001
Join or not there is no way to update a calculated field.

Galaxiom, I wasn't referring to a JOIN as having anything to do with updating a calculated field. I was referring to any attempt to update records using a query that had a JOIN to a table with a calculated field if that field was part of the query. Because of course in that case you have no place to write back the calculated field. Since I avoid that abomination like the plague, I wasn't sure whether that would guarantee a non-updateable query. So I loosely worded it.

But that leads me to this question. If you absolutely HAD to do it, would you be able to generate some DDL statements via VBA that would update a particular computed field? I rarely diddle with DDL because dynamic data changes were EXTREME anathema in the Navy environment. Data changes had to be done during a maintenance cycle when everything was down. It was a security regulation that all schemas had to be very strictly protected, so I never got the chance to play with that kind of stuff very much.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:44
Joined
Feb 19, 2002
Messages
42,973
The point is, these numbers might change from time to time (the 60000 part) and I am after a user friendly way of changing it in a calculated field in a table.
There is nothing user friendly about changing a calculated field in a table and why are you even thinking about allowing a user to make a design change to a table?????

I also don't use calculated fields in tables. They are not as bad as some newer "features" in Access but user friendly - not even close.

If you want the user to be able to change the divisor, you need to create a table as the others have suggested. You can create a form that allows the user to change the value. If you have one of these, you probably have more so make sure to make the table support multiple values.

This is actually one case where you can use DLookup() in a query without slowing it to a crawl. The domain function will be evaluated only once and the lookup value will be applied to all rows because the lookup will use a constant where argument rather than one that is variable;

Select MyField/DLookup("LiteralValue", "tblLiterals", "LiteralName = 'George'") as CalcValue
 

JYeoman

Registered User.
Local time
Tomorrow, 08:44
Joined
Aug 28, 2018
Messages
17
Despite the fact that Microsoft makes it possible, using a calculated field in a table is possibly one of the worst things you can do to yourself because of the implications of trying to update that field, particularly if there is a JOIN involved.

By far the best method is to build a table with everything EXCEPT that computation. Then build a query that performs the computation.

Now, as to how to do this with a form to change values? Well, depends on how many of those special values you have. But if it is not TOO many, build a table with a SINGLE RECORD and each field gets named according to something about what "constant" that field holds. So you might have a field named [WklyPctDvsr] (weekly percent divisor) or you could name it George for all it matters. (As in, "Let George do it.")

Build a form that ONLY updates the conversion factors. Call the table FudgeFactors.

Now that query? Make it a special kind of JOIN. Then compute what you want as

Code:
SELECT W.WklySales, W.WklySales/FF.WklyPctDvsr AS WklyPct, etc
FROM main-table W, FudgeFactor FF
etc.

This is TECHNICALLY a Cartesian JOIN which is normally frowned upon. But if there is only one record in the fudge factor table, the deleterious effects of a Cartesian JOIN are negated. So you build a form to edit the factors in the FudgeFactor table and then build that wonky query to compute what you want with NAMED fudge factors.

The reason I did it the way I did, was because for some reason the calculations in queries would error for me.
For example:
Code:
 [AllData].[Hamilton_Residential]+[AllData].[Hamilton_Commercial] AS Hamilton_Sum
In my SQL returns the error "Your query does not include the specified expression "Hanmilton_Residential" as part of an aggregate function."

Whereas in another query I built, the following works fine:
Code:
 [AllData].[Napier_TotalSales]/[Last_Year_tbl].[Napier_TotalSales] AS Expr5

The only difference being '/' instead of '+'



EDIT:
upon further exploration, I discovered that I simply cannot add any other fields to this query. Here is what I have, causing the error:
Code:
SELECT Max(AllData.[Week Number]) AS WeekNumber, AllData.Hamilton_Residential
FROM AllData
WHERE (((AllData.Total_Sales)<>0));

When I didn't have the "AllData.Hamilton_Residential" in there it works fine, showing the highest "Week Number" value where the corresponding fields are not empty
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:44
Joined
Jan 20, 2009
Messages
12,849
The reason I did it the way I did, was because for some reason the calculations in queries would error for me.
For example:
Code:
 [AllData].[Hamilton_Residential]+[AllData].[Hamilton_Commercial] AS Hamilton_Sum
In my SQL returns the error "Your query does not include the specified expression "Hanmilton_Residential" as part of an aggregate function."

Because you have an aggregate somewhere in the Select list and have not included the field derived by the calculation in the Group By.

Whereas in another query I built, the following works fine:
Code:
 [AllData].[Napier_TotalSales]/[Last_Year_tbl].[Napier_TotalSales] AS Expr5
The only difference being '/' instead of '+'

Nothing to do with the different calculation. There will be is no aggregate in the Select of that query.
 

JYeoman

Registered User.
Local time
Tomorrow, 08:44
Joined
Aug 28, 2018
Messages
17
Because you have an aggregate somewhere in the Select list and have not included the field derived by the calculation in the Group By.



Nothing to do with the different calculation. There will be is no aggregate in the Select of that query.

I just edited my post (you were very quick to reply :D)

EDIT:
upon further exploration, I discovered that I simply cannot add any other fields to this query. Here is what I have, causing the error:
Code:
SELECT Max(AllData.[Week Number]) AS WeekNumber, AllData.Hamilton_Residential
FROM AllData
WHERE (((AllData.Total_Sales)<>0));
When I didn't have the "AllData.Hamilton_Residential" in there it works fine, showing the highest "Week Number" value where the corresponding fields are not empty
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Try

SELECT Max(AllData.[Week Number]) AS WeekNumber, AllData.Hamilton_Residential
FROM AllData
WHERE AllData.Total_Sales<>0
GROUP BY AllData.Hamilton_Residential
 

JYeoman

Registered User.
Local time
Tomorrow, 08:44
Joined
Aug 28, 2018
Messages
17
Try

SELECT Max(AllData.[Week Number]) AS WeekNumber, AllData.Hamilton_Residential
FROM AllData
WHERE AllData.Total_Sales<>0
GROUP BY AllData.Hamilton_Residential

This now lists all records in ascending order of Hamilton_Residential values, instead of only the record with highest [Week Number] value (autonumber)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Then try

SELECT Max(AllData.[Week Number]) AS WeekNumber
FROM AllData
WHERE AllData.Total_Sales<>0
 

JYeoman

Registered User.
Local time
Tomorrow, 08:44
Joined
Aug 28, 2018
Messages
17
Then try

SELECT Max(AllData.[Week Number]) AS WeekNumber
FROM AllData
WHERE AllData.Total_Sales<>0

Hi, already had that part working - but now I can't add any other fields.

Basically what I am trying to do here is eliminate possibility for user error. In my current query It asks the user to put in the week number, then it returns only the record (about 20 fields) with that week number. Now I am trying to make it so that when the query is run it simply brings up the latest records with filled in blanks.

So I have the Query so far with only the one field - Week Number. This shows the latest record except for the ones that are unfilled.
However, when I try to select other fields too it gives me the aggregate error
 

JYeoman

Registered User.
Local time
Tomorrow, 08:44
Joined
Aug 28, 2018
Messages
17
I've got a half bottle of wine in me, so I probably shouldn't be posting. This will be my last try until tomorrow:

http://www.baldyweb.com/LastValue.htm

Thanks for that link - I used your info on joining another query onto another one using INNER JOIN.

I kept the first query with only the
Code:
SELECT Max(AllData.[Week Number]) AS WeekNumber
FROM AllData
WHERE (((AllData.Total_Sales)<>0));

and then used in a second query
Code:
SELECT MaxWeekNumber.WeekNumber, AllData.Hamilton_Residential, AllData.Hamilton_Commercial
FROM AllData INNER JOIN MaxWeekNumber ON AllData.[Week Number] = MaxWeekNumber.WeekNumber;
etc etc for the extra fields
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Glad it worked for you.
 

Users who are viewing this thread

Top Bottom