Supply and demand query

[jamtrad]

Hi Folks


This has got me stumped. I trying to build a query that will show me stores (Str) who have not sold items but that them on Hand (OH) to and match them to stores who have sold items and have none on hand. A kind of supply and demand. The table has 12 different stores and each store has the same items. So there are duplicates items and duplicate stores. So I have tried showing data to show stores that has not sold an item and have it OH and tried to join with Stores that have sold items do not have it on hand. When I link the items of both querys and I have tried Make tabel querys is does bring back the correct data.
Hope this make sense to you. Thank you for your help.

Qry Sold and OH (Ratio)
StrItem#O/HSOLDExpr1primary KeyFTU4053310-11SAN4414310-12SAN441450113SAN441530004

[aytee111]

Do it bit by bit, slowly, and maybe at the end you can put it all in one, once you understand what is going on.

- get a list (query) of all stores, group on store and item, sum OH, criteria OH = 0 and sold > 0
- get a list of the opposite
- now you have two lists - one for stores that have sold but have no OH, one of stores that have OH but not sold
- join these two lists by item

[aytee111]

Damn - this was a duplicate!!

[jamtrad]

Thanks for you response. However that didn't work.

Qry Sold and OH By item)StrItemSumOfO/HSumOfSOLDExpr1primary KeyNUmberBTF497211102671COT497210112682FTU4972120-22694SAN497211212705ST4972110-12667

Here's some results from another query, as you can see FTU has 2 on hold (OH) and COT has sold one and none OH. How do I write a query to just pull Item 49721 needs to come from SAN and go to COT?

Sorry for not being clear.

[aytee111]

"However that didn't work."

Which part exactly didn't work?

[jamtrad]

when you join the 2 queries, the results don't make sense.

[jzwp11]

Qry Sold and OH By item)StrItemSumOfO/HSumOfSOLDExpr1primary KeyNUmberBTF497211102671COT497210112682FTU4972120-22694SAN497211212705ST4972110-12667

This is hard to interpret because there is no formatting.

Can you zip and post a copy of your database with any sensitive data removed? Perhaps that will give us a better idea of what you are after.

Please make sure to provide the name of the query and/or tables that are pertinent to your issue.

[aytee111]

OK. Let's go back a step. We have two lists, one for stores with OH and one for stores without. There isn't going to be a 1 to 1 match-up - one store on the left query might have 100 OH and three stores on the right query which need that stock. Or the opposite. So there maybe needs to be some manual input? Such as a form which displays these lists and you manually select which quantity goes to which store.