Multiple Parameter Queries

[Michelle]

I am using Access 97. I have a Macro that runs several
queries. Each query contains the SAME parametric query
(all the parametric prompts are exact in format). When I
run the macro it prompts the user each time for a value.
How do I synchronize them so that the user is only
prompted once. I thought that but having the parametric
queries set up the same in each query would achieve this,
but I guess I was wrong...

 

[Steve Schapel]

Michelle,

In my opinion, the best way to handle this is to abandon the idea of
Parameter Queries, and instead put some unbound controls (textbox,
combobox, option group, whatever) on a form which will be open at the
time the queries are run, and enter your query criteria values in here.
Then, in the place of the existing parameter prompts in the query
criteria, simply replace by a reference to the form control, using
syntax such as [Forms]![NameOfForm]![NameOfControl]

 

[JUAN]

Hello Steve,
I have something similar to this, so trying to understand
your suggestion. let say I have a form and I add option
buttons. NA, EU, AP, and ALL. You mention enter your query
criteria values in here. What do you mean by this? I
understand changing in the query the criteria but how in
the form.
I export queries for those for regions, so I don't want
user to type in NA for every query.

Please advise, would appreciate the help.

Thanks

JUAN

-----Original Message-----
Michelle,

In my opinion, the best way to handle this is to abandon the idea of
Parameter Queries, and instead put some unbound controls (textbox,
combobox, option group, whatever) on a form which will be open at the
time the queries are run, and enter your query criteria values in here.
Then, in the place of the existing parameter prompts in the query
criteria, simply replace by a reference to the form control, using
syntax such as [Forms]![NameOfForm]![NameOfControl]

--
Steve Schapel, Microsoft Access MVP

I am using Access 97. I have a Macro that runs several
queries. Each query contains the SAME parametric query
(all the parametric prompts are exact in format). When I
run the macro it prompts the user each time for a value.
How do I synchronize them so that the user is only
prompted once. I thought that but having the parametric
queries set up the same in each query would achieve this,
but I guess I was wrong...

.

 

[Steve Schapel]

Juan,

It depends exactly what you mean. If you mean you want the user to be
able to select NA *or* EU *or* AP *or* All, then your 4 option buttons
can be part of an Option Group on your form. But if you need to allow
for the possibility, for example, of NA *and* AP but *not* EU, or some
such combination, then the option buttons (or, preferably, checkboxes)
will need to be stand-alone. Either way, they can't directly return the
text of your criteria, so you will have to do some sort of conversion or
reference. So, let's say it is the first scenario, so the 4 option
buttons are in an option group, with the values 1, 2, 3, and 4. Ok, one
way to handle this is to make a simple 2 field 3 records table, with one
column containing 1, 2, 3 and the other containing NA, EU, AP. Then,
you can add this table to your query, join it on the Region field to
your existing table, and then apply your criteria to the RegionNumber
field of the lookup table. The criteria will look something like this...
[Forms]![NameOfForm]![YourOptionGroup] Or
[Forms]![NameOfForm]![YourOptionGroup]=4
Another approach is to make a calculated field in your query, based on
the existing region field, like this...
RegionNumber:
Switch([Region]="NA",1,[Region]="EU",2,[Region]="AP",3,True,4)
and then put your criteria [Forms]![NameOfForm]![YourOptionGroup] into
this column of the query.

 

[Juan]

Hello Steve,
Sorry should have probably explain my situation better.
Here's what I have.
I have 5 queries that I export to excel for each
Organization, (A110,A132 as one), B110, C110 and maybe for
all combined. So instead of me creating 20 queries I did a
parameter Like [Enter A* or B* or C* or * for all]
I created a macro with Macro names example,
AMERICA
TRANSFER SHEETS
CLOSE MACRO
EUROPE
TRANSFER SHEETS
CLOSE MACRO Etc.
Then Created a form with a button for each organization,
so if I want Europe, I would click the europe button and I
will get the parameter promp 5 times,since those how many
queries it contains.
I want to be able to just get prompt once. I have other
macros with exporting 12 queries for each period so don't
want to sit there typing 12 times the right region.
Hope I'm explaining this better. I'm trying to find a way
to not get prompt that many times.
Please advise if its possible or not.
Thanks for your time I really appreciated alot.
Juan

