Query in access by linking

Occasional Contributor

I have data the looks like the below. I want to create a query that will give me a result like the second one. I want all "PCFN" numbers that are the same but the "Carrier" is different as shown in second example below. I have tried to link the tables by PCFN # but do not know the criteria to enter in the Carrier column to get the results in the second table below. Thanks for any help provided. Tom

 

Oct 2016 POD FIELD AND ADD DODAAC & PORTS

Paid Date      Carrier                                               PCFN    Invoice #

1/11/2021MATSON NAVIGATION COMPANY, INC.2578998214855
6/7/2021MATSON NAVIGATION COMPANY, INC.2698294952360
9/24/2021MATSON NAVIGATION COMPANY, INC.2779174233331
12/13/2021MATSON NAVIGATION COMPANY, INC.2845347532787
2/18/2022PASHA HAWAII TRANSPORT LINES LLC288045MC174E288045
5/10/2021SEALIFT INC OF DELAWARE258876258876 - 2020
12/27/2021SEALIFT INC OF DELAWARE265450265450 - 21
10/21/2021SEALIFT INC OF DELAWARE271306271306 - 21

 

Copy Of Oct 2016 POD FIELD AND ADD DODAAC & PORTS

Paid Date      Carrier                              PCFN     Invoice #

12/27/2021SEALIFT INC OF DELAWARE265450265450 - 21
12/27/2021SEALIFT INC OF DELAWARE265450265450 - 21
12/27/2021SEALIFT INC OF DELAWARE265450265450 - 21
12/27/2021SEALIFT INC OF DELAWARE265450265450 - 21
12/27/2021SEALIFT INC OF DELAWARE265450265450 - 21
12/27/2021SEALIFT INC OF DELAWARE265450265450 - 21
3/24/2021US OCEAN265450PC265450
3/24/2021US OCEAN265450PC265450
3/24/2021US OCEAN265450PC265450
3/24/2021US OCEAN265450PC265450
4 Replies

@ygoshy 

Is this a table? What is the name of the table? 

 

There are values in the second copy that do not appear in the first copy of the table. Where do they come from?

 

In the second copy there are multiple rows that appear to be identical. Why?

 

In trouble-shooting problems like this context and detail are really important to conveying the situation to others who can't see the source, and have no idea what the purpose or goal is. Help us by providing that context and detail. Thank you.

you may try:

SELECT [yourTableName].*
FROM [yourTableName] INNER JOIN (SELECT PCFN, COUNT("1") AS CNT FROM (SELECT [yourTableName].Carrier, [yourTableName].PCFN
FROM [yourTableName] GROUP BY [yourTableName].Carrier, [yourTableName].PCFN) GROUP BY PCFN) AS T
ON [yourTableName].PCFN = T.PCFN WHERE T.CNT > 1
ORDER BY Carrier, [Paid Date]

@George Hepworth I am trying to come up a query result from the same table that is as shown in the second example. This table has 50 or more columns and has almost a million rows. The second table is the same as the first one. It is a copy of it.

@arnel_gp I am not a expert query writer and a little confused by your response. My table has many columns and almost a million rows in it. See below this is how I tried to do this query. The goal is the have a result where the PCFN is the same but the Carrier is different name. Will this additional information help your investigation to solve my issue?

ygoshy_0-1651411607451.png