Create stored procedure with variable for table name

G

Guest

In Access Project (2002) and I'm writing a stored procedure to create tables
based on whatever year the user selects. i.e. asmntyear = @year. This
executes correctly but I would also like to use the @year in my table name
(see below). My INTO table name gives me an error at the &.

Is there a way to incorporate this variable in the table name? Do you have
another suggestion?

SELECT dbo.tblPembina_Linear_Master.[Roll Number],
dbo.tblPembina_Linear_Master.LPAUID, dbo.tblPembina_Linear_Master.[Munc
Code],
dbo.tblPembina_Linear_Master.[Municipality Name],
dbo.tblPembina_Linear_Master.Licence, dbo.tblPembina_Linear_Master.[Line ],
dbo.tblPembina_Linear_Master.Description,
dbo.tblPembina_Linear_Master.[Fr Fac ], dbo.tblPembina_Linear_Master.[Fr
Lsd],
dbo.tblPembina_Linear_Master.[Fr Rge],
dbo.tblPembina_Linear_Master.[Fr Twp], dbo.tblPembina_Linear_Master.[Fr Sec],
dbo.tblPembina_Linear_Master.[Fr Mer],
dbo.tblPembina_Linear_Master.[To Fac ], dbo.tblPembina_Linear_Master.[To
Lsd],
dbo.tblPembina_Linear_Master.[To Sec],
dbo.tblPembina_Linear_Master.[To Twp], dbo.tblPembina_Linear_Master.[To Rge],
dbo.tblPembina_Linear_Master.[To Mer],
dbo.tblPembina_Linear_Master.[AUB Status],
dbo.tblPembina_Linear_Master.[Material/Substance/Size],
dbo.tblPembina_Linear_Master.Length,
dbo.tblPembina_Linear_Master.FACILITY, dbo.tblPembina_Linear_Master.COMMENTS,
dbo.tblEubdata.AsmntValue, dbo.tblEubdata.AsmntYr,
dbo.tblPembina_Linear_Master.Rate, dbo.tblPembina_Linear_Master.Depr,
dbo.tblPembina_Linear_Master.[Share %],
dbo.tblMultiplier.Multiplier,
ROUND(((dbo.tblPembina_Linear_Master.Length *
dbo.tblPembina_Linear_Master.Rate) * (dbo.tblPembina_Linear_Master.[Share %]
/ 100))
* (dbo.tblPembina_Linear_Master.Depr / 100) *
dbo.tblMultiplier.Multiplier * 0.75, - 1) AS check_value
INTO dbo.[tbl] + @year + [LinearAssessment]
FROM dbo.tblEubdata INNER JOIN
dbo.tblMultiplier ON dbo.tblEubdata.AsmntYr =
dbo.tblMultiplier.Year INNER JOIN
dbo.tblPembina_Linear_Master ON dbo.tblEubdata.LPAUID
= dbo.tblPembina_Linear_Master.LPAUID INNER JOIN
dbo.tblCreateTableMunc ON
dbo.tblPembina_Linear_Master.[Municipality Name] =
dbo.tblCreateTableMunc.[Municipality Name]
WHERE (dbo.tblEubdata.AsmntYr = @Year)
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Good DB design says that the table [LinearAssessment] should have a Year
column and all the years' data should be put in ONE table. Breaking out
the data into separate tables by year is a design flaw called attribute
splitting. Google it to find out why you shouldn't use it:

http://www.mcse.ms/archive94-2004-12-1317873.html
http://www.thescripts.com/forum/thread143904.html - CELKO
http://www.mcse.ms/archive94-2003-12-228249.html - CELKO again
http://forums.asp.net/888069/ShowPost.aspx

On each page search for "attribute splitting."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+ul64echKqOuFEgEQImuQCggE7gMIbeOyryC9DLTWNwFKYRyFEAn1ui
7tj+SWkTtRyF4Z2TQmGVNjCE
=oisp
-----END PGP SIGNATURE-----
In Access Project (2002) and I'm writing a stored procedure to create tables
based on whatever year the user selects. i.e. asmntyear = @year. This
executes correctly but I would also like to use the @year in my table name
(see below). My INTO table name gives me an error at the &.

Is there a way to incorporate this variable in the table name? Do you have
another suggestion?

SELECT dbo.tblPembina_Linear_Master.[Roll Number],
dbo.tblPembina_Linear_Master.LPAUID, dbo.tblPembina_Linear_Master.[Munc
Code],
dbo.tblPembina_Linear_Master.[Municipality Name],
dbo.tblPembina_Linear_Master.Licence, dbo.tblPembina_Linear_Master.[Line ],
dbo.tblPembina_Linear_Master.Description,
dbo.tblPembina_Linear_Master.[Fr Fac ], dbo.tblPembina_Linear_Master.[Fr
Lsd],
dbo.tblPembina_Linear_Master.[Fr Rge],
dbo.tblPembina_Linear_Master.[Fr Twp], dbo.tblPembina_Linear_Master.[Fr Sec],
dbo.tblPembina_Linear_Master.[Fr Mer],
dbo.tblPembina_Linear_Master.[To Fac ], dbo.tblPembina_Linear_Master.[To
Lsd],
dbo.tblPembina_Linear_Master.[To Sec],
dbo.tblPembina_Linear_Master.[To Twp], dbo.tblPembina_Linear_Master.[To Rge],
dbo.tblPembina_Linear_Master.[To Mer],
dbo.tblPembina_Linear_Master.[AUB Status],
dbo.tblPembina_Linear_Master.[Material/Substance/Size],
dbo.tblPembina_Linear_Master.Length,
dbo.tblPembina_Linear_Master.FACILITY, dbo.tblPembina_Linear_Master.COMMENTS,
dbo.tblEubdata.AsmntValue, dbo.tblEubdata.AsmntYr,
dbo.tblPembina_Linear_Master.Rate, dbo.tblPembina_Linear_Master.Depr,
dbo.tblPembina_Linear_Master.[Share %],
dbo.tblMultiplier.Multiplier,
ROUND(((dbo.tblPembina_Linear_Master.Length *
dbo.tblPembina_Linear_Master.Rate) * (dbo.tblPembina_Linear_Master.[Share %]
/ 100))
* (dbo.tblPembina_Linear_Master.Depr / 100) *
dbo.tblMultiplier.Multiplier * 0.75, - 1) AS check_value
INTO dbo.[tbl] + @year + [LinearAssessment]
FROM dbo.tblEubdata INNER JOIN
dbo.tblMultiplier ON dbo.tblEubdata.AsmntYr =
dbo.tblMultiplier.Year INNER JOIN
dbo.tblPembina_Linear_Master ON dbo.tblEubdata.LPAUID
= dbo.tblPembina_Linear_Master.LPAUID INNER JOIN
dbo.tblCreateTableMunc ON
dbo.tblPembina_Linear_Master.[Municipality Name] =
dbo.tblCreateTableMunc.[Municipality Name]
WHERE (dbo.tblEubdata.AsmntYr = @Year)
Click to expand...
 
