If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query from 3 tables
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 "exebat" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|