Repeat Data

[tbotkin via AccessMonster.com]

Here is my dilemma; I am attempting to match a line item for a product from a
table of project estimates with a product from a table of prices. The
products are matched on three fields, Product ID, Vendor ID and Subdivision
ID. It is the last criteria for matching that is causing me issues. The
products table has repeated product id’s for each different vendor and
subdivision except for the fact that not every subdivision has its own unique
ID. Some of the products simply have an “X†in the Subdivision ID field that
is to be used for any subdivision that does not have specific pricing. For
a hand full of products this produces two results for a particular line item
because there are Product ID’s that have both a Subdivision ID and an X.

My question becomes, how do I filter out the double results? How do I tell
the query; if the product for this line item matches on Subdivision ID use
the Subdivision ID but do not use X if there is already a Subdivision ID
match and if there is no Subdivision ID match then use X. I think there is
some kind of a sub query, similar to finding duplicates that would resolve
the issue but I am not proficient enough in sql to get there.

Here is how I have set up my query so far, there is a one to many
relationship on product ID and vendor ID while attempting to manipulate
Subdivision ID in the WHERE statement.

SELECT tbl_ModelEsData2.Model, tbl_ModelEsData2.SubDivCode,
tbl_ProductVendorX.SubDivCodePV, tbl_ModelEsData2.ItemLineCode,
tbl_ModelEsData2.item code, tbl_ModelEsData2.line no, tbl_ModelEsData2.
product no, tbl_ModelEsData2.UsualVendor, tbl_ProductVendorX.vendor no,
tbl_ModelEsData2.purchase u m, tbl_ModelEsData2.quantity, tbl_ModelEsData2.
unit price, tbl_ProductVendorX.curr price, [quantity]*[curr price] AS
Extension
FROM tbl_ModelEsData2 LEFT JOIN tbl_ProductVendorX ON (tbl_ModelEsData2.
UsualVendor = tbl_ProductVendorX.vendor no) AND (tbl_ModelEsData2.product no
= tbl_ProductVendorX.product no)
WHERE (((tbl_ModelEsData2.Model)="GA5A1") AND ((tbl_ProductVendorX.
SubDivCodePV)=IIf([SubDivCode]=[SubDivCodePV],[SubDivCodePV],"X")))
ORDER BY tbl_ModelEsData2.item code, tbl_ModelEsData2.line no;

By the way, I am pulling my data from another program so I no control of the
inputted data.

Thanks in advance.

 

[Michel Walsh]

You can use something that is better explained by using two tables. The
first table (in your case, the sub-set of row having a X for the
subdivision) hold 'by default' values. The second table, in your case, those
rows with something ELSE than an X for the subdivision, hold the values that
are 'specific'.


SELECT defaults.itemID, Nz(specifics.price, defaults.price)
FROM specifics RIGHT JOIN defaults
ON specifics.itemID = defaults.itemID



that is, if the price exists in the 'specifics', we use it, else, we use the
price in 'defaults'.


So, in your case, the syntax is a little bit more complex in one query (you
can write queries defaults and specifics explicitly, though), but the idea
is exactly the same:


SELECT defaults.itemID, Nz(specifics.price, defaults.price)
FROM (SELECT itemID, price FROM myTable WHERE subdivision =
wantedSubdivision ) AS specifics
RIGHT JOIN
(SELECT itemID, price FROM myTable WHERE subdivision = "X" ) AS
defaults
ON specifics.itemID = defaults.itemID



Note that I am not sure, from the context, if you have a specific
'wantedSubdivision', or if you would simply use WHERE subdivision <> "X",
but I hope you can handle the exact details from here.


Hoping it may help,
Vanderghast, Access MVP

Here is my dilemma; I am attempting to match a line item for a product
from a
table of project estimates with a product from a table of prices. The
products are matched on three fields, Product ID, Vendor ID and
Subdivision
ID. It is the last criteria for matching that is causing me issues. The
products table has repeated product id's for each different vendor and
subdivision except for the fact that not every subdivision has its own
unique
ID. Some of the products simply have an "X" in the Subdivision ID field
that
is to be used for any subdivision that does not have specific pricing.
For
a hand full of products this produces two results for a particular line
item
because there are Product ID's that have both a Subdivision ID and an X.

My question becomes, how do I filter out the double results? How do I
tell
the query; if the product for this line item matches on Subdivision ID use
the Subdivision ID but do not use X if there is already a Subdivision ID
match and if there is no Subdivision ID match then use X. I think there
is
some kind of a sub query, similar to finding duplicates that would resolve
the issue but I am not proficient enough in sql to get there.

Here is how I have set up my query so far, there is a one to many
relationship on product ID and vendor ID while attempting to manipulate
Subdivision ID in the WHERE statement.

SELECT tbl_ModelEsData2.Model, tbl_ModelEsData2.SubDivCode,
tbl_ProductVendorX.SubDivCodePV, tbl_ModelEsData2.ItemLineCode,
tbl_ModelEsData2.item code, tbl_ModelEsData2.line no, tbl_ModelEsData2.
product no, tbl_ModelEsData2.UsualVendor, tbl_ProductVendorX.vendor no,
tbl_ModelEsData2.purchase u m, tbl_ModelEsData2.quantity,
tbl_ModelEsData2.
unit price, tbl_ProductVendorX.curr price, [quantity]*[curr price] AS
Extension
FROM tbl_ModelEsData2 LEFT JOIN tbl_ProductVendorX ON (tbl_ModelEsData2.
UsualVendor = tbl_ProductVendorX.vendor no) AND (tbl_ModelEsData2.product
no
= tbl_ProductVendorX.product no)
WHERE (((tbl_ModelEsData2.Model)="GA5A1") AND ((tbl_ProductVendorX.
SubDivCodePV)=IIf([SubDivCode]=[SubDivCodePV],[SubDivCodePV],"X")))
ORDER BY tbl_ModelEsData2.item code, tbl_ModelEsData2.line no;

By the way, I am pulling my data from another program so I no control of
the
inputted data.

Thanks in advance.