If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Several Queries in a Calculated Field
Hi there,
A bit of background: I have 33 simple select queries, of the form recruit1 recruit2 recruit3 |
#2
|
|||
|
|||
Using Several Queries in a Calculated Field
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 -- "Muraii" wrote in message m... 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 |
#3
|
|||
|
|||
Using Several Queries in a Calculated Field
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 false 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 |
#4
|
|||
|
|||
Using Several Queries in a Calculated Field
"Duane Hookom" wrote in message ...
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 |
Thread Tools | |
Display Modes | |
|
|