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.

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.

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.