Selecting Table based on user input

[ace]

I have a query that works fine if I know which table to use for my query.
However, in reality the table I will use is based on the user selection in a
combo box in a form.

So,

If selection is A then use table A
If selection is B then use table B etc.

How do I pass this info to a query? How do I tell query to use A.[First
Name] or B.[First Name]

Thanks,
AC Erdal

 

[John W. Vinson]

I have a query that works fine if I know which table to use for my query.
However, in reality the table I will use is based on the user selection in a
combo box in a form.

So,

If selection is A then use table A
If selection is B then use table B etc.

How do I pass this info to a query? How do I tell query to use A.[First
Name] or B.[First Name]

Thanks,
AC Erdal

To do this you will need to construct the SQL string in VBA code,
incorporating the tablename.

The need to do so, though, VERY strongly suggests that you're making an error
in your database design! Having multiple identically structured tables with
different names is *a very bad idea* and is not normalized. Could you consider
instead incorporating all of these tables into one with an additional field
containing what's now the tablename?

 

[ace]

John,

I already have an SQL string in VBA for one table. See below for the code. I
am hoping to find out if and how can I use variables, etc. to select a
different table for the SQL. For example, I can use "dim variablename as
string" and assign a table name to this variable based on the selection from
a combo box. What I am not sure is how to incorporate this variable into the
SQL! When I use "dim" is that a global variable so that I can use it outsite
of a sub? Otherwise, how can I pass it to the SQL? Can I use
"variablename.[First Name] to selec the table? Let me know your feedback
regardless of if I endup combining all the tables into one.

I agree with you about combining the tables into one. There are two issues.
One is each table, even though a contact table, has very different fields.
For example, for one contact type I need to track their spouse
name/phone/email, etc. versus another contact type where I need to track
their assistant's name/phone/email. etc. There are also other differences.
So, it will not be an efficient single table with many empty fields.
Nevertheless I am considering combining them. However, I am hoping that there
is a easy way to combine the tables into one, rather than creating a new one
with all the fields from scratch! Is there a way? Like append or merge one
table to another!

Thanks,
AC Erdal
___ SQL string

SELECT [Last Name] & "," & [First Name] AS Expr1, Sellers.[First Name],
Properties.[Property Address] FROM Properties INNER JOIN (Sellers INNER JOIN
[Transaction] ON Sellers.[ID-Sellers]=Transaction.[ID-Sellers]) ON
(Properties.[ID-Properties]=Transaction.[ID-Properties]) AND
(Properties.[ID-Properties]=Transaction.[ID-Properties]);
________

I have a query that works fine if I know which table to use for my query.
However, in reality the table I will use is based on the user selection in a
combo box in a form.

So,

If selection is A then use table A
If selection is B then use table B etc.

How do I pass this info to a query? How do I tell query to use A.[First
Name] or B.[First Name]

Thanks,
AC Erdal

To do this you will need to construct the SQL string in VBA code,
incorporating the tablename.

The need to do so, though, VERY strongly suggests that you're making an error
in your database design! Having multiple identically structured tables with
different names is *a very bad idea* and is not normalized. Could you consider
instead incorporating all of these tables into one with an additional field
containing what's now the tablename?

 

[John Spencer MVP]

Without any details on which table and what fields, you could do something
like the following.

Assumptions: The fields you want to use from the alternative tables are named
the same.

In the example, I am assuming that the table Sellers is the variable table.
You would call the following function by passing it the table name. Then you
would use the result returned and assign that as the source for a report or
form or as the SQL property of a saved query.

Function fBuildSQL(strTableName as String) as String
Dim StrSQL as String

strSQL = "SELECT [Last Name] & "","" & [First Name] AS Expr1" & _
", S.[First Name]" & _
", Properties.[Property Address]" & _
" FROM Properties INNER JOIN ([" & strTableName & "] As S" & _
" INNER JOIN [Transaction] " & _
" ON S.[ID-Sellers]=Transaction.[ID-Sellers])" & _
" ON (Properties.[ID-Properties]=Transaction.[ID-Properties])" & _
" AND (Properties.[ID-Properties]=Transaction.[ID-Properties])"

