CODEWITHSUNDEEP
sqlmergeunion
n_urb
n_urb

Reputation: 21

Merging of multiple tables with Microsoft Access

On work, I have a problem with merging multiple tables into one table with Microsoft Access. So what I have is one Microsoft Access DB-file with multiple tables.

We can assume that each table only consists out of two columns. CustomerID and numb_of_purchases. I have one table for each month and want to merge all those tables in one big table, so for example for two months:

TableJan
    Customer_ID  numb_of_purchases_Jan
    1                     10
    2                     53
    3                     98
    4                     101

and

TableFeb
    Customer_ID  numb_of_purchases_Feb
    3                     86
    5                     5

are supposed to be merged into a bigger table, which looks like:

  Customer_ID  numb_of_purchases_Jan  numb_of_Purchases_Feb
    1                     10                   0
    2                     53                   0
    3                     98                   86
    4                     101                  0
    5                     0                    5

how would you solve such a problem? At first I thought it would be very easy by, for example, using the UNION function, so I tried to write the following query:

SELECT TableJan.Customer_ID, numb_of_purchases_Jan
FROM Table_Jan

UNION

SELECT TableFeb.Customer_ID, numb_of_purchases_Feb
FROM Table_Feb;

Executing this query was something along these lines:

Customer_ID  numb_of_purchases_Jan
1                     10
1                     0
2                     53
2                     0
3                     98
3                     86
4                     101
4                     0
5                     5

So essentially, I am missing an entire column compared to the desired output and instead the values of numb_of_purchases_Feb are stored in the numb_of_purchases_Jan-column.

I would greatly appreciate if someone could point out any mistakes in my UNION-command or have an other, maybe more simpler solution to the given problem.

Upvotes: 0

Views: 2091

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

MS Access doesn't support full outer join. But you might be able to use union all and group by:

SELECT Customer_ID,
      SUM(numb_of_purchases_Jan) as numb_of_purchases_Jan,
      SUM(numb_of_purchases_Feb) as numb_of_purchases_Feb
FROM (SELECT TableJan.Customer_ID, numb_of_purchases_Jan, 0 as numb_of_purchases_Feb
      FROM Table_Jan 
      UNION ALL 
      SELECT TableFeb.Customer_ID, 0, numb_of_purchases_Feb 
      FROM Table_Feb 
     ) c
GROUP BY Customer_ID
ORDER BY Customer_ID;

Then, you should fix your data processing. There is no reason to store the same information in tables that have the same structure. All this data should be in a single table, with an additional column that specifies the time period.

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

@Himanshu provided a good start of reply to your question. But the design you requested is inefficient in my opinion. I would rather use something like:

SELECT TableJan.Customer_ID, numb_of_purchases_Jan as numb_of_purchases, #1/1/2019# as Period 
FROM Table_Jan 
UNION ALL 
SELECT TableFeb.Customer_ID, numb_of_purchases_Feb as numb_of_purchases, #2/1/2019# as Period 
FROM Table_Feb;

You would then end up with a 3 fields dataset:

  • CustomerID
  • numb_of_purchases
  • period

from which your can easily calculate Monthly, Quarterly, Yearly sales....

Do you have any good reason to denormalize ? Millions of rows ?

Upvotes: 0

Himanshu
Himanshu

Reputation: 3970

UNION is to give rows combined on same no of columns and types.

As your o/p contains no of columns different from that of your input hence, you need to use Join 

   SELECT a.Customer_ID, 
      a.numb_of_purchases_Jan,
      b.numb_of_purchases_FEB
     FROM TABLE_JAN a LEFT 
    JOIN TABLE_FEB b on 
     a.Customer_ID=b.Customer_ID

Upvotes: 1

Related Questions