Solved How to filter a specific column in MS Access Query

[nector]

I want to filter the column called TPIN so that all duplicates are eliminated , how do I do it?

SELECT tblCustomers.CustomerID, tblCustomers.Company, tblCustomers.CustStatus, tblCustomers.TPIN
FROM tblCustomers
WHERE (((tblCustomers.Company) Is Not Null) AND ((tblCustomers.CustStatus) Is Null Or (tblCustomers.CustStatus)<>"2"))
ORDER BY tblCustomers.Company;

 

[GPGeorge]

Your query include the CustomerID, which must be unique for each record in the table, assuming that it's name reflects its status as the primary key, of course.

Given that assumption, you can't include it in the query directly if you want to select only customers with the same TPIN (whatever that means).

In fact, I can't quite figure out how this would work. Please explain the context and how this result would be useful.

 

[mike60smart]

I want to filter the column called TPIN so that all duplicates are eliminated , how do I do it?

SELECT tblCustomers.CustomerID, tblCustomers.Company, tblCustomers.CustStatus, tblCustomers.TPIN
FROM tblCustomers
WHERE (((tblCustomers.Company) Is Not Null) AND ((tblCustomers.CustStatus) Is Null Or (tblCustomers.CustStatus)<>"2"))
ORDER BY tblCustomers.Company;

Can you upload the Db

 

[nector]

The Dd is mysql its on cloud

 

[mike60smart]

OK

 

[plog]

Can you demonstrate what you want to occur? Suppose you have the below sample data, please list the data you expect your query to return:

TPIN, ID
abc, 1
def, 2
ghi, 1
abc, 2
def, 2

 

[nector]

At the moment users captures one customer twice hence the need for a filter

 

[nector]

Can you demonstrate what you want to occur? Suppose you have the below sample data, please list the data you expect your query to return:

TPIN, ID
abc, 1
def, 2
ghi, 1
abc, 2
def, 2

If TPIN = 100 , then even if in a combo there 5 customers with 100 , the combo must return only one

 

[GPGeorge]

At the moment users captures one customer twice hence the need for a filter

So you actually want to surpress the fact that some Customers are in the table twice (or more)?

The real problem, it seems, is that there is no constraint on the way customer are entered. If your customers are entered twice, that's a serious data integrity problem. Filtering the bad data to hide it from view isn't the way to solve that problem. You need to remove the duplicated customer records, then apply an index on the TPIN field to prevent duplicates from being entered in the future.

 

[CJ_London]

If TPIN = 100 , then even if in a combo there 5 customers with 100 , the combo must return only one

Why can’t you just answer the question, not describe something else?

Or provide your own version of what you have and the required outcome

From your description I would suggest an aggregate query

 

[plog]

I gave you a bunch of data, give me a bunch of data back that represents what you would like to appear in the final query

 

[Mark_]

You would be looking for Select Distinct to return just the TPINs.
More important, you'd want a COUNT of TPINs in a query so you can return all CustomerIDs that have redundant TPINs. This would be used to help clean up your data. Otherwise you'll be leaving bad data in your system and run into further issues where the wrong customer is referenced by a TPIN.

 

[nector]

Many thanks to all of you I appreciate your help its well noted