Is this possible? Using calculation fields from one query in another query.

[mfearer94]

Alright so I'm going to make this basic since I just want to know if this is possible.

You have some fields in your main data table, i.e. Customer, Price 1, Price 2...
You then call upon that information by a select query (Query 1) and create new fields inside the query using calculations i.e. "SubTotal:[Price 1]+[Price 2]"
Now I want to call upon the field [SubTotal] by Query 2 and use that in another calculation. i.e. "Total:[SubTotal 1]+[SubTotal 2]"


I'm guessing this isn't possible since the field isn't stored anywhere, but if it is, please tell me how to call upon that field in a query and if not, how you would get around this.
Thanks in advance.

[ketbdnetbp]

mfearer94 -

If I understand your situation correctly, yes, this is possible. The second query would be based upon the first - so, all fields in the first query would be available in the second.

All the best,

Jim

[ssanfu]

Yes, you can do that. I have many queries like that.

Have you tried creating Query2 based on Query1? (Query1 is, in effect, a virtual table.)

Post back if you have problems....

[mfearer94]

Thanks for your response Jim. That would work, but I actually want to have my final query pull in many calculated fields from many different queries, about 4 to be exact.

I read something about a union query which I haven't worked at all with. Could that help? Thanks again.

[ketbdnetbp]

mfearer94 -

A Union query might be a possibility. Other forum members would be better qualified to help you with them as I have very limited experience using them.

However, if you can provide the table names where the raw data resides, the field names used in each of the calculations and the calculations themselves as well as any existing relationship between the tables, there might be other possibilities.

All the best,

Jim

[mfearer94]

Thanks again Jim,

I believe I came up with an idea even though it's not pretty at all.
I'm going to create update queries based upon each query. Then I will update a larger calculation table which then I can use in the final large calculation query.
Like I said, it's definitely not pretty, but I think it might work.

Thanks for the help though.

[June7]

If you want to UNION and not JOIN records, no need to write over to another table. A UNION query is not really that difficult. I do wonder why you have 4 separate datasets that require UNION. If these 4 queries all use the same datasource and do the same calcs, why not just 1 query to begin with? Maybe you need to build an aggregate GROUP BY (Totals) query or build a report that uses Grouping & Sorting with aggregte calcs. The report will allow display of detail records as well as summary calcs.

Post the sql statements of the 4 queries for analysis.