Query for same PCFN and different Carrier

Copper Contributor

See below. I am trying to write a query that the "PCFN" is the same that only includes a different "Carrier" name.  I keep getting that something like ( , etc is wrong. Can anyone look at what is wrong with the below to obtain my results?

 

ygoshy_0-1651512906286.png

 

 

 

10 Replies
it much easier to understand if we have any tangible table to work with.
if it is not much, can you upload the table.

Please review the attached sample of my Table. My previous screen shot shows all the fields within the actual data I have in my database. I made a copy of some fields to make a sample for your review. Table 1 shows the data that I made as a sample only. Theirs is over a million records in my database. My goal for the query is to come up with a result of the same "PCFN" and a different "Carrier" name. The result I am looking for is as shown in 'Copy of table 1".

 

Does this help?@ygoshy 

 

UPDATE: I tried several time to upload the access database sample and keep getting the error message "

Please review the attached sample of my Table. My previous screen shot shows all the fields within the actual data I have in my database. I made a copy of some fields to make a sample for your review. Table 1 shows the data that I made as a sample only. Theirs is over a million records in my database. My goal for the query is to come up with a result of the same "PCFN" and a different "Carrier" name. The result I am looking for is as shown in 'Copy of table 1".

 

Does this help?@ygoshy 

your first screen shot only show partial records from your table.
you archive your db to a .zip file then attach it here.

@arnel_gp  I have saved the access information to a excel database. Sheet 2 is the results I need to be. Sheet 3 is the data of various records it is derived from. Can you import into access to help in getting my desired results?

 

Thanks Tom

@ygoshy 

see Query1 and your final query, FinalQuery.

I am confused. Where is table "T" located in the linked query? Did you make it?
if you open Query1 in SQL view, you will see that T is just an Alias for the sub-query.

I added table 1 to a design query and then added table 1 again and went to properties of the second table one and renamed it then saving it. Now I have all the exact fields as named in table 1. How do I get CNT into the renamed table without changing the original table 1 design? ;@arnel_gp 

upload the db you have so i can see what you did.
do you believe i am poor in algebra especially on worded problem?

First, I do not mean to waste any ones time. My first example I only provided a small sample. Noe attached is a db with all fields in my accesss db with less than of 100 of the million within. I do not know it it makes a difference but I never use Primary Keys in my DB. The first 10 records are the ones I want in my query result. Note same PCFN # different carrier name. @arnel_gp