How to fix duplicates in a query

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I see no dupes in that picture.

Commented:
Hi, can you give database tables schema you are using in select query?
Dale FyeOwner, Dev-Soln LLC
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
I think you are confused:
 
  • If you wish the invoice details, you will get the invoice main info listed for each invoice line.
  • If you wish one row only for each invoice, you can't have the invoice details, except if you aggregate the fields of these you wish to include, like summing the amounts.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
As you can see above I still get an error?????
No, we cannot see, you did not provide an image which contains an error.
But the point is, that whether you include the table in the query, or use a domain function like DLOOKUP() you will get "duplicates" in the AccountName, AccountCode, and BSID columns.

With regard to your DLOOKUP issue, it might look like this if [Account] is a text field
Ledger:Dlookup("AccountCode","tblaccounts","[Account]='" & [Account] & "'")

Open in new window

or like this, if [Account] is numeric:
Ledger:Dlookup("AccountCode","tblaccounts","[Account]=" & [Account])

Open in new window

Dale FyeOwner, Dev-Soln LLC
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.
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
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad I could help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial