Query retrieves incorrect data

[ramnewbietoaccess]

I have a select query based on one table and one query. The table tells me what products have been forcasted for the month of January and the query tells me what of those forecasted products have actually been ordered. I would like to see the entire list of the products, the number forecasted, and the number ordered with null values in the places where they have not yet ordered their forecast.

When I run the query, however, it does not retrieve accurate data. I am missing some items. I have tried all three types of joins out of frustration and none of them work.

Any suggestions? Is there something with sql that I could do?

Thank you in advance

 

[PHV]

FROM (tblProducts
LEFT JOIN tblForecastedProducts ON tblProducts.PK = tblForecastedProducts.FK)
LEFT JOIN tblOrderedProducts ON tblProducts.PK = tblOrderedProducts.FK

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ramnewbietoaccess]

Thanks. I am having a little trouble understanding the logic well enough to alter the names to what my fields are called. Can you elaborate a bit on what this is doing?

 

[PHV]

tblForecastedProducts.FK is the Foreign Key referencing the Primary Key of tblProducts

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

these left joins indicate that the query should get all the records from tblProducts and only records that match from tblForecastedProducts and again all the records from tblProducts and only the records that match from tblOrderedProducts.

You indicate that you have one table and one query. You could do this in a single query. Assuming you have:

tblProducts
ProductID - PK
(list of all products available)

tblForecastedProducts
ProductID - FK

tblOrderedProducts
OrderID
ProductID - FK

using the left join syntax that PHV provided, you would get all the needed information in this single query.

HTH

leslie

 

[ramnewbietoaccess]

Thank you so much and thanks for taking the time to explain the logic I really really appreciate your time

 

[ramnewbietoaccess]

I have typed in the sql syntax that PHV has provided however I am getting an error saying

"Invalid SQL statement expetced Delete, INsert, Procedure, Select, Update"

Any thoughts?

I am in MSAccess 2000.

thanks again

 

[PHV]

Can you please post the SQL code you tried ?
Obviously my post covered only the JOIN portion of the whole instruction, without the SELECT nor the WHERE clauses ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ramnewbietoaccess]

FROM (tblProducts
LEFT JOIN tblConsolidatedForecast ON tblProducts.PK = tblConsolidatedForecast.FK)
LEFT JOIN tblOrders ON tblProducts.PK = tblOrders.FK

Thanks for looking at this again

 

[PHV]

Reread carefully my last post.

 

[ramnewbietoaccess]

im sorry i dont know what you mean i re-read it. you had said the syntax would get the correct results so i typed it as it was posted

 

[lespaul]

please post the ENTIRE SQL statement, not just the FROM clause.


Leslie

 

[ramnewbietoaccess]

that is all she gave me

 

[lespaul]

that is all she gave me

uhhhh? In Access switch to the SQL view of the query?

I have a select query

Then you should have a query that looks like:

SELECT something FROM someTable WHERE Blah and blah and blah

could we please see the SELECT part and the WHERE part of your query?

Leslie