CODEWITHSUNDEEP
sqlms-access
tsmvengy
tsmvengy

Reputation: 33

Access - Join two tables on two fields, get all records from table A

OK, so in MS Access I am trying to join two tables on two fields (customer ID and product type), have table A use a sum of each product type, and have all the records from table A so I can know what is missing from table B.

In table A, there are multiple records for each customer for each product type by year. But in table B there is only one record per product type. And in table B not all the product types are there.

Example Tables:

Table A:

Cust ID    ProdType   Year   Number
   1          A       2014     5
   1          A       2013     8
   1          B       2014     3
   2          A       2014    13
   2          C       2014     2
   3          B       2014     1
   3          C       2014     4

Table B:

                             Number 
    Cust ID    ProdType     Arrived
       1          A            5
       2          A           13
       2          C            2
       3          B            1
       3          C            2

Final Result should look like:

                        Sum of    Number
Cust ID    ProdType     Number    Arrived
   1          A           13        5
   1          B            3        
   2          A           13       13
   2          C            2        2
   3          A            1        1
   3          C            4        2

Upvotes: 0

Views: 3034

Answers (1)

Ravi Dhoriya ツ
Ravi Dhoriya ツ

Reputation: 4414

Try this,

MySQL Syntax

select a.cust_id, a.prodtype, sum(a.number), b.arrived
from table_a a left join table_b b on a.cust_id=b.cust_id and a.prodtype=b.prodtype
group by a.cust_id, a.prodtype

Here is DEMO (MySQL)

Ms-Access

select a.cust_id, a.prodtype, sum(a.number), b.arrived
from table_a a left join table_b b 
               on a.cust_id=b.cust_id and
               on a.prodtype=b.prodtype
group by a.cust_id, a.prodtype

Upvotes: 1

Related Questions