(Solved) Duplicated Records On Query...

[G-Hawk]

Hi everybody... I have loooking for some answers to my problem in the forum but I got nothing... so here I am....

Lets say that Table A has the following fields: Order,Rack, ID, INDate, EndDate.
And TableB has: Order, Model, Line, CaptDate, Qty.

Here is my query for a lis of racks used by Line:

SQL = "SELECT Rack, Sum(1) as TotalRack FROM TableA " _
   & ", TableB WHERE TableA .Orden = TableB.Orden" _
   & " AND TableB.Linea = '" Line "' GROUP BY Rack ORDER BY TotalRack DESC"

I found that I got repeated records if Orden is repeated in TableB, so the question is how can I get rid of those duplicated records?... I mean that if there is a match and TableA.Orden exist in TableB then it counts (thats OK) but if TableA.Orden exist twice then it counts twice (thats not good)...

Hope you can help me...

[G-Hawk]

Change the Question because I found the reason why I'm getting extra count data, still I don't know how to fixed...

[G-Hawk]

Well... in the end I found out how to avoid duplicated values (I saw some light because of a previous question answered by Si_The_Geek related to a similiar problem)

SQL = "SELECT Rack, Count(Rack) as TotalRack FROM TableA" _
     & ",(SELECT DISTINCT TableB.Orden, Linea FROM TableB) as NTB WHERE TableA.Orden = NTB .Orden " _
     & " AND NTB .Linea = '" & CombOp2.List(CombOp2.ListIndex) & "' " _
     & " GROUP BY Rack ORDER BY TotalRack DESC"

The problem was due to duplicated TableA.Orden in TableB so I just wanted to have non duplicated records on TableB and I came up with the solution... ...

Hope can be useful for someone..

C-ya!