How to fix duplicates in a query

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
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
I see no dupes in that picture.
Nikoloz KhelashviliSoftware DeveloperCommented:
Hi, can you give database tables schema you are using in select query?
Dale FyeOwner, Dev-Soln LLCCommented:
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.
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor 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
Gustav BrockCIOCommented:
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.
Dale FyeOwner, Dev-Soln LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Dev-Soln LLCCommented:
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.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor 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 LLCCommented:
glad I could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.