Help creating a query to distribute incoming parts.

[Fernando]

Hi, I'm new to the forums and a begginer at SQL, hope this is the right place to post my request. I'm hoping someone knows a good example on how to do the following:



Table A

binID	screwsNeeded
1	6
2	10
3	5

Table B

orderID	screwsOrdered
100	1
101	6
102	4
103	10

Table A shows I have 3 bins, each bin is missing a set number of screws.
Table B shows the I've bought the 21 missing screws form 4 different sources.

So what I need to do is create a query that shows how are the screws distributed among the 3 bins.


Query:

binID	orderID	screwsReceived
1	100	1
1	101	5
2	101	1
2	102	4
2	103	5
3	103	5

I've googled around, but I come up empty, maybe I'm not using the right keywords?

Thanks in advance,
Fernando

[June7]

I don't think query can handle this.

Ideally, db would have table(s) for transaction records that show receipt of items and consumption of items. Aggregate queries would calculate totals and difference would be balance on hand.

Need transaction records to show the breakdown of the order quantity to specific bins.

Sounds like would involve a lot of VBA code. Or manual data entry to create the transaction records.

[orange]

Can you put some business context on your requirement?
How do Bins relate to Orders, if they do? I would expect "screws" to be of various types, and not lumped together under NumberMissing or similar.
(eg a 2 1/2 inch wood screw is nothing like a 3/4 sheet metal screw) brass, steel, stainless???
What exactly are you trying to do in plain English?

[Fernando]

Here's a more detailed example.

Let's say I'm building different types of circuit cards, so these are mounting screws, all the same type of screw.

In our process I enter how many screws each circuit card will use, then our software will group them all together and present it to procurement. So in this case procurement sees that he needs to order 21 screws, he doesn't care how many goes into each card, he just orders the total, and to simplify the example, lets say table B is just 1 big order of 21 screws instead of 4 small ones (will need to get back to this later), but the vendor only has 20 screws.

Then the screws arrive, now a different person needs to verify that each circuit card has received the screws that it needs. June7 talked about manual data entry, which would be the safest way, but for this exercise I'm trying to automatically distribute the screws among the 3 PCBs in the order that they are received, so since we were able to order only 20 screws, board 3 will only have received 4 instead of 5. So my query would look like this:

binID	orderID	screwsReceived
1	100	6
2	100	10
3	100	4

Looking at the big picture I know there's a huge disconnect between procurement and receiving, but at this stage I'm just trying to get away with something like this.

[June7]

Doesn't alter my assessment.

[orange]

Thanks for the clarification. The only thing I can think of is that you identify the "importance/criticalness" of the circuit boards and distribute the "available screws" accordingly. If you don't have enough "screws" to complete a critical board, then drop to the most critical board for which you have sufficient screws.

What about the other components -capacitors, inductors, relays, transformers, diodes...? Do you run out of these also?

[Fernando]

orange,

For my application, I'm just filling the bins in order, so no matter how many screws come in at a time I will just start by filling bin 1, then 2 and so on. About distributing the available screws, well, that's what I'm looking for, to see if there's a way to do so in a query. And yes, this will apply to caps, resistors and so on, but I can take care of those once I figure out the distribution part.

June7,

I'm starting to believe that you are correct and that I'll have to do this in VBA. Though, I just found an example:

http://stackoverflow.com/questions/1...ing-values-sql

I'm in the process of dissecting it to see if that's what I was looking for, I'll come back with my findings.

[orange]

Do you have a model or good writeup of what you are trying to do?

From my view, you build circuit boards, so you need the basic circuit board.
Every circuit board requires components. Some circuit boards use more components than others.
Circuit boards require mounting screws. Some circuit boards require more screws than others.

So, you have to identify each circuit board. For each circuit board, how many of componentA, componentB..., screws to begin.

So if you plan to build 20 circuitboard_900, you know in advance how many componentA, componentB... and how many screws.

That's where I would start. But I don't know how you set priority for which circuitboards to build.

[Fernando]

Thanks OJ, but I think you are trying to help me with a bigger picture than I really need.

I have all the circuit board components listed in my DB, i'm just using the screws as an example. As to priority, binID1 has first priority, binID2 has second priority and so on. I just need to solve this exercise, I have tables A and B, is it possible to come up with Table C using a query? And so far I'm agreeing with June7.

[orange]

Please post a jpg (screen capture) of your relationships window.
It's difficult to respond when you only getting a piece of the database and requirement.

In my view, this is no way to approach design

Looking at the big picture I know there's a huge disconnect between procurement and receiving, but at this stage I'm just trying to get away with something like this.

You can design to solve a large issue (that you already recognize) and develop as time and resources allow based on priority knowing that the pieces fit within the big picture. Having the big picture in mind as you develop is a good thing.