Forum Discussion

ygoshy's avatar
ygoshy
Copper Contributor
Apr 30, 2022

Query in access by linking

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
access
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    May 01, 2022
    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]
    • ygoshy's avatar
      ygoshy
      Copper Contributor
      May 01, 2022

      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?

       

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Apr 30, 2022

    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.

    • ygoshy's avatar
      ygoshy
      Copper Contributor
      May 01, 2022

      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.

Resources