Join/merge/mix tables problem

[niculaegeorge]

I paste this table from excel to access
Table 1

Fruit|freq
  Apples|7
  Bananas|10
  Grapes|4
  Strawberries|3

Then after a while I came up with another table looking like this:
Table 2

Fruit|freq
    Marshmallows|5
  Grapes|8
  Prunes|2
  Strawberries|3

Note that Grapes repeat in both tables.
I want to JOIN THE TWO TABLES in a single table, or to mix them together, to unify them, union, or to merge them, however it's properly called in MS Acces.
result:

Fruit|Table1|Table2|TOTAL
  Apples|7||7
  Bananas|10||10
  Grapes|4|8|12
  Marshmallows||5|5
  Prunes||2|2
  Strawberries|3||3

Fruit names, don't repeat, and look at grapes entry.
I've managed to do this with a pivot table in excel, but I'm interested in millions of entries, so I can't use excel because of its limit of 2 millions of rows.
Then I progressively update this end result table with similar tables. (New entries may occur)
Table 3

Fruit|freq
    Oranges|3
  Lemons|9
  Kiwifruits|4
  Bananas|2
  Blueberries|1

now my updated table looks like this:

Fruit|Table1|Table2|Table3|TOTAL
 Apples|7|||7
  Bananas|10||2|12
  Blueberries|||1|1
  Grapes|4|8||12
  Kiwifruits|||4|4
  Lemons|||9|9
  Marshmallows||5||5
  Prunes||2||2
  Oranges|||3|3
  Strawberries|3|||3

I want this result to become a new table, so that I can freely delete table1, table2 and table3 from my database to be left only with this end result table. Then the process continues forever where I again paste a new table4 from excel, that I want it to be merged/union/mixed/joined into this big one.
I'm a beginner in SQL, but I found a SQL Union Query code, on a site, after i tweak it a little bit, I manually join first 2 table and I try the code:

select fruit, NULL AS table1, NULL AS table2, freq
  from table3
  UNION
  select fruit, table1, table2, NULL AS table3
  from table1

but it doesn't work , and a ? symbol appear instead of numbers, I think because It can't join cells that are declared as null with cells that are numeric/numbers, and even if it worked I would have to change the code for every new future merging of tables.
How do I do this? With query design, make table ? Please, someone help me find a solution...

And if I don't upset anybody with my long/boring post, I have another problem. For reasons of space, and archiving/compressing all my data into a single table, I'm looking at something like let's say 10 millions of rows by 2000 column. Now the problem that I will probably be confronted with is the speed of operating with such a large single table. The question is: Is it faster, or more recommended to leave these tables (table1, table2, table3) individually separated, in my database, and each time I require to view a total freq of all the fruits in all the tables, run a query, a report or something?

[alansidman]

It seems to me that a simpler solution may be to do an append query to combine the tables and then run an aggregate query to get your totals. You seem to be going at it in a convoluted manner. Also, instead of copy and paste, why not import the data directly into Access from excel?

Alan

[niculaegeorge]

I stress out the fact that this is my first time in access, so I'm a beginner. I will stumble a lot. But I'm advanced in excel
I think I will move to access because of excel limitations, but I don't find it very attractive and user friendly, but hey…power comes with a price.

Ok. I've manged to make an aggregate query, with this table:
The steps that I did:
The above table was manually created in excel
1. External Data > Excel
2. File name > (I chose my table.xlsx) (import the source data into a new table…)
3. Create > Query Design
----a. Show table > Sheet1
----b. Below I select fields: Fruit, table1 freq, table2 freq
----c. Press the Greek letter sigma (Totals) from the top panel
----d. Now appears a new row called: "Totals:" I select "Sum" for the "table1 freq" and "Sum" for the "table2 freq" field
----e. I hit !(Run) button

This was the result:

I managed to create a "totals" field like this
1. In query1 switch to design view
2. In an empty colum below, I click on the first "Field:" row
3. Then the "builder" button activates in the top panel
4. In the window that appears I chose from the list Tables > Sheet1 then from the neighbor list > table1 freq > hit paste > hit the & button > I select table2 freq > hit paste
5. The expression I build was this: [Sheet1]![table1 freq] & [Sheet1]![table2 freq]
6. Then in the third row "Total:" I chose "Sum" (for this expression)
7. Hit run

This was the result.

Everything nice and beautiful till now.
Success! But not complete.
I had another problem on how to import/append to a new colum each time.

In other terms my main table (to which I am appending) gets a new Colum with each import/append operation.

I've tried to normally import/append from excel, but each time the rows from table2 get's added at the end of the table and the frequencies get mingled. I thought that I had to fill with 0 the rows for the rest of the not yet imported tables.
So I've got the genius idea to create the colum "table2freq" before importing/appending the new table, so that the new entryes goes to this colum, and the rest will be null and they won't be mixed.
Problem solved!
Thank you very much for support and direction. I am very grateful.
I have a small question. For reasons of space, and archiving/compressing all my data into a single table, I'm looking at something like let's say 10 millions of rows by 2000 columns.
Now the problem that I will probably be confronted with is the speed of operating with such a large single table.
The question is: Is it faster, or more recommended to leave these tables (table1, table2, table3) individually separated, in my database, and each time I require to view a total freq of all the fruits in all the tables, run a query?