queries to scrub for contract prices and then compare to existing

  • Thread starter Thread starter KODA
  • Start date Start date
K

KODA

I have a DB where I import an xls from outlook. I query the xls (table) for a
Y/N for a contract price and list the p/n's that are contract parts and their
quoted cost. I have trouble when I try to then compare the contract price in
the query to the known contract price in a table. I know I have 3 p/n's that
are contract parts but I can't get the second query to get all 3. I may just
be doing it incorrectly but I am stummped.
 
Post the SQL of your attempts. Post sample data from the tables along with
query results.
 
for qFindContractPrices:
SELECT tblTest_Bom.[RSC PN], tblTest_Bom.Description, tblTest_Bom.[Contract
Part Supplier], tblTest_Bom.[Unit Cost]
FROM tblTest_Bom
WHERE (((tblTest_Bom.[Contract Part])="Y"));

here I get three results



for qCompareContractPricing:
SELECT qFindContractPrices.[RSC PN], qFindContractPrices.Description,
qFindContractPrices.[Contract Part Supplier], qFindContractPrices.[Unit
Cost], tblContractVendorMicroChip.[Contract Price]
FROM qFindContractPrices RIGHT JOIN tblContractVendorMicroChip ON
qFindContractPrices.[RSC PN] = tblContractVendorMicroChip.[RSC PN]
WHERE (((qFindContractPrices.[RSC PN])=[tblContractVendorMicroChip].[RSC
PN]));

here I get one result, I don't know the right query to give me all three
with Unit Cost AND Contract Price.

I generate a report to e-mail to the user. My ultimate destination is to
have all of this run behind the seen.

as you say, build a little - test a little
--thx
 
Change --
FROM qFindContractPrices RIGHT JOIN tblContractVendorMicroChip ON
To ---
FROM qFindContractPrices LEFT JOIN tblContractVendorMicroChip ON
you should get the three.

But what if there are more on the right side next time?
You need to build a union query like this --
SELECT tblTest_Bom.[RSC PN]
FROM tblTest_Bom
UNION SELECT tblContractVendorMicroChip.[RSC PN]
FROM tblContractVendorMicroChip;

Use this query LEFT JOIN to both to pull everything that matches.
--
KARL DEWEY
Build a little - Test a little


KODA said:
for qFindContractPrices:
SELECT tblTest_Bom.[RSC PN], tblTest_Bom.Description, tblTest_Bom.[Contract
Part Supplier], tblTest_Bom.[Unit Cost]
FROM tblTest_Bom
WHERE (((tblTest_Bom.[Contract Part])="Y"));

here I get three results



for qCompareContractPricing:
SELECT qFindContractPrices.[RSC PN], qFindContractPrices.Description,
qFindContractPrices.[Contract Part Supplier], qFindContractPrices.[Unit
Cost], tblContractVendorMicroChip.[Contract Price]
FROM qFindContractPrices RIGHT JOIN tblContractVendorMicroChip ON
qFindContractPrices.[RSC PN] = tblContractVendorMicroChip.[RSC PN]
WHERE (((qFindContractPrices.[RSC PN])=[tblContractVendorMicroChip].[RSC
PN]));

here I get one result, I don't know the right query to give me all three
with Unit Cost AND Contract Price.

I generate a report to e-mail to the user. My ultimate destination is to
have all of this run behind the seen.

as you say, build a little - test a little
--thx

KARL DEWEY said:
Post the SQL of your attempts. Post sample data from the tables along with
query results.
Click to expand...
Click to expand...
 
thx, what if the the Contract Vendor is another? I have 3 dif ones I'm
testing right now in 3 dif tables, tblContractVendorXXX and
tblContractVendorYYY. I wanted to start with 3 to make it work, then I could
add multiple. If the Contract Vendor was the same all the time it would work
but it could be one of many. This is where I have been trying many queries
and relationships.

KARL DEWEY said:
Change --
FROM qFindContractPrices RIGHT JOIN tblContractVendorMicroChip ON
To ---
FROM qFindContractPrices LEFT JOIN tblContractVendorMicroChip ON
you should get the three.

But what if there are more on the right side next time?
You need to build a union query like this --
SELECT tblTest_Bom.[RSC PN]
FROM tblTest_Bom
UNION SELECT tblContractVendorMicroChip.[RSC PN]
FROM tblContractVendorMicroChip;

Use this query LEFT JOIN to both to pull everything that matches.
--
KARL DEWEY
Build a little - Test a little


KODA said:
for qFindContractPrices:
SELECT tblTest_Bom.[RSC PN], tblTest_Bom.Description, tblTest_Bom.[Contract
Part Supplier], tblTest_Bom.[Unit Cost]
FROM tblTest_Bom
WHERE (((tblTest_Bom.[Contract Part])="Y"));

here I get three results



for qCompareContractPricing:
SELECT qFindContractPrices.[RSC PN], qFindContractPrices.Description,
qFindContractPrices.[Contract Part Supplier], qFindContractPrices.[Unit
Cost], tblContractVendorMicroChip.[Contract Price]
FROM qFindContractPrices RIGHT JOIN tblContractVendorMicroChip ON
qFindContractPrices.[RSC PN] = tblContractVendorMicroChip.[RSC PN]
WHERE (((qFindContractPrices.[RSC PN])=[tblContractVendorMicroChip].[RSC
PN]));

here I get one result, I don't know the right query to give me all three
with Unit Cost AND Contract Price.

I generate a report to e-mail to the user. My ultimate destination is to
have all of this run behind the seen.

as you say, build a little - test a little
--thx

KARL DEWEY said:
Post the SQL of your attempts. Post sample data from the tables along with
query results.

--
KARL DEWEY
Build a little - Test a little


:

I have a DB where I import an xls from outlook. I query the xls (table) for a
Y/N for a contract price and list the p/n's that are contract parts and their
quoted cost. I have trouble when I try to then compare the contract price in
the query to the known contract price in a table. I know I have 3 p/n's that
are contract parts but I can't get the second query to get all 3. I may just
be doing it incorrectly but I am stummped.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top