SQL question

  • Thread starter Thread starter lbsstacey
  • Start date Start date
L

lbsstacey

I have designed a database where I have many queries that do the same thing,
they just are based on tables with specific client information. I know which
client the table relates to based on an abbreviated name found in my process
form. I am wondering how this code could be modified so instead of referring
to a specific table "Terms_BE", the query could 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 )
 
This can be done only if you're building the SQL string in VBA code, and
then running the query. Is that what you're doing? Tell us more details.
 
I have designed a database where I have many queries that do the same thing,
they just are based on tables with specific client information. I know which
client the table relates to based on an abbreviated name found in my process
form. I am wondering how this code could be modified so instead of referring
to a specific table "Terms_BE", the query could 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 )
Click to expand...

You're having difficulty because this entire database design is suspect:
storing data in a tablename as you're doing is simply WRONG. Could you
consider instead having one client information table with an additional field
identifying the client? You could use a Query selecting just the "BE" records
(or any other client's records) to recreate your current view.

You cannot use a variable tablename in a query; you'll need to write VBA code
to construct the SQL each time.
 
This design flaw is called Attribute Splitting. You take an
attribute, say sex_code, and built a table for each of its values.
This would give you "Male_Personnel" and "Female_Personnel" tables
when you needed only the "Personnel" table and perhaps two views on
it.

Data integrity is shot to hell, as well as performance. The queries
become nightmares of complexity that attempt to re-build the
normalized schema on the fly.

You need to read at least ONE book on data modeling before you code
again.
 
Back
Top