sBuildSQL = strSQL

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

I already have an SQL string in VBA for one table. See below for the code. I
am hoping to find out if and how can I use variables, etc. to select a
different table for the SQL. For example, I can use "dim variablename as
string" and assign a table name to this variable based on the selection from
a combo box. What I am not sure is how to incorporate this variable into the
SQL! When I use "dim" is that a global variable so that I can use it outsite
of a sub? Otherwise, how can I pass it to the SQL? Can I use
"variablename.[First Name] to selec the table? Let me know your feedback
regardless of if I endup combining all the tables into one.

I agree with you about combining the tables into one. There are two issues.
One is each table, even though a contact table, has very different fields.
For example, for one contact type I need to track their spouse
name/phone/email, etc. versus another contact type where I need to track
their assistant's name/phone/email. etc. There are also other differences.
So, it will not be an efficient single table with many empty fields.
Nevertheless I am considering combining them. However, I am hoping that there
is a easy way to combine the tables into one, rather than creating a new one
with all the fields from scratch! Is there a way? Like append or merge one
table to another!

Thanks,
AC Erdal
___ SQL string

SELECT [Last Name] & "," & [First Name] AS Expr1, Sellers.[First Name],
Properties.[Property Address] FROM Properties INNER JOIN (Sellers INNER JOIN
[Transaction] ON Sellers.[ID-Sellers]=Transaction.[ID-Sellers]) ON
(Properties.[ID-Properties]=Transaction.[ID-Properties]) AND
(Properties.[ID-Properties]=Transaction.[ID-Properties]);
________

I have a query that works fine if I know which table to use for my query.
However, in reality the table I will use is based on the user selection in a
combo box in a form.

So,

If selection is A then use table A
If selection is B then use table B etc.

How do I pass this info to a query? How do I tell query to use A.[First
Name] or B.[First Name]

Thanks,
AC Erdal

To do this you will need to construct the SQL string in VBA code,
incorporating the tablename.

The need to do so, though, VERY strongly suggests that you're making an error
in your database design! Having multiple identically structured tables with
different names is *a very bad idea* and is not normalized. Could you consider
instead incorporating all of these tables into one with an additional field
containing what's now the tablename?

 

[Dale_Fye via AccessMonster.com]

I agree with John, a single table is a much better option.

One of the problems with multiple tables is that it is difficult to search
for a person if you don't know what table they are in. One option might be
to create a Union query that combines the names and includes a field for
Tablename. Something like:

SELECT [LastName] & (", " + [FirstName] as Name, "A" as TableName, Contact_ID
FROM TableA
UNION All
SELECT [LastName] & (", " + [FirstName] as Name, "B" as TableName, Contact_ID
FROM TableB

You could then use this in an unbound combo box to select the individual you
want. From there, rather than changing the SQL statement, I think I would
create two separate subforms for the contact information, one based on each
of the tables. I would then use the afterupdate event of the name selection
combo box to change the SourceObject of the subform on the main form.

Just one technique.

John,

I already have an SQL string in VBA for one table. See below for the code. I
am hoping to find out if and how can I use variables, etc. to select a
different table for the SQL. For example, I can use "dim variablename as
string" and assign a table name to this variable based on the selection from
a combo box. What I am not sure is how to incorporate this variable into the
SQL! When I use "dim" is that a global variable so that I can use it outsite
of a sub? Otherwise, how can I pass it to the SQL? Can I use
"variablename.[First Name] to selec the table? Let me know your feedback
regardless of if I endup combining all the tables into one.

I agree with you about combining the tables into one. There are two issues.
One is each table, even though a contact table, has very different fields.
For example, for one contact type I need to track their spouse
name/phone/email, etc. versus another contact type where I need to track
their assistant's name/phone/email. etc. There are also other differences.
So, it will not be an efficient single table with many empty fields.
Nevertheless I am considering combining them. However, I am hoping that there
is a easy way to combine the tables into one, rather than creating a new one
with all the fields from scratch! Is there a way? Like append or merge one
table to another!

Thanks,
AC Erdal
___ SQL string

SELECT [Last Name] & "," & [First Name] AS Expr1, Sellers.[First Name],
Properties.[Property Address] FROM Properties INNER JOIN (Sellers INNER JOIN
[Transaction] ON Sellers.[ID-Sellers]=Transaction.[ID-Sellers]) ON
(Properties.[ID-Properties]=Transaction.[ID-Properties]) AND
(Properties.[ID-Properties]=Transaction.[ID-Properties]);
________
[quoted text clipped - 19 lines]

instead incorporating all of these tables into one with an additional field
containing what's now the tablename?

 

[ace]

Thanks for the info.

Can you explain ([" & strTableName & "] As S" mean that you put in the
SQL! I am not sure if I understand the reasons for [,",& characters you are
using.

Also, [ID-Seller] needs to change based on what the "S" value is. So, ID- is
fixed but the "Seller" part also need to be replaced by the "S". Can you show
me how you do that!

Thanks again.
AC Erdal

Without any details on which table and what fields, you could do something
like the following.

Assumptions: The fields you want to use from the alternative tables are named
the same.

In the example, I am assuming that the table Sellers is the variable table.
You would call the following function by passing it the table name. Then you
would use the result returned and assign that as the source for a report or
form or as the SQL property of a saved query.

Function fBuildSQL(strTableName as String) as String
Dim StrSQL as String

strSQL = "SELECT [Last Name] & "","" & [First Name] AS Expr1" & _
", S.[First Name]" & _
", Properties.[Property Address]" & _
" FROM Properties INNER JOIN ([" & strTableName & "] As S" & _
" INNER JOIN [Transaction] " & _
" ON S.[ID-Sellers]=Transaction.[ID-Sellers])" & _
" ON (Properties.[ID-Properties]=Transaction.[ID-Properties])" & _
" AND (Properties.[ID-Properties]=Transaction.[ID-Properties])"

sBuildSQL = strSQL

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

I already have an SQL string in VBA for one table. See below for the code. I
am hoping to find out if and how can I use variables, etc. to select a
different table for the SQL. For example, I can use "dim variablename as
string" and assign a table name to this variable based on the selection from
a combo box. What I am not sure is how to incorporate this variable into the
SQL! When I use "dim" is that a global variable so that I can use it outsite
of a sub? Otherwise, how can I pass it to the SQL? Can I use
"variablename.[First Name] to selec the table? Let me know your feedback
regardless of if I endup combining all the tables into one.

I agree with you about combining the tables into one. There are two issues.
One is each table, even though a contact table, has very different fields.
For example, for one contact type I need to track their spouse
name/phone/email, etc. versus another contact type where I need to track
their assistant's name/phone/email. etc. There are also other differences.
So, it will not be an efficient single table with many empty fields.
Nevertheless I am considering combining them. However, I am hoping that there
is a easy way to combine the tables into one, rather than creating a new one
with all the fields from scratch! Is there a way? Like append or merge one
table to another!

Thanks,
AC Erdal
___ SQL string

SELECT [Last Name] & "," & [First Name] AS Expr1, Sellers.[First Name],
Properties.[Property Address] FROM Properties INNER JOIN (Sellers INNER JOIN
[Transaction] ON Sellers.[ID-Sellers]=Transaction.[ID-Sellers]) ON
(Properties.[ID-Properties]=Transaction.[ID-Properties]) AND
(Properties.[ID-Properties]=Transaction.[ID-Properties]);
________

I have a query that works fine if I know which table to use for my query.
However, in reality the table I will use is based on the user selection in a
combo box in a form.

So,

If selection is A then use table A
If selection is B then use table B etc.

How do I pass this info to a query? How do I tell query to use A.[First
Name] or B.[First Name]

Thanks,
AC Erdal
To do this you will need to construct the SQL string in VBA code,
incorporating the tablename.

The need to do so, though, VERY strongly suggests that you're making an error
in your database design! Having multiple identically structured tables with
different names is *a very bad idea* and is not normalized. Could you consider
instead incorporating all of these tables into one with an additional field
containing what's now the tablename?

 

[John Spencer]

"as S" is assigning an alias to the table name.

The brackets are to take care of the possibility that you have not
followed the naming guidelines and have a space or other special
character in the table name.

So you want to change the following to something else based on the
tablename. The problem is you didn't say what you want to change it too
and if there is any relationship between the table name and the name of
the ID-Sellers field. (Oh by the way notice that the field is enclosed
in brackets since you used a "-" character in the field name.

" ON S.[ID-Sellers]=Transaction.[ID-Sellers])" &

The easiest thing for you to do might be to just have two query strings
and return the desired string based on a value. That might look like
the following. GUESSING as to field and table names.

Function fBuildSQL(strTableName as String) as String
Dim StrSQL as String

If strTableName = "Sellers" then
strSQL = "SELECT [Last Name] & "","" & [First Name] AS Expr1" & _
", S.[First Name]" & _
", Properties.[Property Address]" & _
" FROM Properties INNER JOIN (Sellers As S" & _
" INNER JOIN [Transaction] " & _
" ON S.[ID-Sellers]=Transaction.[ID-Sellers])" & _
" ON (Properties.[ID-Properties]=Transaction.[ID-Properties])" & _
" AND (Properties.[ID-Properties]=Transaction.[ID-Properties])"

else if strTableName = "Buyers" Then

strSQL = "SELECT [Last Name] & "","" & [First Name] AS Expr1" & _
B.[First Name]" & _
", Properties.[Property Address]" & _
" FROM Properties INNER JOIN (Buyers As B" & _
" INNER JOIN [Transaction] " & _
" ON S.[ID-Buyers]=Transaction.[ID-Buyers])" & _
" ON (Properties.[ID-Properties]=Transaction.[ID-Properties])" & _
" AND (Properties.[ID-Properties]=Transaction.[ID-Properties])"
End IF


sBuildSQL = strSQL

End Function


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for the info.

Can you explain ([" & strTableName & "] As S" mean that you put in the
SQL! I am not sure if I understand the reasons for [,",& characters you are
using.

Also, [ID-Seller] needs to change based on what the "S" value is. So, ID- is
fixed but the "Seller" part also need to be replaced by the "S". Can you show
me how you do that!

Thanks again.
AC Erdal

Without any details on which table and what fields, you could do something
like the following.

Assumptions: The fields you want to use from the alternative tables are named
the same.

In the example, I am assuming that the table Sellers is the variable table.
You would call the following function by passing it the table name. Then you
would use the result returned and assign that as the source for a report or
form or as the SQL property of a saved query.

Function fBuildSQL(strTableName as String) as String
Dim StrSQL as String

strSQL = "SELECT [Last Name] & "","" & [First Name] AS Expr1" & _
", S.[First Name]" & _
", Properties.[Property Address]" & _
" FROM Properties INNER JOIN ([" & strTableName & "] As S" & _
" INNER JOIN [Transaction] " & _
" ON S.[ID-Sellers]=Transaction.[ID-Sellers])" & _
" ON (Properties.[ID-Properties]=Transaction.[ID-Properties])" & _
" AND (Properties.[ID-Properties]=Transaction.[ID-Properties])"

sBuildSQL = strSQL

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

I already have an SQL string in VBA for one table. See below for the code. I
am hoping to find out if and how can I use variables, etc. to select a
different table for the SQL. For example, I can use "dim variablename as
string" and assign a table name to this variable based on the selection from
a combo box. What I am not sure is how to incorporate this variable into the
SQL! When I use "dim" is that a global variable so that I can use it outsite
of a sub? Otherwise, how can I pass it to the SQL? Can I use
"variablename.[First Name] to selec the table? Let me know your feedback
regardless of if I endup combining all the tables into one.

I agree with you about combining the tables into one. There are two issues.
One is each table, even though a contact table, has very different fields.
For example, for one contact type I need to track their spouse
name/phone/email, etc. versus another contact type where I need to track
their assistant's name/phone/email. etc. There are also other differences.
So, it will not be an efficient single table with many empty fields.
Nevertheless I am considering combining them. However, I am hoping that there
is a easy way to combine the tables into one, rather than creating a new one
with all the fields from scratch! Is there a way? Like append or merge one
table to another!

Thanks,
AC Erdal
___ SQL string

SELECT [Last Name] & "," & [First Name] AS Expr1, Sellers.[First Name],
Properties.[Property Address] FROM Properties INNER JOIN (Sellers INNER JOIN
[Transaction] ON Sellers.[ID-Sellers]=Transaction.[ID-Sellers]) ON
(Properties.[ID-Properties]=Transaction.[ID-Properties]) AND
(Properties.[ID-Properties]=Transaction.[ID-Properties]);
________

:


I have a query that works fine if I know which table to use for my query.
However, in reality the table I will use is based on the user selection in a
combo box in a form.

So,

If selection is A then use table A
If selection is B then use table B etc.

How do I pass this info to a query? How do I tell query to use A.[First
Name] or B.[First Name]

Thanks,
AC Erdal
To do this you will need to construct the SQL string in VBA code,
incorporating the tablename.

The need to do so, though, VERY strongly suggests that you're making an error
in your database design! Having multiple identically structured tables with
different names is *a very bad idea* and is not normalized. Could you consider
instead incorporating all of these tables into one with an additional field
containing what's now the tablename?

 

[John W. Vinson]

I agree with you about combining the tables into one. There are two issues.
One is each table, even though a contact table, has very different fields.
For example, for one contact type I need to track their spouse
name/phone/email, etc. versus another contact type where I need to track
their assistant's name/phone/email. etc. There are also other differences.
So, it will not be an efficient single table with many empty fields.
Nevertheless I am considering combining them. However, I am hoping that there
is a easy way to combine the tables into one, rather than creating a new one
with all the fields from scratch! Is there a way? Like append or merge one
table to another!

This is a good case for "Subclassing". My databases usually have a table named
CONtblPeople with all of the people (customers, employees, suspects,
witnesses, ...) in it; this table is related one-to-one to specific tables
with the additional fields for those specific classes of people.

You can certainly run Append queries to migrate data from table to table.

 

[ace]

John,

How do you separate the different group of people in this people table? Do
you have a category field in the table? In my case, which is a real estate
application, I have a seller, buyer, seller agent, buyer agent, etc.
However, a seller today can be a buyer tomorrow. Also a seller agent can also
be buyer agent, etc. So, having a category in the table may not help me! I
have also a Sales table. I can create the people table (oneside) four times
in the relationship window and connect the IDPeople into Sales table (many
side) four time to establish the relationship. The part that I am still not
understanding is when and where do I tell the database a seller xyz is
related to Sales 123 during data entry? Same for buyer, buyer agent, etc.
This is why I kept all these tables separate but I am hoping you can help
clarifying this issue!

Thanks,
AC Erdal

 

[ace]

John,
I did reply to you but seems like it is not in the system! So, here is my
question(s).

I presume you have no overlap in different category of people in your people
table and perhaps you created a category field to separate them! Is this
correct?

In my case, for the real estate application, I have buyer, seller, buyer
agent and seller agent. In reality a buyer can become a seller later. Also,
buyer agent and seller agent can be the same, as well as an agent can be
seller or buyer. So, creating a category at the people table is not going to
help. I also have a sales table for a specific sales of a property. How do
you suggest I should relate for example a seller "S1", a buyer "B1", etc. to
a particular sale 123 and property "XYZ1"? I think I can connect them through
the sales table, I am not sure what is the best way! This is the reason I
originally went for separate tables for seller, buyer, etc.

Let me know what you think.
Thanks,,
AC Erdal