We help IT Professionals succeed at work.

How to fix duplicates in a query

56 Views
Last Modified: 2019-02-07
Hi


My query is return duplicates in columns Account name , Account code and BSID, how do I fix it , please this just one the queries I want to unionize so that I have a proper summary,see below:

SELECT DISTINCT tblCustomerInvoice.ShipDate, tblCostofSales.AccountID, tblCustomerInvoice.Reference, tblCustomerInvoice.InvoiceID, tblCostofSales.Debit, tblCostofSales.Credit, tblaccounts.AccountCode, tblaccounts.AccountName, tblaccounts.BSID
FROM (((tblCustomerInvoice INNER JOIN tblLineDetails ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) INNER JOIN tblSalesControls ON tblCustomerInvoice.InvoiceID = tblSalesControls.InvoiceID) INNER JOIN tblCostofSales ON tblCustomerInvoice.InvoiceID = tblCostofSales.InvoiceID) INNER JOIN tblaccounts ON tblCustomerInvoice.AccountID = tblaccounts.AccountID
WHERE (((tblCostofSales.[StatusTwo])="1"));



tb-2018-Analysis.png
Regards
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I see no dupes in that picture.
Nikoloz KhelashviliSoftware Developer

Commented:
Hi, can you give database tables schema you are using in select query?
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
This is a result of your including the invoice details in the query. Because you have multiple invoice items for each invoice, you will also have "duplicates' in the account name, code and BSID.
CERTIFIED EXPERT

Author

Commented:
Suppose I use Dlookup to add the following data from tblaccounts :

(1) Account code
(2) Account Name
(3) BSID

How do i avoid undefined name?
Ledger:Dlookup("AccountCode","tblaccounts","[Account]=&[Account]")

As you can see above I still get an error?????

Once the tblaccounts table is removed then the duplicate goes , that is why I want to settle on the Dlookup function

Regards

Chris
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
and BTW, using domain functions in queries, when they achieve the same results as a join to a table will be extremely slow.  There are reasons for using domain functions in queries, but I don't think it is appropriate in this case, based on your current explanation.
CERTIFIED EXPERT

Author

Commented:
Dale Fye

Thank you so much your point of not including invoice line details has sorted out the whole thing here.

Regards

Chris
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad I could help.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.