Summing from queries within a macro

[warnett]

hey everyone,

Im making a macro which opens several queries and sums a specific column
from each query, and adds these sums together. In addition to this, added to
the sums is the number of records which have not been marked as 'cancelled'
multiplied by 2.5. The total of this is displayed in a message box

The running of my macro is as follows:
OpenQuery {Money Taken SC}
OpenQuery {Money Taken T1}
OpenQuery {Money Taken T2}
OpenQuery {Money Taken T3}
OpenQuery {Money Taken T4}
OpenQuery {Money Taken T5}
MsgBox:
=Sum([Money Taken T1]![Cost1])+Sum([Money Taken T2]![Cost2])+Sum([Money
Taken T3]![Cost3])+Sum([Money Taken T4]![Cost4])+Sum([Money Taken
T5]![Cost5])+(Count([Money Taken SC]![Order ID])*2.5)

But alas, it doesnt work. I get the error message that i need to be specific
to an object if it is not the current one. Which i am. Im using the syntax
where the query name is then followed by the field name.

Someone mentioned to me that i may need to make a report for each, then sum
it from there. Surely this need not be done? Is there a way around this?

If you know of a way round, please reply!

Thanks for your time,
Jason

 

[Steve Schapel]

Jason,

Alas, there are some misunderstandings here.

First of all, OpenQuery is normally applicable to an Action Query (e.g.
Append, Update, Delete, etc). Using OpenQuery with a Select Query, as
you are apparently doing, normally achieves nothing. A Select Query
doesn't actually *do* anything.

Second, [Money Taken T1]![Cost1] is not valid syntax to refer to a field
in a query, and means nothing to Access. Even if it was valid syntax,
you would need a way to indicate to Access which records in the query
you are referring to, so unfortunately this won't work.

It is certain that there will be an easier way to do this. Probably you
could do the calculations/sums in another query, and then simply refer
to it, and how simple this will be would depend on whether your tables
are correctly designed, but I couldn't comment more specifically without
knowing more details of your existing queries. As another option, you
could use Domain Aggregate Functions. For example, you could do a
MsgBox action in a macro with the Message Text set to...
=DSum("[Cost1]","Money Taken T1")+DSum("[Cost2]","Money Taken
T2")+DSum("[Cost3]","Money Taken T3")+DSum("[Cost4]","Money Taken
T4")+DSum("[Cost5]","Money Taken T5")+DCount("*","Money Taken SC")*2.5
This may be a bit sluggish if you have a very large number of records.
Note that this just involves a MsgBox, there would be no purpose served
by retaining the OpenQuery actions in the macro.