Help with Join Query to include record from 2 tables

[Ira]

Hi,
Running Access 2013 on Windows 8.1

Need help finishing this query. I know I'm close. I'm getting just one record, but missing all the records from the first table.



I have a table of Product Prices (Pricing) and a second table of Deviated Prices (DeviatedPrice) for special customers.

I need to generate a List of Prices for a specific client, but if the PartNo exist in both tables and the client is the one in question (ClientID), have the deviated price written to the table rather than the standard price.

Any help would be greater appreciated. See code below.

SELECT Pricing.PartNo, Pricing.StandardPrice, DeviatedPrice.DeviatedPrice
FROM Pricing LEFT JOIN DeviatedPrice ON Pricing.PartNo = DeviatedPrice.PartNo
WHERE DeviatedPrice.ClientID=7382;

If I mess around with the joins, I either get just one record or all the record plus one extra. The deviated price for the client in question is added to the table along with the standard price. I need the deviated price to override the standard price for that client.

Thank you,
Ira

[JoeM]

Can you post an example, with sample data from both tables along with your expected output.
I have a feeling it be a bit clearer if we have a visual view of what you are working with and trying to accomplish.

[Ira]

Sure, See Example below

[ArviLaanemets]

On fly (I don't have Access available to test this):

Create a saved query p.e.
qFullPrices: SELECT p.PartNo, p.StandardPrice, dp.DeviatedPrice, dp.ClientID FROM Pricing p LEFT JOIN DeviatedPrice.DeviatedPrice dp ON dp.PartNo = p.PartNo;

Now you query will be
Select fp.PartNo, fp.StandardPrice, fp.DeviatedPrice FROM qFullPrices fp WHERE fp.ClientID Is Null OR fp.ClientID = 7382;
or
Select fp.PartNo, Nz(fp.DeviatedPrice, fp.StandardPrice) AS Price FROM qFullPrices fp WHERE fp.ClientID Is Null OR fp.ClientID = 7382;

Edit. Scrap it! This leaves out parts, which have deviated price for some other client, but don't have deviated price for your wanted client! I could make a working query in MS SQL, but I'm afraid this syntax doesn't work in access (where in LEFT JOIN clause you use syntax "... ON tbl1.id1 = tbl2.id1 ... AND tbl1.somefield = somevalue" - I have tried it and did get an error)

[Ira]

I tried it too. Doesn't work. Okay, maybe someone else will have a solution. Thanks anyway.

[JoeM]

Try this:

SELECT Pricing.PartNo, NZ(DeviatedPrice.DeviatedPrice,Pricing.StandardPrice)+0 AS Price
FROM Pricing 
LEFT JOIN 
(SELECT DeviatedPrice.DeviatedPrice, DeviatedPrice,PartNo
FROM DeviatedPrice
WHERE DeviatedPrice.ClientID='1234')  AS dp 
ON Pricing.PartNo = dp.PartNo;

[June7]

A SELECT query does not write data to table. Exactly how do you intend to use the SELECT to accomplish? If a SELECT query can produce the required output when needed, why write to a table?

Are you trying to save the alternate value to an order record? If you really want to save the alternate value to table, then why not code this behind Order data entry form?

Regardless, maybe a DLookup() would serve?

[Ira]

Try this:

SELECT Pricing.PartNo, NZ(DeviatedPrice.DeviatedPrice,Pricing.StandardPrice)+0 AS Price
FROM Pricing 
LEFT JOIN 
(SELECT DeviatedPrice.DeviatedPrice, DeviatedPrice,PartNo
FROM DeviatedPrice
WHERE DeviatedPrice.ClientID='1234')  AS dp 
ON Pricing.PartNo = dp.PartNo;

Awesome [JoeM]. Works great! Thank you very much

[JoeM]

You are welcome.