C
carl jordan
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.
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.