Using Several Queries in a Calculated Field

[Muraii]

Hi there,

A bit of background:

I have 33 simple select queries, of the form

recruit1
recruit2
recruit3

 

[Duane Hookom]

Without a better understanding of your table structure, data, and what you
are attempting to do I can only suggest there may be a method of
accomplishing this all in one query. You should have a tipcode table that
stores a category value of "Dialer Issues" for records with values
204,207,208,209,210,221,225,234. I think you are making this overly complex
with your expressions. This should all be data driven possibly using a
simple crosstab query.

--
Duane Hookom
MS Access MVP
--

Hi there,

A bit of background:

I have 33 simple select queries, of the form

recruit1
recruit2
recruit3
.
.
.
recruit31
recruit32
recruit33

Each query is of a similar form to

SELECT [tipcodes 2003].[event name], Sum([tipcodes 2003]![tipcode
frequency] AS [Dialer issues (Recruit)]
FROM [tipcodes 2003]
WHERE ((([tipcodes 2003].[tipcode]) In
(204,207,208,209,210,221,225,234)))
GROUP BY [tipcodes 2003].[event name];

I have created one metaquery like

SELECT recruit1.[event name],
[recruit1]![Initial Refusals]+
[recruit2]![Business phone/fax/modem (Recruit)]+
[recruit3]![No answer (Recruit)]+
[recruit4]![Disconnected/out of service (Recruit)]+
[recruit5]![Busy (Recruit)]+
[recruit6]![Answering Machine (Recruit)]+
[recruit7]![Language barrier (Recruit)]+
[recruit8]![Callback - not returned (Recruit)]+
[recruit9]![Exit (Recruit)]+
[recruit10]![Dialer issues (Recruit)] AS [No Contact]

FROM ((((((((
recruit1
INNER JOIN recruit2 ON recruit1.[event name] = recruit2.[event name])
INNER JOIN recruit3 ON recruit1.[event name] = recruit3.[event name])
INNER JOIN recruit4 ON recruit1.[event name] = recruit4.[event name])
INNER JOIN recruit5 ON recruit1.[event name] = recruit5.[event name])
INNER JOIN recruit6 ON recruit1.[event name] = recruit6.[event name])
INNER JOIN recruit8 ON recruit1.[event name] = recruit8.[event name])
INNER JOIN recruit9 ON recruit1.[event name] = recruit9.[event name])
INNER JOIN recruit10 ON recruit1.[event name] = recruit10.[event
name])
INNER JOIN recruit7 ON recruit1.[event name] = recruit7.[event name];

...which achieves what I need.

When I extend this to summing "recruit11" through "recruit33", i.e.
summing the results of 23 queries rather than the results of 10
queries, Access becomes something less than congenial. I've chopped
the summing into two, and then five calculated fields which would then
be used in a last calculated field to achieve the sum of results from
all 23 queries. Nothing.

Actually, to conclude "nothing" may be premature. In many cases,
Access appears to crash, hanging for as many as 20 minutes (the
longest I could wait before ending it).

Is there something fundamental over which I'm stumbling? Do I need a
swift kick to the head? I'm a beginner, which is likely obvious, but
it would seem that what works for 10 should work for 23, so long as a
workaround is found for the 20-variable limit of Access's calculated
fields.

Thanks for any help.

Daniel

 

[Guest]

Daniel
Access does have limitations. It doesn't have the power of SQL Server and even that cannot do everthing. Once you reach that limit, or perhaps even before then it you don't want to grow old waiting for the execution, you need to built make table queries and then queries that rest on the tables which are made. In code you need to put something like this

docmd.setwarnings fals
docmd.openquery("qryRecruit1Make"
docmd.openquery("qryRecruit2Make"
docmd.openquery("qryRecruit3Make") ...etc. where the name of the make-table query is qryRecruit1Make. Your tables should be could "tblRecruit1" etc. Then create a monster query to pull all of the tables together for your final result.
BigManT

 

[Muraii]

Without a better understanding of your table structure, data, and what you
are attempting to do I can only suggest there may be a method of
accomplishing this all in one query.

Understood. The nature of the data and the structure I feel necessary
to emphasize is pretty complex (as I imagine is the case with most
databases). Simply put, there are three levels to the hierarchy:

(1) A tipcode records the state of the most recent dial of a phone
number, e.g. "no answer", "completed interview", etc. Each phone
number dialed can have only one tipcode at any time, though a "no
answer", upon dialing later, may become a "completed interview". In
that case, the history of that number will be erased, and we will only
ever know that it became a "completed interview".

(2) A subcategory collects a few tipcodes into one narrowly focused
context, e.g. "busy", "no answer", and "disconnected" are all forms of
the context "no contact".

(3) A category collects subcategories into a broader context, e.g.
the "no contact" and "immediate refusal" subcategories are both forms
of the context "no substantive response".

At the moment, all I care about is the category level; however, I know
I'll eventually need to tease out the finer details.

You should have a tipcode table that
stores a category value of "Dialer Issues" for records with values
204,207,208,209,210,221,225,234. I think you are making this overly complex
with your expressions. This should all be data driven possibly using a
simple crosstab query.

My approach had been to create queries at the subcategory level, and
simply compile the queries themselves into the categories. I think
now, having tried that approach, that the difficulty of this method
outstrips any efficiency I might expect. And, really, if we have the
following situation:

subcategory A = (204, 207, 208)
subcategory B = (209, 210, 221)
subcategory C = (225, 234)

union(A, B, C) = (204, 207, 208, 209, 210, 221, 225, 234) = category
ABC

I thought this might have been a naive approach, flying in the face of
the power that Access had to provide. Maybe, as you say, I should
"Keep It Simple Stupid", and keep everything a simple collection of
the tipcodes themselves.

....unless someone can see a more efficient way of going about this?

Thank you, Duane.

Daniel