query returning duplicate rows : Invoicing (1 Viewer)

haardshah

New member
Local time
Today, 16:01
Joined
Apr 18, 2020
Messages
4
I have Two tables...
Capture.PNG



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?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:31
Joined
May 21, 2018
Messages
8,463
SQL:
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] & "*")
   )

I formatted this using an online formatter to make it easier to see. You have 2 inner joins and 1 left join on queries we do not know. So it may be hard to answer without understanding those queries. Can you post a sample db?
 

haardshah

New member
Local time
Today, 16:01
Joined
Apr 18, 2020
Messages
4
I share result with you...


001.jpg




002.jpg
 

Attachments

  • 001.jpg
    001.jpg
    121.7 KB · Views: 143
  • 002.jpg
    002.jpg
    100.7 KB · Views: 224

mike60smart

Registered User.
Local time
Today, 10:31
Joined
Aug 6, 2017
Messages
1,899
Hi

You will need to upload a zipped copy of the database
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 19, 2002
Messages
42,970
You are joining on data field to data field. Normally, a join would be from data field to PK. Data field to data field almost always leads to "duplication".

An Inner Join (which is what you have) returns a row from tbl1 for each match in tbl2 so if there are 9 records for PSA-063 in tbl1 and 4 records with that value in tbl2, then you will end up with 9 * 4 or 36 rows in the result set for PSA-063.

There is either something wrong with your data or the logic of your join.
 

Users who are viewing this thread

Top Bottom