Noticed your location the other day... I'm in Germantown.
If your database is at 2.1 gig now, you are hitting the ceiling for Access and won't be able to put much more into that particular .mdb. Are you planning on putting monthly records to a history file - hence the monthly "buckets"?
At this point and time, with what I think I understand about your situation, here is what I would recommend: - FIRST... make a backup!
Create a new query, based on one of the 50 tables.
Select the fields you want to keep in your database.
Click on Query/Make Table... and enter a Name for the new table. Click OK.
Run the Make Table query. This will make a new table with the name you entered, copy the data rows from the old table to the new table, selecting only the columns you selected in the query.
Still in design mode of the query (we haven't saved and exited from design mode yet), click on Query/Append. Select the name of the table you just created.
Click on Query/Show Tables
Select another table of the 50 you want to combine.
Click the mouse pointer in the Table field for column 1 of your current query... select the second table... do this for each column in the query.
Click on the first table and delete it (top portion of your query pane). You should now have an Append query listing the fields from your Make Table query but with the 2nd table selected - at this point. (You could have closed/deleted the Make Table query and created a new Append query, but this is a bit shorter).
Run the Append query.
Select another table and make the same field changes. No need to click on Query/Append any more... just Query/Show Tables... select another table. (make sure you change the Field option to point to the new table and then delete the old table from the query)
Do this to all 50 tables.... sorry, only other way I know of doing this is a 50+ line macro or by VBA code.... either way, I'm not sure there's anything faster.
You will most likely have to delete the old tables as you move forward, due to the current size of the db.
After every table delete, click on Tools/Database Utilities/Compact & Repair database. (Save/Exit your Append query beforehand).
Compact and Repair Database again. Check db size... should be better now.
Once you have copied all 50 tables into one, you would create another (new) query, based on the table you just created and copy records by month to 12 seperate tables.
Click on Query/Make Table query.... enter the name you want for this table... example: Jan05...
Select All fields for your query.
Click your mouse pointer in the criteria field for your date field.
Enter - Between [Start Date] and [End Date]
Run the query.
You should be prompted for a Start Date... enter (for example
01/01/2005 and click on OK.
You should be prompted for an End Date... enter (for example
01/31/2005
When you click on OK, the query will create the Jan05 table and copy records that meet this criteria.
Change the Make Table to Feb05
Run query again, providing proper parameters.... until you have your 12 months table layout. You will need to decide on the naming convention of these 12 tables. using Jan05 - Dec05 might not fit the bill, depending on whether or not you want to store multiple years worth in each.
You would then create a delete query, based on your "combined" table and delete the records you moved to the monthly tables.
If you already know how to do the above, you could create a macro to do the same thing... all in one run.
If you don't, you will delete the current Monthly tables and lose that data.
Unless you plan to use thes queries in the future, you should delete them now as this should have been a one-time process.
WARNING!: This is NOT very efficient and only walk you through these steps because you are pretty much at the Access limit.
Let me know if this helps you out and if it does, how much space you recovered.
