create a query to filter on a field on all tables

Hello,

Access 2007

I would like to query each table in my database, on a specific criteria in field 1. The criteria is that the end of the string is .RAMB. The resultant query would should show F1 and F3 from each table in the database.

There are lots of tables - so it would be good if it would just look at all tables in the database, rather than specifying all.

How would i do this?

Regards,

Your request is a bit confusing. Do you wish to query each table individually? Can the tables be joined on a common field? Are you looking to include or exclude those records ending with .RAMB? Which field (does it have a name?) contains this information? Are the tables all set up the same? Please provide more information so that we can help you.

From the info provided, assuming you wish to include records ending in .RAMB from F1, then try this:

Please Login or Register  to view this content.

If you want to join all the tables, you will definitely have to id the table name in your SQL statement. When I need to edit SQL statements, I usually copy them to WordPad or NotePad and make the edit changes there and then copy it back to Access.

If you are getting a new .csv file everyday that you want to add to your query then I think an easier way would be to
1. Take your existing tables and do a Union Query one time.
2. With the Union Query as your record source, do a make table query and save it. This is your new record source. Delete the old tables that were imported from .csv. Delete the Union Query.
3. With each new .csv file you import, after importing append it to the new table you made and delete the imported .csv table.
4. Now run your query for the fields you want with the criteria you want.
5. Steps 3 and 4 can be automated with VBA so that all you need to do is click on a button and in an input box, identify the file to import.
6. Most important step. Make sure you backup your db before you start this in case you make a mistake you can reconstruct.

will it crash when i run the code?

You will get an error message and the Query will not run.
Alan

Originally Posted by alansidman

If you are getting a new .csv file everyday that you want to add to your query then I think an easier way would be to
1. Take your existing tables and do a Union Query one time.
2. With the Union Query as your record source, do a make table query and save it. This is your new record source. Delete the old tables that were imported from .csv. Delete the Union Query.
3. With each new .csv file you import, after importing append it to the new table you made and delete the imported .csv table.
4. Now run your query for the fields you want with the criteria you want.
5. Steps 3 and 4 can be automated with VBA so that all you need to do is click on a button and in an input box, identify the file to import.
6. Most important step. Make sure you backup your db before you start this in case you make a mistake you can reconstruct.

You will get an error message and the Query will not run.
Alan

Hi Thanks for the pointers.
I am not actually importing any of the files. The .csv files sit in a folder and i link to them using this code:

Please Login or Register  to view this content.

Does this make any difference with what you suggested?

You could probably do the same. I have not done this with linked tables but there is no reason that you couldn't build a make table query and then append it with the data from the latest linked table.

Make sure to do a backup before you attempt it. Post back with your successes or issues.

Did you create the new table with a make table query first? If so, what is your SQL statement for the append query to that table?

Hi, I have given up on this and chose to pursue this in a different way. Instead of creating a table for each csv file in my desktop folder. I have now created a single link to all files in the folder. So now i have one table with all .csv data in. Which i think may be better?

I now just use a where clause on the entire table to filter out what i want.

The code i used to link is below for your reference:

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



I have added the code tags in this case, but in the future, you should add them yourself.
Glad that you have resolved the issue.

If this solved your issue, please mark your thread as solved.