Creating a Querry using a macro

[alexaronson]

Hello,

I am looking for a little help. I have 50 tables in a file that I want to append to another table in particular file. I do not need all the data, just selected data summed up. The fields I want are Item, Month, Year, Qty all grouped together with QTY summed.

Any help will be greatly appreciative.

Thanks
Alex in Memphis, TN

[MyBoo]

To create an APPEND query, you need to open the table that contains the data you want to append from, select the fields you want appended, set your criteria and point to the table you want to append to.

With that said, "50 tables in a flat file" does not sound like you are using MS Access.

Do you want to import these tables into Access and append them into one table? Where is the "another table in particular file"? Same "flat file" or in an Access db?

Under normal circumstances, you would NOT store SUMMED data... you let your query or report do the summing for you.

There are many ways to accomplish your task, but one would need additional information (outlined above) in order to provide the best or quickest solution for you.

[alexaronson]

MyBoo, thanks for inquiring on my delima.

I have 50 tables all in single access file not a falt file. This is how the data was given to me. The data in these tables are daily transactional data, as a result, the file is roughly 2.1 gigs and has no room for another table within Access. Therefore I need to combine the tables into a single table in another file for proper query work. All 50 tables have the same field names and are in the exact same order. To help with the file size, I want to group the data into monthly buckets.

I hope this gives you enough information.

[MyBoo]

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.

[Norie]

Alex

Have you compacted the database recently?

[alexaronson]

MyBoo,

I ended up doing what you said to do while waiting for your reply. I was hoping to learn a better way of doing it. Thanks for your time and help.

Norie,

How do I compact my file?

Thanks,

[MyBoo]

Tools/Database Utilities/Compact and Repair Database

[Norie]

Alex

Code can be created to create queries.

But I'm afraid, to me anyway, it isn't 100% clear what you want to do.:o

[alexaronson]

Norie,

I want to be able to make a macro that will create a query for each table that I have and to append those tables together.

[Norie]

Why not create a query that combines all the tables into one first?

That seems like the thing to do to me.

You can then create another query based on the one table.

PS Have you compacted?

If so has it made any difference in the size or performance of the database?