Prev: Selecting and counting records in one query
Next: Crosstab Query Rows
From: carl jordan on 30 Jun 2008 23:03
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" <carljordan(a)discussions.microsoft.com> wrote in message
> news:E1281B98-4B3F-4A2F-9B3D-3720C33F7324(a)microsoft.com...
> >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.
> >
>
>
>
 | 
Pages: 1
Prev: Selecting and counting records in one query
Next: Crosstab Query Rows