If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Make table query combining records of two tables into a third tabl
I thought I posted this yesterday, but somehow it did not get into the forum.
I apologize if it is there and I just cannot find it, and this is ends up as a dupe. I will be glad to delete it if I can find it. I have tried to simplify my question too. Here is my problem, related to a project I am working on. I have two tables. 1) FPDS: fields are Duns, Company Name, Transaction Amount Source: Federal Procurement Data System (https://www.fpds.gov/) (a public site) Approximately 174,000 records; fiscal years 05, 06 and 07 2) CCR: fields are DUNS, Company Name, Annual Receipts, Number of Employees Source: Central Contractor Registration, http://www.ccr.gov/ (also public, but the receipts and employee numbers are proprietary and not public - I obtained a special run). Approximately 31,000 records Every DUNS number in FPDS must be in CCR. But not every DUNS number in CCR must be in FPDS. Reason: To sell a product or provide a service to the Federal government, you must be registered in CCR; you need (can) only register one time for each location (hence, a unique DUNS for each location). The FPDS data represents every Federal contracting transaction (new, increase, decrease, cancel, etc) for each contractor that did business with the government. Therefore, there are in this table DUNS numbers with many records - one for each transaction. Not every CCR registered DUNS is in the FPDS table because not every CCR registered contractor did business with the Federal government. I want to run a "make table" query. The resulting table should match the DUNS numbers in FPDS to those in CCR. It will include the records and data for all those contractors whose DUNS numbers are in both tables. It will have all contractors that did business with the Federal government (FPDS) together with their annual receipts and number of employees (CCR) in one table. These two tables do not have the same fields, by the way, as I show above. If they did, I could just append one to the other and be done with it. But outside the DUNS numbers and the company names, there are no similar fields. Thanks in advance for the help. |
#2
|
|||
|
|||
Make table query combining records of two tables into a third tabl
You said every FPDS.duns should be in CCR.duns.
Every DUNS number in FPDS must be in CCR. But not every DUNS number in CCR must be in FPDS. And you want the result: It will include the records and data for all those contractors whose DUNS numbers are in both tables So, basically, you want the table FPDS. Indeed, if a given DUNS appears in FDPS, it appears in CCR (first of your claim) and if a DUNS does not appear in FPDS, then it should not appear in the result. So, simply SELECT * FROM FPDS Now, if you are saying that every DUNS number in FDPS must be in CCR, in THEORY, but it just happen that some just unfortunately do not, that is another problem. SELECT * FROM fpds INNER JOIN (SELECT DISTINCT duns FROM ccr ) AS a ON fpds.duns = a.duns should do, in that case (but better to change the database design adding the required relationship, and enforcing it... if you can, that is). Vanderghast, Access MVP "carl jordan" wrote in message ... I thought I posted this yesterday, but somehow it did not get into the forum. I apologize if it is there and I just cannot find it, and this is ends up as a dupe. I will be glad to delete it if I can find it. I have tried to simplify my question too. Here is my problem, related to a project I am working on. I have two tables. 1) FPDS: fields are Duns, Company Name, Transaction Amount Source: Federal Procurement Data System (https://www.fpds.gov/) (a public site) Approximately 174,000 records; fiscal years 05, 06 and 07 2) CCR: fields are DUNS, Company Name, Annual Receipts, Number of Employees Source: Central Contractor Registration, http://www.ccr.gov/ (also public, but the receipts and employee numbers are proprietary and not public - I obtained a special run). Approximately 31,000 records Every DUNS number in FPDS must be in CCR. But not every DUNS number in CCR must be in FPDS. Reason: To sell a product or provide a service to the Federal government, you must be registered in CCR; you need (can) only register one time for each location (hence, a unique DUNS for each location). The FPDS data represents every Federal contracting transaction (new, increase, decrease, cancel, etc) for each contractor that did business with the government. Therefore, there are in this table DUNS numbers with many records - one for each transaction. Not every CCR registered DUNS is in the FPDS table because not every CCR registered contractor did business with the Federal government. I want to run a "make table" query. The resulting table should match the DUNS numbers in FPDS to those in CCR. It will include the records and data for all those contractors whose DUNS numbers are in both tables. It will have all contractors that did business with the Federal government (FPDS) together with their annual receipts and number of employees (CCR) in one table. These two tables do not have the same fields, by the way, as I show above. If they did, I could just append one to the other and be done with it. But outside the DUNS numbers and the company names, there are no similar fields. Thanks in advance for the help. |
#3
|
|||
|
|||
Make table query combining records of two tables into a third
Thank you very much. This did the trick, but I still had to work at it. I
always have trouble with any coding. But I am OK now on this. Thanks again. "Michel Walsh" wrote: You said every FPDS.duns should be in CCR.duns. Every DUNS number in FPDS must be in CCR. But not every DUNS number in CCR must be in FPDS. And you want the result: It will include the records and data for all those contractors whose DUNS numbers are in both tables So, basically, you want the table FPDS. Indeed, if a given DUNS appears in FDPS, it appears in CCR (first of your claim) and if a DUNS does not appear in FPDS, then it should not appear in the result. So, simply SELECT * FROM FPDS Now, if you are saying that every DUNS number in FDPS must be in CCR, in THEORY, but it just happen that some just unfortunately do not, that is another problem. SELECT * FROM fpds INNER JOIN (SELECT DISTINCT duns FROM ccr ) AS a ON fpds.duns = a.duns should do, in that case (but better to change the database design adding the required relationship, and enforcing it... if you can, that is). Vanderghast, Access MVP "carl jordan" wrote in message ... I thought I posted this yesterday, but somehow it did not get into the forum. I apologize if it is there and I just cannot find it, and this is ends up as a dupe. I will be glad to delete it if I can find it. I have tried to simplify my question too. Here is my problem, related to a project I am working on. I have two tables. 1) FPDS: fields are Duns, Company Name, Transaction Amount Source: Federal Procurement Data System (https://www.fpds.gov/) (a public site) Approximately 174,000 records; fiscal years 05, 06 and 07 2) CCR: fields are DUNS, Company Name, Annual Receipts, Number of Employees Source: Central Contractor Registration, http://www.ccr.gov/ (also public, but the receipts and employee numbers are proprietary and not public - I obtained a special run). Approximately 31,000 records Every DUNS number in FPDS must be in CCR. But not every DUNS number in CCR must be in FPDS. Reason: To sell a product or provide a service to the Federal government, you must be registered in CCR; you need (can) only register one time for each location (hence, a unique DUNS for each location). The FPDS data represents every Federal contracting transaction (new, increase, decrease, cancel, etc) for each contractor that did business with the government. Therefore, there are in this table DUNS numbers with many records - one for each transaction. Not every CCR registered DUNS is in the FPDS table because not every CCR registered contractor did business with the Federal government. I want to run a "make table" query. The resulting table should match the DUNS numbers in FPDS to those in CCR. It will include the records and data for all those contractors whose DUNS numbers are in both tables. It will have all contractors that did business with the Federal government (FPDS) together with their annual receipts and number of employees (CCR) in one table. These two tables do not have the same fields, by the way, as I show above. If they did, I could just append one to the other and be done with it. But outside the DUNS numbers and the company names, there are no similar fields. Thanks in advance for the help. |
Thread Tools | |
Display Modes | |
|
|