-----Original Message-----
Juan,

It depends exactly what you mean. If you mean you want the user to be
able to select NA *or* EU *or* AP *or* All, then your 4 option buttons
can be part of an Option Group on your form. But if you need to allow
for the possibility, for example, of NA *and* AP but *not* EU, or some
such combination, then the option buttons (or, preferably, checkboxes)
will need to be stand-alone. Either way, they can't directly return the
text of your criteria, so you will have to do some sort of conversion or
reference. So, let's say it is the first scenario, so the 4 option
buttons are in an option group, with the values 1, 2, 3, and 4. Ok, one
way to handle this is to make a simple 2 field 3 records table, with one
column containing 1, 2, 3 and the other containing NA, EU, AP. Then,
you can add this table to your query, join it on the Region field to
your existing table, and then apply your criteria to the RegionNumber
field of the lookup table. The criteria will look something like this...
[Forms]![NameOfForm]![YourOptionGroup] Or
[Forms]![NameOfForm]![YourOptionGroup]=4
Another approach is to make a calculated field in your query, based on
the existing region field, like this...
RegionNumber:
Switch([Region]="NA",1,[Region]="EU",2,[Region] ="AP",3,True,4)
and then put your criteria [Forms]![NameOfForm]! [YourOptionGroup] into
this column of the query.

--
Steve Schapel, Microsoft Access MVP

Hello Steve,
I have something similar to this, so trying to understand
your suggestion. let say I have a form and I add option
buttons. NA, EU, AP, and ALL. You mention enter your query
criteria values in here. What do you mean by this? I
understand changing in the query the criteria but how in
the form.
I export queries for those for regions, so I don't want
user to type in NA for every query.

Please advise, would appreciate the help.

Thanks

JUAN

.

 

[Steve Schapel]

Juan,

Yes, it is exactly what I put in my earlier reply... you need to use a
reference to a control (option group, combobox) in the criteria of the
query, instead of the parameter prompt.

 

[Guest]

Steve,
slowly but surely, I think I'm getting somewhere. But when
I run the query puttin an value I get the same. Here's
what I did/have:
SALESORG(new table) blank form with optionGroup
SALES_ORG OPTION AMERICAS VALUE 1
B110 1 EUROPE Value 2
A110 2 ASIA Value 3
A330 2 ALL Value 4
C110 3

Query
table1 SALES_ORG
SALES_ORG SALES_ORG Joint here, include all records from
table 1 and only those records

-----Original Message-----
Juan,

Yes, it is exactly what I put in my earlier reply... you need to use a
reference to a control (option group, combobox) in the criteria of the
query, instead of the parameter prompt.

--
Steve Schapel, Microsoft Access MVP

Hello Steve,
Sorry should have probably explain my situation better.
Here's what I have.
I have 5 queries that I export to excel for each
Organization, (A110,A132 as one), B110, C110 and maybe for
all combined. So instead of me creating 20 queries I did a
parameter Like [Enter A* or B* or C* or * for all]
I created a macro with Macro names example,
AMERICA
TRANSFER SHEETS
CLOSE MACRO
EUROPE
TRANSFER SHEETS
CLOSE MACRO Etc.
Then Created a form with a button for each organization,
so if I want Europe, I would click the europe button and I
will get the parameter promp 5 times,since those how many
queries it contains.
I want to be able to just get prompt once. I have other
macros with exporting 12 queries for each period so don't
want to sit there typing 12 times the right region.
Hope I'm explaining this better. I'm trying to find a way
to not get prompt that many times.
Please advise if its possible or not.
Thanks for your time I really appreciated alot.
Juan

.

 

[juan]

