query based on query

[fanfan2]

I'd like to make a query based on the other 2 queries.

One of the original queries has a calculated field. When I make the 2nd query and select that field, the field seems to be "dynamic", the result query is different from if I make the same query based on 2 tables with the same data. How can I still make a query based on queries but make the field "static" just like they were tables?

Thanks.

 

[The_Doc_Man]

I'm a bit confused here, but I'll take a shot.

Basing a query on another query is not only valid but often useful. But you have to remember that a calculated field is often, by its very nature, quite dynamic. SQL Aggregates, for example, give you the value associated with the aggregate AT THE EXACT TIME YOU OPEN THE QUERY. If the underlying table changes tomorrow, the value for the aggregate SHOULD change. Therefore, I'm going to tell you that for at least some cases, you are reporting correct behavior that SHOULD NOT be changed.

Where I am not clear is your statement that you see a different result from doing the same query on 2 tables with the same data.

If you are merely saying that when you query tables directly the values are not dynamic, again I would agree. Once you store data in a table, most (but not all) applications would want that data to stay put as historical reference material. So again I don't see any incorrect behavior.

Therefore, what we need to understand is WHY you think you should NOT see dynamic data from a formula-based query, and why you bothered to use the intermediate query when underlying tables were available. Is the formula one that does not involve an SQL or Domain Aggregate? (If so, then what is the formula, at least in general terms?)

OR, were you merely saying that if you stored the same data as is found in the query into a table and then did your second query, the behavior is different (non-dynamic)? In this case, again that is expected behavior since in effect you are taking a snapshot of the tables to do what I described.

Please don't take this wrong, I'm not discounting that you see a problem, but from your description it is hard to understand exactly what is wrong. If my discussion above doesn't bring you a new understanding, then YOU need to post at least a FEW details of the problem and why you think it is wrong behavior.

 

[fanfan2]

thanks The Doc Man - I really appreciate your help.

the attached is the access table format I used. query2 is based on a table and a query which has a calculated field 'p-f'. query3 is based on 2 tables with the same contents. the "p-f" field in query2 and query3 are different.

thanks very much.

 

[The_Doc_Man]

I am not able to download zip files because I work on a government computer and my firewall blocks the attachments.

What is the formula for p-f? Explain in English where it originates. (Sometimes the mere act of explaining the problem causes the light to go off in your head, so don't consider it a waste of time.)