A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Creating a query to select 1 of 3 tables



 
 
Thread Tools Display Modes
  #1  
Old December 6th, 2009, 05:27 PM posted to microsoft.public.access.tablesdbdesign
hshepardjr
external usenet poster
 
Posts: 7
Default Creating a query to select 1 of 3 tables

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.


  #2  
Old December 6th, 2009, 06:04 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Creating a query to select 1 of 3 tables

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




"hshepardjr" wrote in message
...
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.




  #3  
Old December 6th, 2009, 07:26 PM posted to microsoft.public.access.tablesdbdesign
hshepardjr
external usenet poster
 
Posts: 7
Default Creating a query to select 1 of 3 tables

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




"hshepardjr" wrote in message
...
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.




.

  #4  
Old December 6th, 2009, 10:31 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Creating a query to select 1 of 3 tables

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" wrote in message
...
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




"hshepardjr" wrote in message
...
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.




.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.