I have Two tables...
Field2(from table 4) is Invoice No nad
Field1(from qry_invoice_main_sub) is items of invoice
Now suppose i create 1 Invoice with 5 items ...
And when i generate query with the help of form .... it shows me 5 duplicate entries.
SELECT Table4.Field1 AS FirmCode, Table4.Field2 AS InvoiceNo, Table4.Field3 AS Incrementer, DateValue([Table4]![Field4]) AS InvDate, Table4.Field5 AS nPartyid, Table4.Field6 AS CltName, Table4.Field7 AS CltAddress, Table4.Field8 AS CltMNo, Table4.Field9 AS CltE, Table4.Field10 AS CltGstn, Table4.Field11 AS CltStateCode, Table4.Field12 AS FirmindID, Table4.Field13 AS InvNote, Table4.Field14 AS InterState, qry_invoice_firm.MyName, qry_invoice_firm.MyAddress, qry_invoice_firm.MyGSTNumber, qry_invoice_firm.BankName, qry_invoice_firm.BankCode, qry_invoice_firm.ACNo, qry_invoice_firm.BankIFSC, qry_invoice_company_master.[Company Name], qry_invoice_company_master.[Mobile Number], qry_invoice_company_master.Address, qry_invoice_company_master.[Email Address], qry_invoice_firm.field2 AS MyMobNo, qry_invoice_firm.field3 AS MyEmailId, qry_invoice_firm.field4 AS MyWebsite, qry_invoice_firm.field5 AS InvType, qry_invoice_firm.field7 AS InvoiceFooter, qry_invoice_firm.field10 AS MyPan, qry_invoice_firm.Field12 AS MyTag
FROM (qry_invoice_company_master INNER JOIN (qry_invoice_firm INNER JOIN Table4 ON qry_invoice_firm.field1 = Table4.Field1) ON qry_invoice_company_master.[Company Name] = Table4.Field6) LEFT JOIN qry_invoice_main_sub ON Table4.Field2 = qry_invoice_main_sub.Field1
WHERE (((Table4.Field2) Like "*" & [Forms]![frm_invoice]![DInvNotxt] & "*"));
Can anyone help?
Field2(from table 4) is Invoice No nad
Field1(from qry_invoice_main_sub) is items of invoice
Now suppose i create 1 Invoice with 5 items ...
And when i generate query with the help of form .... it shows me 5 duplicate entries.
SELECT Table4.Field1 AS FirmCode, Table4.Field2 AS InvoiceNo, Table4.Field3 AS Incrementer, DateValue([Table4]![Field4]) AS InvDate, Table4.Field5 AS nPartyid, Table4.Field6 AS CltName, Table4.Field7 AS CltAddress, Table4.Field8 AS CltMNo, Table4.Field9 AS CltE, Table4.Field10 AS CltGstn, Table4.Field11 AS CltStateCode, Table4.Field12 AS FirmindID, Table4.Field13 AS InvNote, Table4.Field14 AS InterState, qry_invoice_firm.MyName, qry_invoice_firm.MyAddress, qry_invoice_firm.MyGSTNumber, qry_invoice_firm.BankName, qry_invoice_firm.BankCode, qry_invoice_firm.ACNo, qry_invoice_firm.BankIFSC, qry_invoice_company_master.[Company Name], qry_invoice_company_master.[Mobile Number], qry_invoice_company_master.Address, qry_invoice_company_master.[Email Address], qry_invoice_firm.field2 AS MyMobNo, qry_invoice_firm.field3 AS MyEmailId, qry_invoice_firm.field4 AS MyWebsite, qry_invoice_firm.field5 AS InvType, qry_invoice_firm.field7 AS InvoiceFooter, qry_invoice_firm.field10 AS MyPan, qry_invoice_firm.Field12 AS MyTag
FROM (qry_invoice_company_master INNER JOIN (qry_invoice_firm INNER JOIN Table4 ON qry_invoice_firm.field1 = Table4.Field1) ON qry_invoice_company_master.[Company Name] = Table4.Field6) LEFT JOIN qry_invoice_main_sub ON Table4.Field2 = qry_invoice_main_sub.Field1
WHERE (((Table4.Field2) Like "*" & [Forms]![frm_invoice]![DInvNotxt] & "*"));
Can anyone help?