Steve, sorry last post, sent it without finishing.
slowly but surely, I think I'm getting somewhere. But when
I run the query puttin an value I get the same. Here's
what I did/have:
SALESORG(new table) blank form with optionGroup
SALES_ORG OPTION AMERICAS VALUE 1
B110 1 EUROPE Value 2
A110 2 ASIA Value 3
A330 2 ALL Value 4
C110 3
Field type number single for Option field.
Query
table1 SALES_ORG
SALES_ORG SALES_ORG Joint here, include all records from
table 1 and only those records from Sales_org are equal
Field1, field2 OPTION
table1 table2 SALES_ORG
WHERE
[Forms]![Form1]![Frame0]=3
I would expect to get ASIA, if in my form I check ASIA.
But seems to get for all regions. If I put 1 or 2 get all
data. So I'm missing something. i'm not sure if in the
optiongroup under control source I have to put something
or not. Try different things but still not able to get
specific data.
Please advise if you see anything wrong in my setup.
Thanks for all your help.
Juan

-----Original Message-----
Juan,

Yes, it is exactly what I put in my earlier reply... you need to use a
reference to a control (option group, combobox) in the criteria of the
query, instead of the parameter prompt.

--
Steve Schapel, Microsoft Access MVP

Hello Steve,
Sorry should have probably explain my situation better.
Here's what I have.
I have 5 queries that I export to excel for each
Organization, (A110,A132 as one), B110, C110 and maybe for
all combined. So instead of me creating 20 queries I did a
parameter Like [Enter A* or B* or C* or * for all]
I created a macro with Macro names example,
AMERICA
TRANSFER SHEETS
CLOSE MACRO
EUROPE
TRANSFER SHEETS
CLOSE MACRO Etc.
Then Created a form with a button for each organization,
so if I want Europe, I would click the europe button and I
will get the parameter promp 5 times,since those how many
queries it contains.
I want to be able to just get prompt once. I have other
macros with exporting 12 queries for each period so don't
want to sit there typing 12 times the right region.
Hope I'm explaining this better. I'm trying to find a way
to not get prompt that many times.
Please advise if its possible or not.
Thanks for your time I really appreciated alot.
Juan

.

 

[Steve Schapel]

Juan,

I think it should be:
WHERE [Option] = [Forms]![Form1]![Frame0]

If you still can't get it working, please post full SQL view of the
query, as I'm not quite clear from your description.

 

[Juan]

Morning Steve,
I got it to work for each region, but need to know how to
run for combined regions. I know I would have to add
option 4 in my SALES ORG table since I have in my form ALL
option value 4.
SALES_ORG OPTION AMERICAS VALUE 1
B110 1 EUROPE Value 2
A110 2 ASIA Value 3
A330 2 ALL Value 4
C110 3

So when I click on ALL, it should run b110+A110+A330+C1110
Please advise
Thanks,
Juan

-----Original Message-----
Juan,

I think it should be:
WHERE [Option] = [Forms]![Form1]![Frame0]

If you still can't get it working, please post full SQL view of the
query, as I'm not quite clear from your description.

--
Steve Schapel, Microsoft Access MVP

Steve, sorry last post, sent it without finishing.
slowly but surely, I think I'm getting somewhere. But when
I run the query puttin an value I get the same. Here's
what I did/have:
SALESORG(new table) blank form with optionGroup
SALES_ORG OPTION AMERICAS VALUE 1
B110 1 EUROPE Value 2
A110 2 ASIA Value 3
A330 2 ALL Value 4
C110 3
Field type number single for Option field.
Query
table1 SALES_ORG
SALES_ORG SALES_ORG Joint here, include all records from
table 1 and only those records from Sales_org are equal
Field1, field2 OPTION
table1 table2 SALES_ORG
WHERE
[Forms]![Form1]![Frame0]=3
I would expect to get ASIA, if in my form I check ASIA.
But seems to get for all regions. If I put 1 or 2 get all
data. So I'm missing something. i'm not sure if in the
optiongroup under control source I have to put something
or not. Try different things but still not able to get
specific data.
Please advise if you see anything wrong in my setup.
Thanks for all your help.
Juan

