How to compare two fields from two different tables.

[#1]

How to compare two fields from two different tables.

[Mark_Little]

Hi

Some more details would be helpful.

Normally you would need to join the tables and then compare if done in script.

But you could do in front end if you are using on a chart, with set analysis.

Mark

[#3]

In customer table,

where Document Type =2 then amount is sales_amount

In purchase table,

invoice = Document No in customer table then Amount in purchase table is purchase_amount

[#4]

     Purchase

Code	invoice No	Date	Amount
a1	QA/001	15/1/2010	45
b2	SA/002	8/2/2010	85
c3	LA/003	7/1/2011	95
a1	NA/005	3/5/2014	65
b2	DA/005	27/4/2015	15
c3	BA/005	19/3/2012	24
a1	KA/006	6/6/2013	72
b2	ZA/007	17/1/2015	38
a1	EA/008	7/8/2014	18
a1	GA/009	16/9/2015	91
b2	HA/010	5/9/2012	48
c3	JA/011	6/8/2013	46
a1	WA/012	24/03/2016	67
b2	IA/013	31/05/2015	92
c3	TA/014	23/05/2016	39

Customer Table

Code	Document No	Document Type	Amount	Date
a1	RA/001	1	10	1/1/2010
b2	SA/002	1	20	2/2/2010
c3	LA/003	1	30	1/1/2011
a1	NA/005	2	50	5/5/2014
b2	DA/005	3	60	10/4/2015
c3	BA/005	4	70	9/3/2012
a1	KA/006	5	80	6/6/2013
b2	PA/007	2	90	8/1/2015
a1	FA/008	2	45	7/8/2014
a1	GA/009	2	65	6/9/2015
b2	HA/010	2	82	4/9/2012
c3	JA/011	6	15	6/8/2013
a1	YA/012	7	24	25/03/2016
b2	IA/013	6	62	30/05/2015
c3	OA/014	8	34	21/05/2016

[Mark_Little]

HI,

If i am understanding correctly you could load both tables. then join the fields back you want to purchases,

So

Customer:

Load

     *

From Customers;

Purchases:

Load

     *

From purchases;

Left Join(Purchases)

Load

[Document No] as [invoice No]

Amount as [Sales Amount]

Resident Customer

Where [Document No] = 2;

Mark