[The1Bill]

I have a database that is built from Crystal reports. I get one report a day, and export it to Access. While it beats manually entereing all of this data back in, the table that it produces is a bit cumbersome. I'm able to grab what I need with a query, though.

So, now, I have a table with three columns... DE_Classcode, DE_Weight, and GF1_TotalFreight.

I get one of these per day, so I save each one of them as a table, using the date. June 10th's table is called 061004, for example.

At the end of each month, I need to generate a report, based on the whole month. I need a way to combine all of these tables so that I can do the math for the whole month.

What I have been doing is exporting it to Excel, combining all of the tables, and importing it back into Access. I would like to be able to use a UNION ALL statement, but typing in each table name is cumbersome, as is typing in each field name for each table. Since all of the table names for a month are only two digits apart, I am thinking that there must be an easy way to use a wildcard in a SQL statement, but I am not sure how I would do that.

Worst comes to worst, I will revert back to exporting to Excel.

Thanks for reading!

-=B-=

[LKBrwn_DBA]

Change your design to something like this:

1) Create a 'History' table as: DE_Date, DE_Classcode, DE_Weight, and GF1_TotalFreight.
2) Create a 'Daily' table same as current daily tables structure except do not add the date to the table name.
3) Each day import into your 'Daily' table, and then 'Insert' the daily data into the 'History' table with a statement like:

INSERT INTO DE_HISTORY
SELECT p_date, d.DE_Classcode, d.DE_Weight, d.GF1_TotalFreight
FROM DE_DAILY_TRANS as d;

Here when MS Access does not recognize p_date as a column name, it will prompt the user for a value, enter the correct date of the transactions.

4) Now you can generate all reports directly from MS Access!

[The1Bill]

Thanks for the reply. One remaining question, though. What's the best way to dump a lot of little tables into one great big huge gigantic table?

-=B-=

[LKBrwn_DBA]

Assuming the name of the table is the date, then you could create a query like:

Select 'INSERT INTO DE_HISTORY SELECT ', Name,
', DE_Classcode, DE_Weight, GF1_TotalFreight FROM ',Name,';'
From MSysObjects
Where Type=1 And Left(Name,4) <> 'Msys'
Order By Name;

Save it to a text file and remove the tables which you do not want to 'INSERT' into the history!

Then execute the script (I would not know how to do it in Access).