Append string value to table name

Lulu0 used Ask the Experts™
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

...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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

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
    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

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
- 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