Calculated Fields in Queries Theory Question

[OasisUnknown]

Hello All,

I have another theory question. This time it concerns Calculated Fields in queries.

For example I have a calculated field that reads.

AuthSL1: CInt(Nz([myQuery_Crosstab].[AAAPosition],"0"))

AuthSL2: CInt(Nz([myQuery_Crosstab].[BBBPosition],"0"))

AuthSL3: CInt(Nz([myQuery_Crosstab].[CCCPosition],"0"))

AuthSL4: CInt(Nz([myQuery_Crosstab].[DDDPosition],"0"))

AuthSL5: CInt(Nz([myQuery_Crosstab].[EEEPosition],"0"))

all this is doing is grabbing the specific number from the crosstab and holding it in comparison to the department that it belongs to.

the data looks like this in the datasheet view
AuthSL1 = 10
AuthSL2 = 1
AuthSL3 = 3
AuthSL4 = 1
AuthSL5 = 0

All of the fields are required and My set up is pretty good but this is just back story information. With the data I am using it must be done this way. I cant think of any more elegant way to get the exact data I am looking for.

I then have a calculated field that rolls up all that data up into a single field to be able to be used for display purposes.

IE

AuthRollup: [AuthSL1]+[AuthSL2]+[AuthSL3]+[AuthSL4]+[AuthSL5]+[AuthSL6]

This displays the result
AuthRollup = 15

again all is good.

I do this process three more times all in the same query (again I do it this way for my specific display needs at a later point.)

I also have a Percentage tracker to keep track of some basic division of numbers

I then roll all of that up into a total summary that is again a calculated field of all this data

So I would get a display like
15/15/100.00%/1/2/14/93.33% 'This is my final summary output of all the calculated fields rolled up into one.


So after all of that back story here is my real question.


Would it be wise to make another query based off of this master sheet and use that new query for different sorting and filtering options that is manipulated by the user.

In my mind this would make sense because this query is doing so much calculation I would rather not try and filter the data and then it do the calculation and then requery and filter and calculate.


I would rather use a new query to filter and then no new calculations need to happen it just filters the already existing and locked in data. If there is updates to the source data that feeds the master query then that new information would get sent to the dummy query anyways because it is a direct representation of the master just with the expressions locked in.


I know this is long and convoluted but hopefully someone can make some sense of what I am suggestion.

 

[xenou]

I would favor writing the second query to keep it simple and straightforward for your filtering. Sometimes Access performs well this way, as a plus. But don't expect it will necessarily save you from doing calculations - the first query would evaluate when the second query is run. If you change a filter setting and run the second query again, you will probably execute the first query again as well to get the new result.