MS Couple of questions from beginner

[arukort12]

Hello all,


at the beginning I need to say that I'm just starting my journey with access, I couldn't find answers on the internet though. I have few questions regarding ms access. I made a button which allows me to import excel file to access (for example: I import the first excel file - access makes first table with data from that file, I import the second file - the second table is made etc.). Now I want to make a one query with certain columns (just couple of columns out of ~40 to be honest) with data from all of the tables that I have imported in to access. Is there a way to 'automatize' that process? By pressing the button for example, which would 'update' (by update I mean just adding a specified data from the new imported excel file to the query) the query. If yes - where can I look for the solution?
Best regards,
Adam.

[ranman256]

save each new xl file to the same place everytime: c:\folder\file2import.xls
link it in the db as an external table
make an update query to update only the fields you want. (or an append query to import all)
put this query into a macro.

then the steps are:
1. save new file onto old
2. run macro
done

[arukort12]

save each new xl file to the same place everytime: c:\folder\file2import.xls
link it in the db as an external table
make an update query to update only the fields you want. (or an append query to import all)
put this query into a macro.

then the steps are:
1. save new file onto old
2. run macro
done

Thank you for the reply. Is there a way to link whole folder instead of linking each file singly?
I've linked all the tables that I had to link. Now, when I want to make an update query - the problem is that the query looks like: [Table1ColumnA, Table2ColumnA, Table3ColumnA] etc. instead of data from column A in table number 2 and 3 being under the last record of the [Table1ColumnA]... How can I change that? I would like to have a data of -for example- column A from all tables stacked in to the one column in the created query.

[ranman256]

if you have many files to do this, then youd need code to cycle thru the entire list,
reset the link,
import,
repeat.

I may have some.

[arukort12]

I see, I think I can do this but I'm still stuck at making the query... The data from the same columns appears next to each other instead of under the last record of the first column.

[ranman256]

i am at a loss on what youre talking about.
an example perhaps...

[arukort12]

When I want to create a query from the all tables that I have imported and I'm picking the field that I'm interested in I can't pick more than one table to that column - for example Surname from table A in the created query is on column called 'SurnameTableA', and column Surname but from the table B is not copied to the column SurnameTableA in the new query (under the last record of the surnames from table A) but it's pasted next to that column and it's called something like 'SurnameTableB'. I can't merge those two columns from two different tables although their names are the same.

[ArviLaanemets]

There is 2 types of queries using several tables as source:

a) tables in query are joined - and into different fields you get info from different source tables. Depending on join criterias, such queries have inner (one-to-one) or outer (ono-to-many) join. An Example.
SELECT a.Field2, b.Field3 From Table1 a, Table2 b WHERE a.Field1 = b.Field2

b) UNION queries, where data from several tables with similar info is added together. An Example.
SELECT a.Field1 As NewField1, a.Field2 As NewField2 FROM Table1 a UNION SELECT b.Field1 As NewField1, b.Field2 As NewField2 FROM Table2 b WHERE a.Field3 = b.Field3.

Probably you have to write the query directly into SQL Editor window.

[arukort12]

Ok, I did the b one type of query (UNION). But now I have another problem - what If I would import a new excel file (or link it) - how can I make query get updated with the new data that I have imported? Should I use a loop? If so, could you explain me how or how find the tutorial? I'm thinking of making a button which would run the query with the loop inside - is it possible? Because right now I have to 10x SELECT sentences in the code because I have 10x tables to merge and I don't know how to loop it - I'm a newbie unfortunately.

Thanks for responses.

[ArviLaanemets]

It looks like all your Excel tables have same or similar layout. So consider having one Excel file linked into Access database. You add new data (using some VBA code or simply copy-paste) at end of table there. In Access database, you create an Access table, and then you can always append data from linked Excel table into Access table (checking that added data are new info, not some duplicate). An example ofsuch append query:

INSERT INTO MyAccessTable (Field1, Field2, Field3)
SELECT a.Field1, a.Field2, a.Field3 FROM MyExcelTable a WHERE a.Field1 NOT IN (SELECT Field1 FROM MyAccessTable)