Query from 3 tables

[exebat]

I have three tables that I want to put in query. But this is what I
need:

Articles Stores MinimumQTY
----------- ---------- ------------------
ArticleID StoreID ArticleID
ArticleName StoreName StoreID
ArticlePrice MinQTY
OptimalQTY

What I would like is to have a query that shows all recors from
Articles, and for each article to show all Stores and for each StoreID
to show records in MinimumQTY. This seems like easy task but I want to
be able to enter quantities for each store directly in query. I mean
it should show every StoreID even if it doesnt have related record in
MinimumQTY.

It should be like this:

ArticleID
1001
StoreID MinQTY OptimalQTY
1 2 5
2
3 2 5
4
5
ArticleID
1002
StoreID MinQTY OptimalQTY
1
2 4 10
3
4
5

If I could have this I could simply enter MinQTY for ArticleID and
StoreID in query to be for example 5.

Any help ?

Thanks in advance.

 

[vanderghast]

Note that it could be FAR EASIER to have the junction table minimumQty
already filled with all the possible articleID, storeID, with NULLs under
the qty if none is appopriate! And that table is surely updateable,
directly, no fancy code to write.


The following suggestion just try to make the same thing, more or less, with
a temp table, and it is untested.


Make a temp table with


SELECT x.articles.articleID as articleID, x.stores.storeID as storeID,
minQty, optimalQty
FROM (SELECT * FROM articles , stores) As x LEFT JOIN minimumQty
ON x.articles.articleID = minimumQty.articleID
AND x.stores.storeID = minimumQty.storeID


let the user modify the data, and, before deleting that temp table with the
new data, make an ***upsert*** (update/insert) query between this temp table
and the table minimumQty:


UPDATE minimumQty RIGHT JOIN temp
ON minimumQty.articleID = temp.articleID
AND minimumQty.storeID = temp.storeID
SET minimumQty.articleID = temp.articleID,
minimumQty.storeID = temp.storeID,
minimumQty.minQty=temp.minQty,
minimumQty.optimalQty=temp.optimalQty
WHERE temp.minQty IS NOT NULL
OR temp.optimalQty IS NOT NULL








Vanderghast, Access MVP