.

 

[JUAN]

Steve,
In my sales org table I added
B110 4
A110 4
A330 4
C110 4
And seems to work, so now if I want ALL, it works. I
really want to thank you so so so much for your help. This
will save me time and its more efficient. Couldn't have
done it without your excellent help.
Now, I just need to do my macro and I think I'm all set.
Once again, THANKKKKKKKSSSSSSSSSSSSS ALOTTTTTTTTTTTT.
JUAN

-----Original Message-----
Juan,

I think it should be:
WHERE [Option] = [Forms]![Form1]![Frame0]

If you still can't get it working, please post full SQL view of the
query, as I'm not quite clear from your description.

--
Steve Schapel, Microsoft Access MVP

Steve, sorry last post, sent it without finishing.
slowly but surely, I think I'm getting somewhere. But when
I run the query puttin an value I get the same. Here's
what I did/have:
SALESORG(new table) blank form with optionGroup
SALES_ORG OPTION AMERICAS VALUE 1
B110 1 EUROPE Value 2
A110 2 ASIA Value 3
A330 2 ALL Value 4
C110 3
Field type number single for Option field.
Query
table1 SALES_ORG
SALES_ORG SALES_ORG Joint here, include all records from
table 1 and only those records from Sales_org are equal
Field1, field2 OPTION
table1 table2 SALES_ORG
WHERE
[Forms]![Form1]![Frame0]=3
I would expect to get ASIA, if in my form I check ASIA.
But seems to get for all regions. If I put 1 or 2 get all
data. So I'm missing something. i'm not sure if in the
optiongroup under control source I have to put something
or not. Try different things but still not able to get
specific data.
Please advise if you see anything wrong in my setup.
Thanks for all your help.
Juan

.

 

[Steve Schapel]

Juan,

I am pleased to hear that you have made progress.

An alternative to what you have done, which I actually already suggested
in my earlier reply, is instead of the addition of the 4 records in the
sales org table, you could use criteria in your query like this...
WHERE (([Option]=[Forms]![Form1]![Frame0]) Or
([Forms]![Form1]![Frame0]=4))

--
Steve Schapel, Microsoft Access MVP

Steve,
In my sales org table I added
B110 4
A110 4
A330 4
C110 4
And seems to work, so now if I want ALL, it works. I
really want to thank you so so so much for your help. This
will save me time and its more efficient. Couldn't have
done it without your excellent help.
Now, I just need to do my macro and I think I'm all set.
Once again, THANKKKKKKKSSSSSSSSSSSSS ALOTTTTTTTTTTTT.
JUAN

-----Original Message-----
Juan,

I think it should be:
WHERE [Option] = [Forms]![Form1]![Frame0]

If you still can't get it working, please post full SQL

view of the

query, as I'm not quite clear from your description.

--
Steve Schapel, Microsoft Access MVP

Steve, sorry last post, sent it without finishing.
slowly but surely, I think I'm getting somewhere. But

when

I run the query puttin an value I get the same. Here's
what I did/have:
SALESORG(new table) blank form with optionGroup
SALES_ORG OPTION AMERICAS VALUE 1
B110 1 EUROPE Value 2
A110 2 ASIA Value 3
A330 2 ALL Value 4
C110 3
Field type number single for Option field.
Query
table1 SALES_ORG
SALES_ORG SALES_ORG Joint here, include all records

from

table 1 and only those records from Sales_org are equal
Field1, field2 OPTION
table1 table2 SALES_ORG
WHERE
[Forms]![Form1]![Frame0]=3
I would expect to get ASIA, if in my form I check ASIA.
But seems to get for all regions. If I put 1 or 2 get

all

data. So I'm missing something. i'm not sure if in the
optiongroup under control source I have to put

something

or not. Try different things but still not able to get
specific data.
Please advise if you see anything wrong in my setup.
Thanks for all your help.
Juan

.