Remove "Duplicates" which are not really duplicates.

[MrDummy]

Hello,

I am gaining more and more experience in Access however I have come across the following issue which I haven’t been able to solve yet.
In a selection query I have the following result in one field:

1 9876 Combined with 2345
2 2346 Separated by 1234
3 6534 Issued by 2569
4 1234 Separated by 2346


5 5634 Issued by 9346
6 2345 Combined with 9876
7 3273 Boarded by 9346

Is this field the records 1 and 6 are the same and records 2 and 4 are the same; the numbers have only changed position from back to forth. The number always consists of four characters. I want to exclude these seemingly “Duplicates” from the list and the result must be:

1 9876 Combined with 2345
2 2346 Separated by 1234
3 6534 Issued by 2569
5 5634 Issued by 9346
7 3273 Boarded by 9346

Question is: Do you have an idea how to remove these "Duplicates". I know these are not really Access Duplicates but can't find a better word for it.

[ItsMe]

Things like this are typically managed with functions like, InStr(), Left(), Mid(), and InstrRev().
https://msdn.microsoft.com/en-us/lib.../ff823033.aspx

What does the actual data look like? Does an actual record contain text, like "Combined with"? You are going to need to find something to distinguish one number from the other. So maybe the fact that numbers are always 4 characters long, or they are always separated by a comma, etc. You may also be able to take advantage of the Split() function.

[CJ_London]

or solve it in your query before you generate the result. Also - does it matter which duplicate is excluded - you excluded records 4 and 6 - is this because they are further down the list or because the first number is smaller than the second

[Xipooo]

I think you should look into the EXISTS operator for your query. It allows you to check if a value exists in a subquery. Additionally you can use the NOT EXISTS to make sure the value doesn't already exist. You can also use table aliasing to refer to a table in your main query, from within your subquery. Without knowing more about your data tables and your query that pulls the data, I'm afraid this is the best we can do.

[MrDummy]

I have been trying a little bit but no joy so far.

Originally the numbers come from a Table of 3500 records and are randomly combined by the user. The text comes from a standard of 4 phrases. Theoretically there are 100000plus combinations. This devided by two because of the problem as shown above.

Would it help if I give you the SQL for this Union Query:

Select ContainerDataID, Deficiencies as DefFinal
From Query2
UNION Select ContainerDataID, ClassFin as DefFinal
From Query3
ORDER BY DefFinal DESC;

[CJ_London]

Would it help if I give you the SQL for this Union Query:

your union query does not appear to reflect the result you say per your original post.

Your original post has 1,2 3 or 4 fields - not at all clear which it is

and union query has 2 fields.

so, no, not a lot of help.

Suggest start at the beginning - start with the tables, then the sql for query1 and query2. Plus provide some example data in the table(s) and the result regenerated from query1 and query 2

[knarfreppep]

Hello,

I am gaining more and more experience in Access however I have come across the following issue which I haven’t been able to solve yet.
In a selection query I have the following result in one field:

1 9876 Combined with 2345
2 2346 Separated by 1234
3 6534 Issued by 2569
4 1234 Separated by 2346
5 5634 Issued by 9346
6 2345 Combined with 9876
7 3273 Boarded by 9346

Is this field the records 1 and 6 are the same and records 2 and 4 are the same; the numbers have only changed position from back to forth. The number always consists of four characters. I want to exclude these seemingly “Duplicates” from the list and the result must be:

1 9876 Combined with 2345
2 2346 Separated by 1234
3 6534 Issued by 2569
5 5634 Issued by 9346
7 3273 Boarded by 9346

Question is: Do you have an idea how to remove these "Duplicates". I know these are not really Access Duplicates but can't find a better word for it.

Let's say you have a table T with a field strResults with these values -
9876 Combined with 2345
2346 Separated by 1234
6534 Issued by 2569
1234 Separated by 2346
5634 Issued by 9346
2345 Combined with 9876
3273 Boarded by 9346

So create query Q1 -
SELECT strResults , Left(strResults, 4) AS A, Right(strResults, 4) AS B FROM T;

And another query Q2 -
SELECT strResults, IIF(A > B, A & B, B & A) AS C FROM Q1;

And a third query Q3 -
SELECT DISTINCT strResults, C FROM Q2;

Then a final query Q4 -
SELECT strResults FROM Q3;

[MrDummy]

Dear Knarfreppep,

I will try your way if this would work.

For Ajax,

I have made a sample database. Attached. Query4 is where it is all combined. here you see the following duplicate records:

For nr 23:

1817 away from 1395 and 1395 away from 1817
1794 away from 1395 and 1395 away from 1794

For nr 24

1395 away from 1794 and 1794 away from 1395

These are considered as "duplicates" and should be taken out in such a way that only one of them is left. It doesn't matter which one of the two.

Maybe better to look at the above first.

You may also see another issue coming up: 1395 separated from 1794 and 1395 away from 1794. The first one has a higher risk level (Sepaarted) then the second one (Away from). The second one must also be be taken out so only the higher risk items remain. The table SegTerms gives the risklevels (1 to 4). So in the end the Query4 should look like:

Nr23:

0104 separated by 1395
0104 spearted by 1794
0104 separeted by 1817
1395 separated from 1794
1395 separarated from 1817

Nr 24

1395 separated from 1794

[CJ_London]

had a quick look at your database and do not have a clue what it is trying to do. However you seem to have taken a tortuous route to get to query4. I recommend you go back to your original tables and start again

[knarfreppep]

Dear Knarfreppep,

I will try your way if this would work.

For Ajax,

I have made a sample database. Attached. Query4 is where it is all combined. here you see the following duplicate records:

For nr 23:

1817 away from 1395 and 1395 away from 1817
1794 away from 1395 and 1395 away from 1794

For nr 24

1395 away from 1794 and 1794 away from 1395

These are considered as "duplicates" and should be taken out in such a way that only one of them is left. It doesn't matter which one of the two.

Maybe better to look at the above first.

You may also see another issue coming up: 1395 separated from 1794 and 1395 away from 1794. The first one has a higher risk level (Sepaarted) then the second one (Away from). The second one must also be be taken out so only the higher risk items remain. The table SegTerms gives the risklevels (1 to 4). So in the end the Query4 should look like:

Nr23:

0104 separated by 1395
0104 spearted by 1794
0104 separeted by 1817
1395 separated from 1794
1395 separarated from 1817

Nr 24

1395 separated from 1794

You just need to join table SegTerms into the queries I suggested.

In fact, now that you are exposing more of your 'problem', it's becoming clear that your database design might need a little work (normalisation) ... your initial query should return a number not text ... something like -
SELECT FisrtValue, SegTerm, SecondValue FROM tableWhatever;

0104, 1, 1395
0104, 1, 1794 *
0104, 1, 1817
1395, 2, 1794
1395, 2, 1817
1794, 2, 0104 *

Assuming your SegTerms are in ASC or DESC order of importance, you'll be able to use Min() or Max() in the queries I suggested to solve your problem.

And maybe you should try to use numbers (104) instead of strings ("0104") for comparison efficiency.

PS I haven't bothered to look at your database because looking at your question tells me enough to know 'tortuous' will be right.