Comparison Query

  • Thread starter Thread starter RD
  • Start date Start date
R

RD

I'm trying to find the difference between these two views in a MS Access
Project file connected to SQL Server 2000. The first returns approximately
1400 records, while the second returns 970 records. The group wants to know
what the 430 records are.



SELECT DISTINCT

dbo.BOM.Component, dbo.PartMaster.DescText,
dbo.PartMaster.StockUOM, dbo.PartMaster.ISC, dbo.PartMaster.OMC,

dbo.PartMaster.LeadTime, dbo.PartMaster.OrderMultiple,
dbo.PartMaster.OrderQuantity

FROM dbo.BOM INNER JOIN

dbo.PartMaster ON dbo.BOM.Component =
dbo.PartMaster.PartNumber





SELECT DISTINCT

dbo.BOM.Component, dbo.PartMaster.DescText,
dbo.PartMaster.StockUOM, dbo.PartMaster.ISC, dbo.PartMaster.OMC,

dbo.PartMaster.LeadTime, dbo.PartMaster.OrderMultiple,
dbo.PartMaster.OrderQuantity, dbo.Suppliers.SupplierName

FROM dbo.BOM INNER JOIN

dbo.PartMaster ON dbo.BOM.Component =
dbo.PartMaster.PartNumber INNER JOIN

dbo.PartXReference ON dbo.PartMaster.PartNumber =
dbo.PartXReference.PartNumber INNER JOIN

dbo.Suppliers ON dbo.PartXReference.SupplierID =
dbo.Suppliers.SupplierID





How do I flush out the 430 records?
 
Dear RD:

The difference is, the second query shows SupplierName. Somewhere in
the two additional tables joined (PartXReference and Suppliers) there
is a one-to-many or many-to-many relationship (at least functionally,
even if not declared). As a result, there can be, and apparently are,
more than one SupplierName in the second query for each of the values
shown in the first query.

So, how can you pare the 1400 records down to 970? Well, when there's
more than one SupplierName, if you don't want to show all of them,
which one do you want to show? You have to supply a rule that says
which SupplierName gets shown, and all the rest are omitted. As long
as the query is being asked to show all these SupplierNames, it has to
add more rows to the query to show the additional SupplierNames. In
fact, that's exactly what your second query DEMANDS that it do.

There is also a possibility that there are rows in BOM joined with
PartMaster that do not exist in PartXReference and Suppliers. This
would result in a net reduction in the number of rows shown. Once
you've brought the second query down to where it shows each
Component/DescText/StockUOM/ISC/OMC/LeadTime/OrderMultiple/OrderQuantity
only once, you could find you have fewer than 970 rows. If this is
not desirable, LEFT JOINS instead of INNER JOINS may be better for
these two tables.

Please let me know if this helped and if I can be of any other
assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top