Naming tables useing various names - Create table query and loops

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI all,

Newby to this.....

Can anyone please help me here, I am sure its simple but the penny is not
dropping.

I want to create 3 tables using runsql: The table names are Tbl_Cash,
Tbl_CC, Tbl_chq. I have the sql ready to go in eg: Create table (MyVar),
[id_nr ] as int, Inv_Nr as Text. etc... Not precise but the real code works.


The question is how do i fill th MyVar field with the appropriate table name
then loop the code so it will create all three tables. The long and wrong
way is to write the 3 codes with the table names hard coded. Or is there a
better way to do this?

Thanks in advance

Regards

Roger
 
If you've got 3 tables that are identical except for their names, you might
want to reconsider your design. Add an additional column for Payment Type,
and store everything in the one table.

The answer to your question, though, is that you need to build the SQL in
VBA, along the lines of:

Dim intLoop As Integer
Dim strSQL As String
Dim strTables(1 To 3) As String

strTables(1) = "Tbl_Cash"
strTables(2) = "Tbl_CC"
strTables(3) = "Tbl_chq"

For intLoop = 1 To 3
strSQL = Create table " & strTables(intLoop) & _
"[id_nr ] as int, Inv_Nr as Text. etc..."
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop
 
Thankyou very much!!!!

I had most of it right but defining the variables undid me.

I have a transactio table to enter the data, I need to separate all
cash/chq/cc transaction and give them a contigious number, so if i have 50
cash transactions the y would have an "index" number of 1 through 50, same
for cc and chqs. Then I need to create batches of 25 and give that batch a
unique number eg: the first 25 cash trans are in batch 1 the next are in
batch 2 etc... They also need to be separated by date so that a batch cannot
include a transaction ffrom another date (usually the next day).

So in current method I need to delete and recreate each table with an
autonumber field attached then insert into each table the relevant
transaction. Each time they correct a transaction this process is repeated
drop, create insert. The hardest issue is the batching of records.

Any other suggestions would be appreciated, I am learning alot thankyou.

Regards

Roger




Douglas J. Steele said:
If you've got 3 tables that are identical except for their names, you might
want to reconsider your design. Add an additional column for Payment Type,
and store everything in the one table.

The answer to your question, though, is that you need to build the SQL in
VBA, along the lines of:

Dim intLoop As Integer
Dim strSQL As String
Dim strTables(1 To 3) As String

strTables(1) = "Tbl_Cash"
strTables(2) = "Tbl_CC"
strTables(3) = "Tbl_chq"

For intLoop = 1 To 3
strSQL = Create table " & strTables(intLoop) & _
"[id_nr ] as int, Inv_Nr as Text. etc..."
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Gettingthere said:
HI all,

Newby to this.....

Can anyone please help me here, I am sure its simple but the penny is not
dropping.

I want to create 3 tables using runsql: The table names are Tbl_Cash,
Tbl_CC, Tbl_chq. I have the sql ready to go in eg: Create table (MyVar),
[id_nr ] as int, Inv_Nr as Text. etc... Not precise but the real code works.


The question is how do i fill th MyVar field with the appropriate table name
then loop the code so it will create all three tables. The long and wrong
way is to write the 3 codes with the table names hard coded. Or is there a
better way to do this?

Thanks in advance

Regards

Roger
Click to expand...
Click to expand...
 
You do realize that Autonumbers aren't guaranteed to be contiguous, I hope.
In fact, if the value of the number matters to you, then Autonumbers
probably aren't an appropriate choice.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Gettingthere said:
Thankyou very much!!!!

I had most of it right but defining the variables undid me.

I have a transactio table to enter the data, I need to separate all
cash/chq/cc transaction and give them a contigious number, so if i have 50
cash transactions the y would have an "index" number of 1 through 50, same
for cc and chqs. Then I need to create batches of 25 and give that batch a
unique number eg: the first 25 cash trans are in batch 1 the next are in
batch 2 etc... They also need to be separated by date so that a batch cannot
include a transaction ffrom another date (usually the next day).

So in current method I need to delete and recreate each table with an
autonumber field attached then insert into each table the relevant
transaction. Each time they correct a transaction this process is repeated
drop, create insert. The hardest issue is the batching of records.

Any other suggestions would be appreciated, I am learning alot thankyou.

Regards

Roger




Douglas J. Steele said:
If you've got 3 tables that are identical except for their names, you might
want to reconsider your design. Add an additional column for Payment Type,
and store everything in the one table.

The answer to your question, though, is that you need to build the SQL in
VBA, along the lines of:

Dim intLoop As Integer
Dim strSQL As String
Dim strTables(1 To 3) As String

strTables(1) = "Tbl_Cash"
strTables(2) = "Tbl_CC"
strTables(3) = "Tbl_chq"