G

Guest

The reason I went to tables is because I can't get my form to work correctly
using a query. When I change my data source from my table to a query I get
the following message:

"An insufficient number of arguments were supplied for the procedure or
funcation dbo.qryCurrentYearAssessments"

The qry is based on a table and the only condition I have is on the year
where I have @year as a prompt to key the year required.

The query works fine and as a form it works fine. However, as a sub-form
linked to a combo box based on muncname for both it doesn't.

Any ideas??

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Good DB design says that the table [LinearAssessment] should have a Year
column and all the years' data should be put in ONE table. Breaking out
the data into separate tables by year is a design flaw called attribute
splitting. Google it to find out why you shouldn't use it:

http://www.mcse.ms/archive94-2004-12-1317873.html
http://www.thescripts.com/forum/thread143904.html - CELKO
http://www.mcse.ms/archive94-2003-12-228249.html - CELKO again
http://forums.asp.net/888069/ShowPost.aspx

On each page search for "attribute splitting."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+ul64echKqOuFEgEQImuQCggE7gMIbeOyryC9DLTWNwFKYRyFEAn1ui
7tj+SWkTtRyF4Z2TQmGVNjCE
=oisp
-----END PGP SIGNATURE-----
In Access Project (2002) and I'm writing a stored procedure to create tables
based on whatever year the user selects. i.e. asmntyear = @year. This
executes correctly but I would also like to use the @year in my table name
(see below). My INTO table name gives me an error at the &.

Is there a way to incorporate this variable in the table name? Do you have
another suggestion?

SELECT dbo.tblPembina_Linear_Master.[Roll Number],
dbo.tblPembina_Linear_Master.LPAUID, dbo.tblPembina_Linear_Master.[Munc
Code],
dbo.tblPembina_Linear_Master.[Municipality Name],
dbo.tblPembina_Linear_Master.Licence, dbo.tblPembina_Linear_Master.[Line ],
dbo.tblPembina_Linear_Master.Description,
dbo.tblPembina_Linear_Master.[Fr Fac ], dbo.tblPembina_Linear_Master.[Fr
Lsd],
dbo.tblPembina_Linear_Master.[Fr Rge],
dbo.tblPembina_Linear_Master.[Fr Twp], dbo.tblPembina_Linear_Master.[Fr Sec],
dbo.tblPembina_Linear_Master.[Fr Mer],
dbo.tblPembina_Linear_Master.[To Fac ], dbo.tblPembina_Linear_Master.[To
Lsd],
dbo.tblPembina_Linear_Master.[To Sec],
dbo.tblPembina_Linear_Master.[To Twp], dbo.tblPembina_Linear_Master.[To Rge],
dbo.tblPembina_Linear_Master.[To Mer],
dbo.tblPembina_Linear_Master.[AUB Status],
dbo.tblPembina_Linear_Master.[Material/Substance/Size],
dbo.tblPembina_Linear_Master.Length,
dbo.tblPembina_Linear_Master.FACILITY, dbo.tblPembina_Linear_Master.COMMENTS,
dbo.tblEubdata.AsmntValue, dbo.tblEubdata.AsmntYr,
dbo.tblPembina_Linear_Master.Rate, dbo.tblPembina_Linear_Master.Depr,
dbo.tblPembina_Linear_Master.[Share %],
dbo.tblMultiplier.Multiplier,
ROUND(((dbo.tblPembina_Linear_Master.Length *
dbo.tblPembina_Linear_Master.Rate) * (dbo.tblPembina_Linear_Master.[Share %]
/ 100))
* (dbo.tblPembina_Linear_Master.Depr / 100) *
dbo.tblMultiplier.Multiplier * 0.75, - 1) AS check_value
INTO dbo.[tbl] + @year + [LinearAssessment]
FROM dbo.tblEubdata INNER JOIN
dbo.tblMultiplier ON dbo.tblEubdata.AsmntYr =
dbo.tblMultiplier.Year INNER JOIN
dbo.tblPembina_Linear_Master ON dbo.tblEubdata.LPAUID
= dbo.tblPembina_Linear_Master.LPAUID INNER JOIN
dbo.tblCreateTableMunc ON
dbo.tblPembina_Linear_Master.[Municipality Name] =
dbo.tblCreateTableMunc.[Municipality Name]
WHERE (dbo.tblEubdata.AsmntYr = @Year)
Click to expand...
Click to expand...
 
Top