Querying 2 or more tables

[NewbieInCT]

I am trying to write a query that queries multiple excel files I download from Government websites. I am trying to create a database of over 14 different excel files that are for checking to see whether or not someones name is on a government list...



I have the query working with 1 excel file, but it freezes when I have 2... It could be the size, or how I have the files linked. I am having access look to an excel file, which can change daily/weekly.

If someone could help with the query, I would greatly appreciate it. I am a novice, and just haven't found what I am looking for here.

If you could also help clarify what I need to do to add another tab, that would be great.

The end result would to be to enter a search term, and if the word (or partial word) appears, to put the entire line in the report. For example, if I enter the word tent would give any words like attention, discontent, intention.

[jzwp11]

First, Access is not some high end spreadsheet it is a relational database and one of the rules of relational databases is that like data should be in 1 table. So, if the 14+ spreadsheets have similar data, that data should be migrated into 1 table in Access. You would only need 1 query.

Now if there is some logical join of the data in the 14 spreadsheets, you may be able to create 1 query after first joining the 14 spreadsheets together. If there is no logical join, then you have to have separate queries (1 for each spreadsheet/table)

[NewbieInCT]

OK. Unfortunately, the different government lists available on the web can not be joined too easily.
I know how to make individual queries. Could you help joining multiple queries into 1?

I am not going to have 14 different files, though I could. But let's say I have 2... I would query each first, using Like "*" & [All] & "*" but is there a way to join the multiple queries into 1? Almost like a macro? a marco would run each query, and then join all the results?

I am sure there might be a way, but it is over my head.

[jzwp11]

You can use a UNION query to join other queries together. You cannot create a UNION query using Access query design grid view; it must be created in SQL view


SELECT field1, field2 etc.
FROM table1

UNION ALL

SELECT field1, field2 etc.
FROM table2

The number of fields, the order of the fields and the I believe the datatypes of the fields from each of the individual queries in the UNION query must be the same.

Can the data from the various sources be put into 1 table? If the various spreadsheets just have names, I would say that putting the data into 1 table is the better approach. You can have a field that designates the source of the data in order to help distiguish from where the data was obtained.

[NewbieInCT]

jzwp11, thanks for the union query. I will try that. Unfortunately, the US gov calls a header one thing and the Canadian gov calls it something else, and the EU calls it something else. I could try to see if I can write a macro in Excel to match headers but again, I am not that familiar with this stuff.

If you can think of any other suggestions, I would love to hear them...

[NewbieInCT]

I was just thinking. if there was a way to tell Access to take column A from file 1, column D from file 2, and column AA from file 3, and make that a new column A in a new table, then we would have a perfect match...

Yes, the US gov, or other gov could change their file format, but I am trying a work around.. not write 100% perfect code for full implementation. As long as the us gov or other gov does not change their file layout, column A in one file, is the same as D in another as is AA in another, as is S in another....

a name is a name, a company name is a company name, an address is an address.......

Is this possible? The matching would take some time to do, but feasible.

[NewbieInCT]

to clarify, the field column names would align, but there is no relationship between what is in A and what is in D or AA... like a filter... it would almost need to be like take column D data and move it to the bottom of of Am and take what is in S, and move it to the bottom of A... merging large files into 1.. not a filter...

sorry for the repetition.

[jzwp11]

I was just thinking. if there was a way to tell Access to take column A from file 1, column D from file 2, and column AA from file 3, and make that a new column A in a new table, then we would have a perfect match...

This is possible in Access. You would create the table, then each time you get a new spreadsheet, you would import the data, run an append query (to take the data from the spreadsheet and put it into the appropriate field in your Access table) and then you can delete the imported data set. You would use the append query to map the field (column) of the imported data to the corresponding field (column) of the table you create. Of course you would still need separate append queries for each different data source, but you can save those and run them each time a new dataset comes in. In the event that the format of those datasets change, you would just modify the append query accordingly.

To create the append query: Start by first creating a SELECT query based on the imported data. Select the appropriate field (or fields). Now change the query type to append and select the destination table (the new table you created in Access). You can now map each selected field to the appropriate field in the destination table by specifying the field(s) in the Append to row of the query grid. Save the query and then run it.

[NewbieInCT]

thank you. I will give this a try, and see how good my basic skills are....

[NewbieInCT]

I am striking out, but the more I think about it, I am not sure if the column headers even really matter. If I am looking for company, Avocado Packaging, and I enter in the word Avocado, I really don't care where in any column the name Avacado appears... What matters to me, is if Avacado appears anywhere, that the word appears on the report...

So, all I really need to figure out, is how to merge all the files into 1 massive file and everytime the program loads, have the program start afresh by deleting any of the merges, and start afresh...

[jzwp11]

Once you get the data in one table, then you would run the search query on that 1 table & you will only have to look in 1 field (column). Putting all of the info in one table, makes the search much more efficient.

[NewbieInCT]

To finalized, I used concatenate to merge all fields together into 1 and then do the search. Works fine for my application!

[jzwp11]

Glad to hear that you came up with a solution!