For intLoop = 1 To 3
strSQL = Create table " & strTables(intLoop) & _
"[id_nr ] as int, Inv_Nr as Text. etc..."
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Gettingthere said:
HI all,

Newby to this.....

Can anyone please help me here, I am sure its simple but the penny is not
dropping.

I want to create 3 tables using runsql: The table names are Tbl_Cash,
Tbl_CC, Tbl_chq. I have the sql ready to go in eg: Create table (MyVar),
[id_nr ] as int, Inv_Nr as Text. etc... Not precise but the real code works.


The question is how do i fill th MyVar field with the appropriate
Click to expand...
table
name
then loop the code so it will create all three tables. The long and wrong
way is to write the 3 codes with the table names hard coded. Or is
Click to expand...
there
a
better way to do this?

Thanks in advance

Regards

Roger
Click to expand...
Click to expand...
Click to expand...
 
Would a last(Id_Nr) + 1 , or (max) be better?
My limited testing by creating table - deleteing and populating has worked
so far. Have done the creation of the table using runsql scripting.

Thanks

Roger

Douglas J. Steele said:
You do realize that Autonumbers aren't guaranteed to be contiguous, I hope.
In fact, if the value of the number matters to you, then Autonumbers
probably aren't an appropriate choice.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Gettingthere said:
Thankyou very much!!!!

I had most of it right but defining the variables undid me.

I have a transactio table to enter the data, I need to separate all
cash/chq/cc transaction and give them a contigious number, so if i have 50
cash transactions the y would have an "index" number of 1 through 50, same
for cc and chqs. Then I need to create batches of 25 and give that batch a
unique number eg: the first 25 cash trans are in batch 1 the next are in
batch 2 etc... They also need to be separated by date so that a batch cannot
include a transaction ffrom another date (usually the next day).

So in current method I need to delete and recreate each table with an
autonumber field attached then insert into each table the relevant
transaction. Each time they correct a transaction this process is repeated
drop, create insert. The hardest issue is the batching of records.

Any other suggestions would be appreciated, I am learning alot thankyou.

Regards

Roger




Douglas J. Steele said:
If you've got 3 tables that are identical except for their names, you might
want to reconsider your design. Add an additional column for Payment Type,
and store everything in the one table.

The answer to your question, though, is that you need to build the SQL in
VBA, along the lines of:

Dim intLoop As Integer
Dim strSQL As String
Dim strTables(1 To 3) As String

strTables(1) = "Tbl_Cash"
strTables(2) = "Tbl_CC"
strTables(3) = "Tbl_chq"

For intLoop = 1 To 3
strSQL = Create table " & strTables(intLoop) & _
"[id_nr ] as int, Inv_Nr as Text. etc..."
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



HI all,

Newby to this.....

Can anyone please help me here, I am sure its simple but the penny is not
dropping.

I want to create 3 tables using runsql: The table names are Tbl_Cash,
Tbl_CC, Tbl_chq. I have the sql ready to go in eg: Create table (MyVar),
[id_nr ] as int, Inv_Nr as Text. etc... Not precise but the real code
works.


The question is how do i fill th MyVar field with the appropriate table
name
then loop the code so it will create all three tables. The long and wrong
way is to write the 3 codes with the table names hard coded. Or is there
a
better way to do this?

Thanks in advance

Regards

Roger
Click to expand...
Click to expand...
Click to expand...
 
I would put everything into a single table, and determine the next value to
use myself, rather than using Autonumbers.

Don't use Last: it's really a meaningless concept in database theory. Use
Max (or DMax) instead.

Is this a single user database, or a multiuser? For single user, it's
trivial to check what the largest value already used for the payment method
is so that you can calculate the next number to use. For multiuser, you may
want to create a second table indicating the largest number already used,
and write a function that uses a transaction so that the table is locked to
all other users while User A gets the next number to use, to minimize the
possibility of duplicate numbers occurring.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Gettingthere said:
Would a last(Id_Nr) + 1 , or (max) be better?
My limited testing by creating table - deleteing and populating has worked
so far. Have done the creation of the table using runsql scripting.

Thanks

Roger

Douglas J. Steele said:
You do realize that Autonumbers aren't guaranteed to be contiguous, I hope.
In fact, if the value of the number matters to you, then Autonumbers
probably aren't an appropriate choice.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Gettingthere said:
Thankyou very much!!!!

I had most of it right but defining the variables undid me.

I have a transactio table to enter the data, I need to separate all
cash/chq/cc transaction and give them a contigious number, so if i have 50
cash transactions the y would have an "index" number of 1 through 50, same
for cc and chqs. Then I need to create batches of 25 and give that batch a
unique number eg: the first 25 cash trans are in batch 1 the next are in
batch 2 etc... They also need to be separated by date so that a batch cannot
include a transaction ffrom another date (usually the next day).

So in current method I need to delete and recreate each table with an
autonumber field attached then insert into each table the relevant
transaction. Each time they correct a transaction this process is repeated
drop, create insert. The hardest issue is the batching of records.

Any other suggestions would be appreciated, I am learning alot thankyou.

Regards

Roger




:

If you've got 3 tables that are identical except for their names,
Click to expand...
you
might
want to reconsider your design. Add an additional column for Payment Type,
and store everything in the one table.

The answer to your question, though, is that you need to build the
Click to expand...
SQL
in
VBA, along the lines of:

Dim intLoop As Integer
Dim strSQL As String
Dim strTables(1 To 3) As String

strTables(1) = "Tbl_Cash"
strTables(2) = "Tbl_CC"
strTables(3) = "Tbl_chq"

For intLoop = 1 To 3
strSQL = Create table " & strTables(intLoop) & _
"[id_nr ] as int, Inv_Nr as Text. etc..."
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



HI all,

Newby to this.....

Can anyone please help me here, I am sure its simple but the penny
Click to expand...
is
not
dropping.

I want to create 3 tables using runsql: The table names are Tbl_Cash,
Tbl_CC, Tbl_chq. I have the sql ready to go in eg: Create table (MyVar),
[id_nr ] as int, Inv_Nr as Text. etc... Not precise but the real code
works.


The question is how do i fill th MyVar field with the appropriate table
name
then loop the code so it will create all three tables. The long
Click to expand...
and
wrong
way is to write the 3 codes with the table names hard coded. Or
Click to expand...
is
there
a
better way to do this?

Thanks in advance

Regards

Roger
Click to expand...
Click to expand...
Click to expand...
 
Thankyou for your assistance in this and other threads - Much appreciated

Roger

Douglas J. Steele said:
I would put everything into a single table, and determine the next value to
use myself, rather than using Autonumbers.

Don't use Last: it's really a meaningless concept in database theory. Use
Max (or DMax) instead.

Is this a single user database, or a multiuser? For single user, it's
trivial to check what the largest value already used for the payment method
is so that you can calculate the next number to use. For multiuser, you may
want to create a second table indicating the largest number already used,
and write a function that uses a transaction so that the table is locked to
all other users while User A gets the next number to use, to minimize the
possibility of duplicate numbers occurring.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Gettingthere said:
Would a last(Id_Nr) + 1 , or (max) be better?
My limited testing by creating table - deleteing and populating has worked
so far. Have done the creation of the table using runsql scripting.

Thanks

Roger

Douglas J. Steele said:
You do realize that Autonumbers aren't guaranteed to be contiguous, I hope.
In fact, if the value of the number matters to you, then Autonumbers
probably aren't an appropriate choice.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)




Thankyou very much!!!!

I had most of it right but defining the variables undid me.

I have a transactio table to enter the data, I need to separate all
cash/chq/cc transaction and give them a contigious number, so if i have 50
cash transactions the y would have an "index" number of 1 through 50, same
for cc and chqs. Then I need to create batches of 25 and give that batch a
unique number eg: the first 25 cash trans are in batch 1 the next are in
batch 2 etc... They also need to be separated by date so that a batch
cannot
include a transaction ffrom another date (usually the next day).

So in current method I need to delete and recreate each table with an
autonumber field attached then insert into each table the relevant
transaction. Each time they correct a transaction this process is
repeated
drop, create insert. The hardest issue is the batching of records.

Any other suggestions would be appreciated, I am learning alot thankyou.

Regards

Roger




:

If you've got 3 tables that are identical except for their names, you
might
want to reconsider your design. Add an additional column for Payment
Type,
and store everything in the one table.

The answer to your question, though, is that you need to build the SQL
in
VBA, along the lines of:

Dim intLoop As Integer
Dim strSQL As String
Dim strTables(1 To 3) As String

strTables(1) = "Tbl_Cash"
strTables(2) = "Tbl_CC"
strTables(3) = "Tbl_chq"

For intLoop = 1 To 3
strSQL = Create table " & strTables(intLoop) & _
"[id_nr ] as int, Inv_Nr as Text. etc..."
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



HI all,

Newby to this.....

Can anyone please help me here, I am sure its simple but the penny is
not
dropping.

I want to create 3 tables using runsql: The table names are
Tbl_Cash,
Tbl_CC, Tbl_chq. I have the sql ready to go in eg: Create table
(MyVar),
[id_nr ] as int, Inv_Nr as Text. etc... Not precise but the real code
works.


The question is how do i fill th MyVar field with the appropriate
table
name
then loop the code so it will create all three tables. The long and
wrong
way is to write the 3 codes with the table names hard coded. Or is
there
a
better way to do this?

Thanks in advance

Regards

Roger
Click to expand...
Click to expand...
Click to expand...
 
Back
Top