Distinct Syntax

guanfl

Registered User.
Local time
Today, 01:49
Joined
Mar 7, 2013
Messages
13
Hi all,

I'm having difficulty with the syntax in this query to remove duplicate data for the field "StocktransID". Any help is greatly appreciated.

Code:
SELECT DISTINCT tblStockTrans.StockTransID, tblItem.Brand, tblItem.Category, tblItem.SubCategory, 
tblItem.Model, tblItem.Description, IIf(TransTypeID=3,Quantity*-1,Quantity) AS Qty, 
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType, 
tblItem.Origin, tblOption.ParentID 

FROM tblTransaction INNER JOIN ((tblItem LEFT JOIN tblOption ON tblItem.ItemID = tblOption.ItemID) 
INNER JOIN tblStockTrans 
ON tblItem.ItemID = tblStockTrans.ItemID) ON tblTransaction.TransactionID = tblStockTrans.TransID 

GROUP BY tblItem.Brand, tblItem.Category, tblItem.SubCategory, tblItem.Model, tblItem.Description, 
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType, 
tblItem.Origin, tblOption.ParentID, tblStockTrans.StockTransID, tblStockTrans.Quantity, 
tblTransaction.location1
 
What is the problem, do you get any error message, then show it, both number and the message.
Else post some sample data in Excel- or MS-Access format.
 
No error message. Just not getting the results i want. I want to apply the "DISTINCT" function to the field 'stocktransid'. I believe the solution is including "DISTINCT" somewhere in the FROM statement but i'm lost with the syntax of writing it.
 
I could be wrong... but I think you will find your query is pulling what is distinct for the ID across all the other fields. What I mean is that if you want to pull distinct ID's, any extra information you pull will make that distinct stretch to the entire record. So what the query returns is a list of records where all records are distinct as a whole as opposed to items just your ID's.

Anytime I need to do this, I make one query pulling distinct items from whatever field I need (in your case, ID), and then join it with the other information as a left outer join.

Again, I could be wrong, but this is what I have noticed. I have resolved this issue with my above suggestion.
 
Do you have sample data to show us exactly what is a duplicate?
Do you have Normalized tables?
Do you have unique identifiers (Primary Key) on each record in the table?
Why are there duplicates?
 
The problems on the site appear to have caused posts to go missing, I replied to this thread explaining how Distinct worked and guanfl said that he would look at a multi query approach, all of that is missing.

Brian
 
As Brian mentioned some posts are missing. I managed to solve the issue though at the same time uncovering another problem. I managed to accomplish this by including and expression like "FIRST(tbloption.parentID) AS ...." on the ParentID field.

Just to shed some light; attached is what i thought i wanted. But i realise it would obscure my data as you would have to decide which of the ParentID data to keep.

Its back to the drawing board for me. Thanks for the help.
 

Attachments

  • Sample Data.jpg
    Sample Data.jpg
    34.9 KB · Views: 150

Users who are viewing this thread

Back
Top Bottom