SQL query with variable

[lbsstacey]

I have designed a database where I have many queries that look the same but
they each refer to a different table that contains specific client
information. I run my queries from a process form that contains an
abbreviated name that refers to each client. I am wondering how I can change
my query to instead of referring to the table "Terms_BE" I can refer to a
variable "Terms _" & [Forms]![MainForm]![AbbrName]

INSERT INTO Terms_BE ( SubSSn, MBRSSn, Rel, [Employee_First Name],
[Employee_Middle Initial], [Employee_Last Name], EffDate, TermDate, Field21,
Expr1 )

 

[Rick Brandt]

I have designed a database where I have many queries that look the same
but they each refer to a different table that contains specific client
information. I run my queries from a process form that contains an
abbreviated name that refers to each client. I am wondering how I can
change my query to instead of referring to the table "Terms_BE" I can
refer to a variable "Terms _" & [Forms]![MainForm]![AbbrName]

INSERT INTO Terms_BE ( SubSSn, MBRSSn, Rel, [Employee_First Name],
[Employee_Middle Initial], [Employee_Last Name], EffDate, TermDate,
Field21, Expr1 )

The best answer is that you should not have separate tables per
customer. You should have one table that includes a FIELD that
identifies the customer. Then you filter on that field when you only
want to deal with the data of particular customers.

Then you have one set of queries, forms, and reports that do everything
you need.

 

[lbsstacey]

Assuming that I don't want to combine the tables (it's eligibility data and
each table has very specific information in it), is there a way I can assign
a variable using SQL and then use the variable in the query?

I have designed a database where I have many queries that look the same
but they each refer to a different table that contains specific client
information. I run my queries from a process form that contains an
abbreviated name that refers to each client. I am wondering how I can
change my query to instead of referring to the table "Terms_BE" I can
refer to a variable "Terms _" & [Forms]![MainForm]![AbbrName]

INSERT INTO Terms_BE ( SubSSn, MBRSSn, Rel, [Employee_First Name],
[Employee_Middle Initial], [Employee_Last Name], EffDate, TermDate,
Field21, Expr1 )

The best answer is that you should not have separate tables per
customer. You should have one table that includes a FIELD that
identifies the customer. Then you filter on that field when you only
want to deal with the data of particular customers.

Then you have one set of queries, forms, and reports that do everything
you need.

 

[Rick Brandt]

Assuming that I don't want to combine the tables (it's eligibility data
and each table has very specific information in it), is there a way I
can assign a variable using SQL and then use the variable in the query?

No, but you could use code to re-write the SQL in the query each time.