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.

 

[Daryl S]

Exebat -

You are asking to do two types of queries in one - an update for records
that are there, and an append for new records. You can't do that in one
query. This approach might work for you:

If you really should have all articles at all stores, then run an append
query to populate the MinimumQty table with all StoreID / ArticleID
combinations that are not there. Then you can run a query that will show you
what you want on a form (use Datasheet Mode to make entry easiest).

If you do not want all articles at all stores, then you need to decide what
the trade-off is of showing the possibilities. You can also take the
approach above, followed by a delete query that will remove all records with
no MinQty and no OptimalQty.

 

[Douglas J. Steele]

Exebat -

You are asking to do two types of queries in one - an update for records
that are there, and an append for new records. You can't do that in one
query.

Actually, you can.

See my November, 2003 "Access Answers" column in Pinnacle Publication's
"Smart Access". You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Daryl S]

Douglas - your code requires the 'new data', which in Exabat's case will
require a cartesian product query, as there is no 'new data' table in this
case. So we are back to two queries in either case, though with your method
you would only need to run one of them, the other would be needed as the 'new
data' source. In either case, if all the records are needed (i.e. all
articles should be in all stores), then this is OK, otherwise there will be
some cleanup to do.