Looping Through Make-Table Query

[Delores]

I have been running make-table queries to create 10 tables (one for
each unit) that are exported to Excel spreadsheets. Because of my
limited knowledge with code, I've created 10 separate queries to do the
job and then run a macro to do the exporting. I know there must be a
way to generate the 10 tables with only one query and I think it
probably involves looping(?) through the query. I've read the previous
postings and anytime I try to apply the examples to my database I
cannot get it to work. I'm wondering if someone could explain how to do
this in a simple way. Here is the SQL for qryPainControlTables:

SELECT Units.Units, FewMoreQuestions.WorstPain,
FewMoreQuestions.PainControlled, Count(FewMoreQuestions.FMQIDCode) AS
CountOfFMQIDCode
FROM Units RIGHT JOIN (Questionnaire LEFT JOIN FewMoreQuestions ON
Questionnaire.[ID Code] = FewMoreQuestions.SurveyIDCode) ON
Units.Number = Questionnaire.Unit
WHERE (((Questionnaire.[Survey Date]) Between
[Forms]![frmDates]![FirstDate] And [Forms]![frmDates]![LastDate]) AND
((FewMoreQuestions.WorstPain) Is Not Null And
(FewMoreQuestions.WorstPain)<>0) AND ((FewMoreQuestions.PainControlled)
Is Not Null And (FewMoreQuestions.PainControlled)<>0))
GROUP BY Units.Units, FewMoreQuestions.WorstPain,
FewMoreQuestions.PainControlled;

[Linda Burnside]

Delores,

I'm assuming that you are making 10 different excel spreadsheets depending
upon some answer(s) or data that resides in your original query? If the 10
tables that you are creating are used only to export to Excel spreadsheets
and for no other purpose, you don't need to create the 10 tables. Tell me
more about the need for 10 separate "tables". What distinguishes one from
another?

Linda

"Delores" <de********@mts.net> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

I have been running make-table queries to create 10 tables (one for
each unit) that are exported to Excel spreadsheets. Because of my
limited knowledge with code, I've created 10 separate queries to do the
job and then run a macro to do the exporting. I know there must be a
way to generate the 10 tables with only one query and I think it
probably involves looping(?) through the query. I've read the previous
postings and anytime I try to apply the examples to my database I
cannot get it to work. I'm wondering if someone could explain how to do
this in a simple way. Here is the SQL for qryPainControlTables:

SELECT Units.Units, FewMoreQuestions.WorstPain,
FewMoreQuestions.PainControlled, Count(FewMoreQuestions.FMQIDCode) AS
CountOfFMQIDCode
FROM Units RIGHT JOIN (Questionnaire LEFT JOIN FewMoreQuestions ON
Questionnaire.[ID Code] = FewMoreQuestions.SurveyIDCode) ON
Units.Number = Questionnaire.Unit
WHERE (((Questionnaire.[Survey Date]) Between
[Forms]![frmDates]![FirstDate] And [Forms]![frmDates]![LastDate]) AND
((FewMoreQuestions.WorstPain) Is Not Null And
(FewMoreQuestions.WorstPain)<>0) AND ((FewMoreQuestions.PainControlled)
Is Not Null And (FewMoreQuestions.PainControlled)<>0))
GROUP BY Units.Units, FewMoreQuestions.WorstPain,
FewMoreQuestions.PainControlled;

[Delores]

Each table contains the survey results for a separate hospital unit.
Each unit has it's own excel spreadsheet in which graphs are
automatically updated when new data is exported.