Append string value to table name

Lulu0
Lulu0 used Ask the Experts™
on
How do I get the value from "purchasing" table (assume that value is 1001) and make a new table called "sales" having that retrieved value after it, such as "sales1001"?

I've started....
sql = "SELECT max(transaction_id) as maxT INTO temp FROM purchasing; "
db.Execute sql
DoEvents

...and to make the new table...
sql2 = "SELECT cust_id, prod_id, qty INTO sales  <--how do I append the value 1001 here?
from cust_inv
db.Execute sql2
DoEvents
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
GlobaLevelProgrammer
Commented:
found these on the web...

Sub CreateTable()

Dim dbs As Database, tbl As TableDef, fld As Field

Set dbs = CurrentDb

Set tbl = dbs.CreateTableDef("Test")
Set fld = tbl.CreateField("test1", dbText)

tbl.Fields.Append fld
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh


find name:
Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
    Set db = DBEngine.Workspaces(0).Databases(0)
    fExistTable = False
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.Count - 1
        If strTableName = db.TableDefs(i).Name Then
            'Table Exists
            fExistTable = True
            Exit For
        End If
    Next i
    Set db = Nothing
End Function
 
 



Commented:
What is the purpose of creating this additional table? Generally, creating individual tables for items in this manner is bad practice and goes against normalization.

Can you please explain?

- Anthony
Top Expert 2011
Commented:
- we use & to concatenate strings. note that there is no spaces after sales to concatenate sales with maxT. however there must be a space before the FROM.
- i am not sure what you are trying to achieve here, but i believe you might need to specify a WHERE clause on cust_inv or else it will take the whole record when creating the new table.

sql = "SELECT max(transaction_id) as maxT INTO temp FROM purchasing; "
db.Execute sql

sql2 = "SELECT cust_id, prod_id, qty INTO sales" & maxT & " FROM cust_inv"
db.Execute sql2

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial