Query a Yet To be Named Table?

[Guest]

I have built a quasi-program using switchboard manager and various forms.
This program was created to streamline the data entry here where I work. Thus
I have four tables that data is entered into via the forms, then once a month
they need to create a bill for all the customers. Right now I’m using run
query commands to update everything, then I have a command button where the
user can choose which customer to bill, and I use a SELECT INTO statement to
make a new table for that month i.e. 05-05-05Billing is the table. I also use
this table to export a fixed field file that is the bill. Now I would like to
give the user the ability to search these newly created tables. So,
1 does anybody have any syntax for querying a table that will be created
months from now preferably with a command button and an entered text.
2 how to OutputTo or export table information from a table that has not been
named i.e. it will have the date as its name.
3 While I’m here and asking, can I pull info from a yet to be created table
to fill form i.e. the old total will come from the table made 04-05-05 and
then on the form the new total will be entered

This is the code for updates and create the new table for this month.

If Me.Frame22.Value = 2 Then

DoCmd.RunSQL "UPDATE WebClients SET batchNumber =
Forms!createBillsForm.batchNumber;"
DoCmd.RunSQL "UPDATE WebClients SET lastBillDate =
Forms!createBillsForm.date;"

' *** create the query table name
strTableName = Format(Now(), "mmddyy")

DoCmd.RunSQL "SELECT copierList.billableCopies,
copierList.departmentContact, copierList.costPerCopy, copierList.total INTO "
+ strTableName + " FROM copierList;"

This is the code for the out put to function but I need to have a variable
name for each new table created
DoCmd.OpenForm "finishDialog"
DoCmd.RunMacro "Macro4"

This is the SQL that is probably the hardest once again 051705 is the new
table created for this month’s bills and the question is can I change that
table name i.e. 051705 will be 061805 next month so that users can search the
new billing file.

