I have an Access database that uses three primary tables that are linked to
an SQL database. The tables have millions of records and cannot be be joined
using a union query due to the inefficiency of running queries across the
network. I'm needing to create a query that will call the correct table when
criteria is selected in a form using a drop down menu. I'm thinking an "if
else" statement may work but not sure if this can be used in Access. An
example could be:

If [Forms]![Pick]![segment] = "WIS"
EXEC [WIS query]
ELSE
If [Forms]![Pick]![segment] = "Count"
EXEC [Count query]
ELSE
If [Forms]![Pick]![segment] = "Summer"
EXEC [Summer query]
END

Can an "if else" statement be used in Access?

Another approach I've tried was creating created a macro to select the
appropriate table/query. I've successfully done this but do not know how to
use the results of a macro in a new query. Is this possible? Any help would
be appreciated.

You don't say whether you want to use the query for the recordsource of a
form or report or as the rowsource of a combobox. Regardless, the solution
is simiilar. Form: Put the following code in the open event
If [Forms]![Pick]![segment] = "WIS"
Me.Recordsource = "NameOfWisQuery"
ELSEIf [Forms]![Pick]![segment] = "Count"
Me.Recordsource = "NameOfCountQuery"
ELSEIf [Forms]![Pick]![segment] = "Summer"
Me.Recordsource = "NameOfSummerQuery"
END

Steve
(E-Mail Removed)



"hshepardjr" <(E-Mail Removed)> wrote in message
news:9C640EB6-5531-4E36-BCED-(E-Mail Removed)...
>I have an Access database that uses three primary tables that are linked to
> an SQL database. The tables have millions of records and cannot be be
> joined
> using a union query due to the inefficiency of running queries across the
> network. I'm needing to create a query that will call the correct table
> when
> criteria is selected in a form using a drop down menu. I'm thinking an
> "if
> else" statement may work but not sure if this can be used in Access. An
> example could be:
>
> If [Forms]![Pick]![segment] = "WIS"
> EXEC [WIS query]
> ELSE
> If [Forms]![Pick]![segment] = "Count"
> EXEC [Count query]
> ELSE
> If [Forms]![Pick]![segment] = "Summer"
> EXEC [Summer query]
> END
>
> Can an "if else" statement be used in Access?
>
> Another approach I've tried was creating created a macro to select the
> appropriate table/query. I've successfully done this but do not know how
> to
> use the results of a macro in a new query. Is this possible? Any help
> would
> be appreciated.
>
>

Thanks for the reply. I use the form as the field selection filter for my
queries. The database originally had one table with all the information.
Now that I'm going to three tables, I'm looking to find a way to query using
a selected table. If I'm following your response correctly, it sounds like
the Form is used as the data source, correct? How would I call the data in a
query?

"Steve" wrote:

> You don't say whether you want to use the query for the recordsource of a
> form or report or as the rowsource of a combobox. Regardless, the solution
> is simiilar. Form: Put the following code in the open event
> If [Forms]![Pick]![segment] = "WIS"
> Me.Recordsource = "NameOfWisQuery"
> ELSEIf [Forms]![Pick]![segment] = "Count"
> Me.Recordsource = "NameOfCountQuery"
> ELSEIf [Forms]![Pick]![segment] = "Summer"
> Me.Recordsource = "NameOfSummerQuery"
> END
>
> Steve
> (E-Mail Removed)
>
>
>
> "hshepardjr" <(E-Mail Removed)> wrote in message
> news:9C640EB6-5531-4E36-BCED-(E-Mail Removed)...
> >I have an Access database that uses three primary tables that are linked to
> > an SQL database. The tables have millions of records and cannot be be
> > joined
> > using a union query due to the inefficiency of running queries across the
> > network. I'm needing to create a query that will call the correct table
> > when
> > criteria is selected in a form using a drop down menu. I'm thinking an
> > "if
> > else" statement may work but not sure if this can be used in Access. An
> > example could be:
> >
> > If [Forms]![Pick]![segment] = "WIS"
> > EXEC [WIS query]
> > ELSE
> > If [Forms]![Pick]![segment] = "Count"
> > EXEC [Count query]
> > ELSE
> > If [Forms]![Pick]![segment] = "Summer"
> > EXEC [Summer query]
> > END
> >
> > Can an "if else" statement be used in Access?
> >
> > Another approach I've tried was creating created a macro to select the
> > appropriate table/query. I've successfully done this but do not know how
> > to
> > use the results of a macro in a new query. Is this possible? Any help
> > would
> > be appreciated.
> >
> >
>
>
> .
>

No, you need three queries; a WIS query, a Count query and a Summer query.
Make the Pick form a pop-up form and use the Segment field on the Pick form
to select the query to be the recordsource of your main form.

Steve


"hshepardjr" <(E-Mail Removed)> wrote in message
news:57A0653A-AD87-4B77-86ED-(E-Mail Removed)...
> Thanks for the reply. I use the form as the field selection filter for my
> queries. The database originally had one table with all the information.
> Now that I'm going to three tables, I'm looking to find a way to query
> using
> a selected table. If I'm following your response correctly, it sounds
> like
> the Form is used as the data source, correct? How would I call the data in
> a
> query?
>
> "Steve" wrote:
>
>> You don't say whether you want to use the query for the recordsource of a
>> form or report or as the rowsource of a combobox. Regardless, the
>> solution
>> is simiilar. Form: Put the following code in the open event
>> If [Forms]![Pick]![segment] = "WIS"
>> Me.Recordsource = "NameOfWisQuery"
>> ELSEIf [Forms]![Pick]![segment] = "Count"
>> Me.Recordsource = "NameOfCountQuery"
>> ELSEIf [Forms]![Pick]![segment] = "Summer"
>> Me.Recordsource = "NameOfSummerQuery"
>> END
>>
>> Steve
>> (E-Mail Removed)
>>
>>
>>
>> "hshepardjr" <(E-Mail Removed)> wrote in message
>> news:9C640EB6-5531-4E36-BCED-(E-Mail Removed)...
>> >I have an Access database that uses three primary tables that are linked
>> >to
>> > an SQL database. The tables have millions of records and cannot be be
>> > joined
>> > using a union query due to the inefficiency of running queries across
>> > the
>> > network. I'm needing to create a query that will call the correct
>> > table
>> > when
>> > criteria is selected in a form using a drop down menu. I'm thinking an
>> > "if
>> > else" statement may work but not sure if this can be used in Access.
>> > An
>> > example could be:
>> >
>> > If [Forms]![Pick]![segment] = "WIS"
>> > EXEC [WIS query]
>> > ELSE
>> > If [Forms]![Pick]![segment] = "Count"
>> > EXEC [Count query]
>> > ELSE
>> > If [Forms]![Pick]![segment] = "Summer"
>> > EXEC [Summer query]
>> > END
>> >
>> > Can an "if else" statement be used in Access?
>> >
>> > Another approach I've tried was creating created a macro to select the
>> > appropriate table/query. I've successfully done this but do not know
>> > how
>> > to
>> > use the results of a macro in a new query. Is this possible? Any help
>> > would
>> > be appreciated.
>> >
>> >
>>
>>
>> .
>>