Find data in a Table

B

Bob

Hi all,
Please I need an answer asap.Very appreciated....

There are 2 col named TaxID and Vendor#
I want to find. Show all records where TaxId is the same and Vendor# is
diffrent.
E.G
TaxID Vendor#
123 5
123 6
123 7
 
R

Roger Carlson

SELECT DISTINCT [TaxID], [Vendor#] FROM TableName
ORDER BY [TaxID], [Vendor#]

(Insert your actual table name for TableName)
 
B

Bob

Thank you, it workd. I want to delete the records selected. Can I delete
these records insted of selecting them? THANKS
Bob
 
B

Bob

What I need to do is In the list below Delete all records where TaxId is the
same and Vendor# is diffrent.Your Query includes Vendor 1 which I want to
keep and delete only 2,3,4.Sorry it is confuzing to me too.I just
clerified.Could you help?Thanks a lot!!!
TaxID Vendor#
123 1
123 2
123 3
123 4
123 1
123 1
123 1
123 1
 
J

John Spencer (MVP)

How do you know WHICH TaxID Vendor# combination to keep? The one that appears
the most times, the lowest Vendor#, etc. This may be one of those problems
where you are going to have to apply the human brain. If you have some rule to
decide then we can try to develop a solution.

To identify those taxIds with more than one Vendor#. You might be able to do
this in qone query if you hadn't used special characters (#) in your field name.

SELECT TaxID, Vendor
FROM YourTable
WHERE YourTable.TaxID IN
(
SELECT DRecs.TaxID
FROM
(SELECT Distinct TaxID, Vendor
FROM YourTable) as DRecs
GROUP BY DRecs.TaxID
HAVING COUNT(DRecs.TaxID) > 1)

Since you did you will need to build stacked queries solution and you may end up
with a query that is not updatable.

QueryOne: Named DRecs
SELECT Distinct TaxID, [Vendor#] as Vendor
FROM YourTable

QueryTwo: Named MultiVendor
SELECT TaxID
FROM DRecs
GROUP BY TaxID
HAVING Count(TaxID)>1

QueryThree:
SELECT TaxID, [Vendor#]
FROM YourTable INNER JOIN MultiVendor
ON YourTable.TaxID = MultiVendor.TaxID


OR Try
SELECT TaxID, [Vendor#]
FROM YourTable
WHERE TaxID in
(SELECT TaxId FROM MultiVendor)
 
Top