SELECT [051705].batchNumber, [051705].customerID, [051705].billableCopies,
[051705].previousMeter, [051705].departmentContact, [051705].costPerCopy,
[051705].total, *
FROM 051705
WHERE ((([051705].batchNumber) Like "*" & [Forms]![searchAll]![searchText] &
"*") AND (([051705].customerID) Like "*" & [Forms]![searchAll]![searchText] &
"*")

 

[George Nicholson]

Rather than making a new table every month, why not create a table called
INVOICES and append data to it? You would have a record of all billing that
could be queried by date (or month, year)?

 

[Guest]

Don't make a new table for every date..

Ad a date field to the table, make a unique index on it (and other key
fields).
Make queries to export date specific information to the flatfile, in the
query, it's easy to make a criteria of date.

instead of a 'select into'

use an append query and append all billing data to the same table (WITH a
datestamp)

otherwise, you're probably going to write a mess of code, that will be
confusing to the next developer..

Keep it simple man

I have built a quasi-program using switchboard manager and various forms.
This program was created to streamline the data entry here where I work. Thus
I have four tables that data is entered into via the forms, then once a month
they need to create a bill for all the customers. Right now I’m using run
query commands to update everything, then I have a command button where the
user can choose which customer to bill, and I use a SELECT INTO statement to
make a new table for that month i.e. 05-05-05Billing is the table. I also use
this table to export a fixed field file that is the bill. Now I would like to
give the user the ability to search these newly created tables. So,
1 does anybody have any syntax for querying a table that will be created
months from now preferably with a command button and an entered text.
2 how to OutputTo or export table information from a table that has not been
named i.e. it will have the date as its name.
3 While I’m here and asking, can I pull info from a yet to be created table
to fill form i.e. the old total will come from the table made 04-05-05 and
then on the form the new total will be entered

This is the code for updates and create the new table for this month.

If Me.Frame22.Value = 2 Then

DoCmd.RunSQL "UPDATE WebClients SET batchNumber =
Forms!createBillsForm.batchNumber;"
DoCmd.RunSQL "UPDATE WebClients SET lastBillDate =
Forms!createBillsForm.date;"

' *** create the query table name
strTableName = Format(Now(), "mmddyy")

DoCmd.RunSQL "SELECT copierList.billableCopies,
copierList.departmentContact, copierList.costPerCopy, copierList.total INTO "
+ strTableName + " FROM copierList;"

This is the code for the out put to function but I need to have a variable
name for each new table created
DoCmd.OpenForm "finishDialog"
DoCmd.RunMacro "Macro4"

This is the SQL that is probably the hardest once again 051705 is the new
table created for this month’s bills and the question is can I change that
table name i.e. 051705 will be 061805 next month so that users can search the
new billing file.

SELECT [051705].batchNumber, [051705].customerID, [051705].billableCopies,
[051705].previousMeter, [051705].departmentContact, [051705].costPerCopy,
[051705].total, *
FROM 051705
WHERE ((([051705].batchNumber) Like "*" & [Forms]![searchAll]![searchText] &
"*") AND (([051705].customerID) Like "*" & [Forms]![searchAll]![searchText] &
"*")

 

[Guest]

Ken,

I believe that if you implement the problem the way you described, you'll
have some tricky coding issues... not only on the database, but on the
programs that use your exports.

Instead of creating a brand new table for each day's billing information,
add a datestamp field to your billing table. incorporate the datestamp into
your primary key (or make it and the pk a unique index). All the billing
data will be stored in the same table then.

As for exports, it is pretty easy to manipulate criteria of queries when you
want to export the current day's billing information. I'd also keep the
exported billing data generic. If you have the date of each entry in the
file, the program that picks it up can use that, BUT won't have to change the
name of the file to be imported based on the date...

Make sense???

I have built a quasi-program using switchboard manager and various forms.
This program was created to streamline the data entry here where I work. Thus
I have four tables that data is entered into via the forms, then once a month
they need to create a bill for all the customers. Right now I’m using run
query commands to update everything, then I have a command button where the
user can choose which customer to bill, and I use a SELECT INTO statement to
make a new table for that month i.e. 05-05-05Billing is the table. I also use
this table to export a fixed field file that is the bill. Now I would like to
give the user the ability to search these newly created tables. So,
1 does anybody have any syntax for querying a table that will be created
months from now preferably with a command button and an entered text.
2 how to OutputTo or export table information from a table that has not been
named i.e. it will have the date as its name.
3 While I’m here and asking, can I pull info from a yet to be created table
to fill form i.e. the old total will come from the table made 04-05-05 and
then on the form the new total will be entered

This is the code for updates and create the new table for this month.

If Me.Frame22.Value = 2 Then

DoCmd.RunSQL "UPDATE WebClients SET batchNumber =
Forms!createBillsForm.batchNumber;"
DoCmd.RunSQL "UPDATE WebClients SET lastBillDate =
Forms!createBillsForm.date;"

' *** create the query table name
strTableName = Format(Now(), "mmddyy")

DoCmd.RunSQL "SELECT copierList.billableCopies,
copierList.departmentContact, copierList.costPerCopy, copierList.total INTO "
+ strTableName + " FROM copierList;"

This is the code for the out put to function but I need to have a variable
name for each new table created
DoCmd.OpenForm "finishDialog"
DoCmd.RunMacro "Macro4"

This is the SQL that is probably the hardest once again 051705 is the new
table created for this month’s bills and the question is can I change that
table name i.e. 051705 will be 061805 next month so that users can search the
new billing file.

SELECT [051705].batchNumber, [051705].customerID, [051705].billableCopies,
[051705].previousMeter, [051705].departmentContact, [051705].costPerCopy,
[051705].total, *
FROM 051705
WHERE ((([051705].batchNumber) Like "*" & [Forms]![searchAll]![searchText] &
"*") AND (([051705].customerID) Like "*" & [Forms]![searchAll]![searchText] &
"*")

 

[Guest]

Thanks so much to all, I went with the first idea of having a table that I
insert to, works well with new time stamp on each record.

Ken,

I believe that if you implement the problem the way you described, you'll
have some tricky coding issues... not only on the database, but on the
programs that use your exports.

Instead of creating a brand new table for each day's billing information,
add a datestamp field to your billing table. incorporate the datestamp into
your primary key (or make it and the pk a unique index). All the billing
data will be stored in the same table then.

As for exports, it is pretty easy to manipulate criteria of queries when you
want to export the current day's billing information. I'd also keep the
exported billing data generic. If you have the date of each entry in the
file, the program that picks it up can use that, BUT won't have to change the
name of the file to be imported based on the date...

Make sense???

I have built a quasi-program using switchboard manager and various forms.
This program was created to streamline the data entry here where I work. Thus
I have four tables that data is entered into via the forms, then once a month
they need to create a bill for all the customers. Right now I’m using run
query commands to update everything, then I have a command button where the
user can choose which customer to bill, and I use a SELECT INTO statement to
make a new table for that month i.e. 05-05-05Billing is the table. I also use
this table to export a fixed field file that is the bill. Now I would like to
give the user the ability to search these newly created tables. So,
1 does anybody have any syntax for querying a table that will be created
months from now preferably with a command button and an entered text.
2 how to OutputTo or export table information from a table that has not been
named i.e. it will have the date as its name.
3 While I’m here and asking, can I pull info from a yet to be created table
to fill form i.e. the old total will come from the table made 04-05-05 and
then on the form the new total will be entered

This is the code for updates and create the new table for this month.

If Me.Frame22.Value = 2 Then

DoCmd.RunSQL "UPDATE WebClients SET batchNumber =
Forms!createBillsForm.batchNumber;"
DoCmd.RunSQL "UPDATE WebClients SET lastBillDate =
Forms!createBillsForm.date;"

' *** create the query table name
strTableName = Format(Now(), "mmddyy")

DoCmd.RunSQL "SELECT copierList.billableCopies,
copierList.departmentContact, copierList.costPerCopy, copierList.total INTO "
+ strTableName + " FROM copierList;"

This is the code for the out put to function but I need to have a variable
name for each new table created
DoCmd.OpenForm "finishDialog"
DoCmd.RunMacro "Macro4"

This is the SQL that is probably the hardest once again 051705 is the new
table created for this month’s bills and the question is can I change that
table name i.e. 051705 will be 061805 next month so that users can search the
new billing file.

SELECT [051705].batchNumber, [051705].customerID, [051705].billableCopies,
[051705].previousMeter, [051705].departmentContact, [051705].costPerCopy,
[051705].total, *
FROM 051705
WHERE ((([051705].batchNumber) Like "*" & [Forms]![searchAll]![searchText] &
"*") AND (([051705].customerID) Like "*" & [Forms]![searchAll]![searchText] &
"*")