Create a table with code

[Sean]

I have a spreadsheet that is connected to my database and it is the raw data
for all of the query's in the database. This spreadsheet changes daily so I
have a bit of code that creates a table from it each day and the table name
changes daily as the current date is added onto the table name. Example:
Data20090727 and today's would be Data20090728. What I would like to be able
to do thru code is create a new table daily off the previous day's backup
table (data20090728) but how do I write the code so it know to choose the
previous days table?

 

[Marshall Barton]

I have a spreadsheet that is connected to my database and it is the raw data
for all of the query's in the database. This spreadsheet changes daily so I
have a bit of code that creates a table from it each day and the table name
changes daily as the current date is added onto the table name. Example:
Data20090727 and today's would be Data20090728. What I would like to be able
to do thru code is create a new table daily off the previous day's backup
table (data20090728) but how do I write the code so it know to choose the
previous days table?

I think(?) this will produce that file name:

"Data" & Format(DateAdd("d", -1, Date()), "yyyymmdd")

 

[Sean]

Thanks Marshall. Code you give me your thoughts on the following?

I have a query, call it my summary query, and it's built on top of ten other
query's that do not relate to each other, i.e. no joins. This allows me to
have 1 set of results in one query. Each of the 10 query's is summarizing
data which produce 1 field of data and that field is either a number or a
percentage, i.e. LateOrders 253, Orders shipped 450, Late Percent 33%,
KeyOrders 45, etc. Now, lets say there were no Key orders that day and the
Keyorder Query (stand alone query but is also 1 of the 10 query's for the
summary query) did not produce any data when you run it by itself. When the
summary query runs, even though the other 9 query's would produce results if
you ran them independently , they do not show any results in the summary
query because the KeyOrders query haad no results to display. So somehow if
1 quey has no results it prevents the others from showing results. Any
thoughts on how I could set this up differently? Could I set up some code to
do it and populate a table then query off the table? Can you give me an
example of the code as I am new to the coding aspect. Thanks so much and
below is my summary query for reference. The fields are differnet than my
explanation above.

SELECT NegativeATP.NegATP, ShipComplete.Ship_Complete,
TotalLines.TotalLines, ShippableLines.Shippable,
DeliveryGroup_Shippable.DeliveryGroup, TJ_UnShippable_Percentage.Percent AS
TJ_Percentage, GHO_UnShippable_Percentage.Percent AS GHO_Percentage,
BB_UnShippable_Percentage.Percent AS BB_Percentage,
BP_UnShippable_Percentage.Percent AS BP_Percentage
FROM NegativeATP, ShipComplete, TotalLines, ShippableLines,
DeliveryGroup_Shippable, TJ_UnShippable_Percentage,
GHO_UnShippable_Percentage, BB_UnShippable_Percentage,
BP_UnShippable_Percentage;