I have a bankacctrans table. I have vendtrans, vendinvoicejour tables.(Banktrans related to vendtrans by field voucher, vendtrans related to vendinvoicejour by field vooucher) I need to create a view/form which will show few fields from above 3 tables. I need to get all records from bankacctrans table with transactiontype as "abc", eventhough it doesnt have related record in vendtrans/VendInvoiceJour. Ex: i need to get all records from bank trans table with transtype as "abc" 1.even if it has related record in vend trans and vendinvoicejour 2.even if it has related record in vend trans and not in vendinvoicejour 3.even if it doesnt have any related record in vendtrans,vendinvoicejour.
Asked
Active
Viewed 223 times
0
-
1Sounds like an outer join to me, have you tried that? – FH-Inway Mar 12 '16 at 06:52
-
What have you done so far? – Jan B. Kjeldsen Mar 12 '16 at 13:34
-
I tried inner join like this Btrans - vendtrans- vendinvjour . This is not getting records if there is no record in vendtrans for btrans. I tried outer join but it didnt work too. i created query, then created view. – Raas Mar 12 '16 at 17:00
-
Could you describe in more detail how you tried the outer join and what did not work? – FH-Inway Mar 14 '16 at 12:27
-
There is not direct relation between the `BankAccountTrans` and `VendTrans` table. You would have to analyze (probably with an functional expert) what relation of these two tables would satisfy your requirements. One possibility is to include table `LedgerJournalTrans`. – FH-Inway Mar 15 '16 at 08:15
-
I tried from scratch again. Outer-join worked for me. Thanks – Raas Mar 16 '16 at 02:41