Combining 2 tables in access where data in one table is subset of other

[singhals]

I have 2 tables with following fields:

Table A
Customer_ID
Sales
Statecode

TableB
Customer_ID
Discount

Table A has something like 200 Customer IDs out of which about 50 Customer IDs also have a discount amount associated with them.

I now want to create a Master Table of 200 customers where I can have all the 4 fields, i.e. Customer_ID, Sales, Statecode and Discount. For those customers who didn’t get any discount, I want a null value in the Discount field.

However if I link the tables using a query, only the customers common on both the tables get selected. Could someone please help me out here.

Regards,
Sagar

[Darren McDonald]

Use a left join, ie all fields from tableA and only field from TableB where Customer_ID are equal.

Use a iif statement for the discount

iif(isnull(Discount),0,discount)

[singhals]

thanks! This did the trick.

[NeoPa]

I suspect this is due to your query joining the two tables with an INNER JOIN rather than the LEFT JOIN which would be required for your needs. See SQL JOINs for more details of this.

[singhals]

This is very helpful. Thanks!

I indeed did not know about various types of joins before today.

[NeoPa]

You're welcome Sagar :) Learning new things is always fun.