Need Help with this query (1 Viewer)

[KitaYama]

This is the relationship between 3 tables:

Some data from tblIn:

InPK	ProductFK	InQuantity
1	12345	100
2	12345	50
3	12345	100
4	22222	1000
5	22222	500
6	22222	1000
7	33333	500

Some data from tblOut:

OutPK	InFK	OutQuantity
1	1	30
2	1	70
3	2	30
4	4	1000
5	5	200
6	5	100

I'm trying hard to write a query that shows the following result:

ProductPK	InPK	Stock_From_Each_InPK
12345	2	20
12345	3	100
22222	5	200
22222	6	1000
33333	7	500

I really don't know how to explain this.
The quantity of InPK 1 is 100. There's two records for this PK in tblOut (OutPk 1 & 2) . The stock for this InPk is (100 - 30 -70=0) . This won't be shown in query.
The quantity of InPK 2 is 50. There's one record for this PK in tblOut (OutPK 3). The stock for this InPK is (50 - 30 = 20). This will be shown in query.
The quantity of InPK 3 is 100. It has no record in tblOut. The stock for this InPK is 100 and the query shows it.
The quantity of InPK 4 is 1000. There's a record in tblOut for this (OutPK 4). The stock for this InPK is 0 (1000-1000). The query doesn't show this.
The quantity of InPK 5 is 500. There's two record for this PK in tblOut (OutPK 5 & 6). The stock for this InPK is (500 - 200 - 100 = 200). The query shows it.
tblOut has no records for InPK 6 & 7. The query shows their original quantity of 1000 & 500.

Is it possible to write a query that shows this result?
Thank you.

 

[MajP]

This table design is wrong. It should be a single table
Why does table 2 have a InFK instead of a ProductFK? If you are only pulling from that specific batch it makes sense.
1. Make a query using tblOut and bring in ProductFK
I will call that qryOut
2. Make a union query to normalize the data

Sql ProductFK, InQuantity as Quantity, "In" as InOut from tblIn
Union All
Select ProductFK, -1 *OutQuantity, "Out" from qryOut

3. Make an aggregate query on the union query, group by ProductFK and Sum Quantity.

 

[KitaYama]

For now the table structure is correct. We are testing different solution to a specific problem that is out of this thread discussion.
And Yes we intend to pull from that specific batch.

Thanks for your solution.
I'll give it a try and will check how it goes.

I appreciate your time and help.

 

[KitaYama]

This table design is wrong. It should be a single table
Why does table 2 have a InFK instead of a ProductFK? If you are only pulling from that specific batch it makes sense.
1. Make a query using tblOut and bring in ProductFK
I will call that qryOut
2. Make a union query to normalize the data

Sql ProductFK, InQuantity as Quantity, "In" as InOut from tblIn
Union All
Select ProductFK, -1 *OutQuantity, "Out" from qryOut

3. Make an aggregate query on the union query, group by ProductFK and Sum Quantity.

If I haven't misunderstood your solution, it seems the result is different.
1- I assume union sql is typo and should be SELECT......
2- Union query was complaining it can't find OutQuantiy field so I added it to qryOut.

Am I doing something wrong?

thank you.

 

[KitaYama]

Will it make it easier if we change the design to this:
Still, The result should be as mentioned above.

We need InFK in tblOut for tracking and traceability purpose.

Thank you.

 

[MajP]

qryOut

SELECT tblIn.ProductFK, tblOut.InFK, tblOut.OutQuantity, "Out" AS InOut
FROM tblIn INNER JOIN tblOut ON tblIn.InPK = tblOut.InFK;

qryOut qryOut

ProductFK	InFK	OutQuantity	InOut
12345​	1​	30​	Out
12345​	1​	70​	Out
12345​	2​	30​	Out
2222​	4​	1000​	Out
2222​	5​	200​	Out
2222​	5​	100​	Out

Select ProductFK, InPK, InQuantity as Quantity, "In" as InOut from tblIn
UNION ALL Select ProductFK,InFK, -1 *OutQuantity, "Out" from qryOut
ORDER BY 2, 1, 4;

qryUnion qryUnion

ProductFK	InPK	Quantity	InOut
12345​	1​	100​	In
12345​	1​	-70​	Out
12345​	1​	-30​	Out
12345​	2​	50​	In
12345​	2​	-30​	Out
12345​	3​	100​	In
2222​	4​	1000​	In
2222​	4​	-1000​	Out
2222​	5​	500​	In
2222​	5​	-100​	Out
2222​	5​	-200​	Out
2222​	6​	1000​	In
3333​	7​	500​	In

SELECT qryUnion.ProductFK, qryUnion.InPK, Sum(qryUnion.Quantity) AS QuantityOfSum
FROM qryUnion
GROUP BY qryUnion.ProductFK, qryUnion.InPK
ORDER BY qryUnion.InPK;

qryResult qryResult

ProductFK	InPK	QuantityOfSum
12345​	1​	0​
12345​	2​	20​
12345​	3​	100​
2222​	4​	0​
2222​	5​	200​
2222​	6​	1000​
3333​	7​	500​

 

[MajP]

I forgot you were grouping by InPK, you can add a criteria to exclude 0 amounts.

 

[KitaYama]

I forgot you were grouping by InPK, you can add a criteria to exclude 0 amounts.

Seems to be perfect.
We do some more tests to see how it responds.

I appreciate your help.
It really feels so good being surrounded by geniuses.

 

[cheekybuddha]

For now the table structure is correct.

Really, it's not!

 

[cheekybuddha]

tblProducts
ProductPK
ProductNo
ProductName

tblStockMovement
StockMovementPK
ProductFK
MovementDate
Quantity (positive for In, negative for out)

Then just use an aggregate query.

 

[KitaYama]

tblProducts
ProductPK
ProductNo
ProductName

tblStockMovement
StockMovementPK
ProductFK
MovementDate
Quantity (positive for In, negative for out)

Then just use an aggregate query.

@cheekybuddha
Thanks for your concern. I think you missed my traceability comment.

With your design, you have several records with positive values and several records with negative values for the same product. 10 months later you receive a complaint about a product you took out on a specific date.
How do you recognize used products on that specific date belongs to which record with positive values?

If you have a solution I'm all ear.

Thank you.

 

[KitaYama]

Really, it's not!

Thanks again for your help.
I thought the business rules deceids the structure of tables.
Without knowing the rules how do you judge?

And I said For now
It meant We are testing different patterns, different approaches.

Again if you have a suggestion that makes traceability possible, we'll be more than glad to test it.

Thanks

 

[MajP]

If you have a solution I'm all ear.

You can simply add the field to @cheekybuddha design. It is a self refencing key where the Parent and Child are in the same table.

tblStockMovement
StockMovementPK
ProductFK
MovementDate
Quantity (positive for In, negative for out)
StockIMovementInID_FK ' self refencing foreign key to StockMovementPK

 

[MajP]

tblStock tblStock

StockMovementPK	ProductFK	Quantity	StockMovementInID_FK
1​	12345​	100​	1​
2​	12345​	50​	2​
3​	12345​	100​	3​
4​	2222​	1000​	4​
5​	2222​	500​	5​
6​	2222​	1000​	6​
7​	3333​	500​	7​
8​	12345​	-30​	1​
9​	12345​	-70​	1​
10​	12345​	-30​	2​
11​	2222​	-1000​	4​
12​	2222​	-200​	5​
13​	2222​	-100​	5​

SELECT tblStock.ProductFK, tblStock.StockMovementInID_FK, Sum(tblStock.Quantity) AS SumOfQuantity
FROM tblStock
GROUP BY tblStock.ProductFK, tblStock.StockMovementInID_FK
ORDER BY tblStock.StockMovementInID_FK;

qryStock qryStock

ProductFK	StockMovementInID_FK	SumOfQuantity
12345​	1​	0​
12345​	2​	20​
12345​	3​	100​
2222​	4​	0​
2222​	5​	200​
2222​	6​	1000​
3333​	7​	500​

 

[KitaYama]

@MajP Thanks for your further help.

I've never worked with self reference keys. We'll work on that to see if it covers all our needs.

I hope you don't mind a question. How does the data entry form look like? I Mean how StockMovementID_FK is filled on each record? Does it need some vba to recognize the Quantity is Positive or Negative and fill the field?
Or some kind of main/sub form with parent/child relation.?
Or a combo box that it's row source is changed based on the sign of quantity (+ or -)?

I will Google the term to see what I find. Meanwhile if there's any sample file or you have a link to read I really appreciate it.

Million thanks for your patience with a newbie.

 

[cheekybuddha]

Sorry, it was late last night when I posted and I hadn't grasped that you needed to track individual batches.

Thanks @MajP for suggesting a solution.

However, after a bit of sleep, I think you actually need another table, rather than a self-reference:

tblProducts
ProductPK
ProductNo
ProductName

tblProductBatches
BatchPK
ProductFK
BatchIdentifier (if required)

tblStockMovement
StockMovementPK
BatchFK
MovementDate
Quantity (positive for In, negative for out)

You can track the stock status of individual batches using aggregate queries, eg with MIN(MovementDate) for the first 'In', SUM(Quantity) grouped by BatchFK for current stock level of a particular batch.

To track Product stock levels you join the tblProductBatches table and group on the ProductFK.

 

[cheekybuddha]

I thought the business rules deceids the structure of tables.

Not really. You structure your tables according what you are modelling.

So you have Products (1 table).
This comes in in batches (1 table)
Batches have stock movements (in/out) (1 table)

Your business rules may determine when or how you update those tables.

You may wish to add a Location table, which you then can reference in the tblStockMovement table with From and To fields. Then you know where the stock is too.

 

[MajP]

This form is a little complicated to get all the subforms linking and refreshing.
1. You can select a product from the combo
2. The in subform should filter and the selected row is yellow
3. The out subform then links to the In subform. (Linked continuous subforms)
4. Then should have a third subform showing all stock out (may not be needed because of the 5 th subform)
5. 4th subform shows the totals by product and the in Batch
6. 5th subform shows all movements.

There is code to ensure out values are negative, and all values store a IN foreign key.

 

[KitaYama]

@MajP I really don't know how to appreciate your help. While I'm still reading on self reference keys, I think you opened a whole new world of possibilites. I also thank you for the sample file you posted above. Though not still in depth, but we tested it a bit and it seems it covers most of our needs. Still there are some pieces missing, but I think we will be able to do it on our own. For example we need some validation to prevent the quantity in Stock out form. As you see bellow, StockMovementPK 24 has a quantity of 100. But Stock out for it is limitless. We also need to add some code to prevent moving out stock from 25 & 26 until all stock of 24 is used.

But as I said it's excellent. We could never end up here on our own.
I really appreciate your help.

Cheers.

 

[KitaYama]

Sorry, it was late last night when I posted and I hadn't grasped that you needed to track individual batches.

Thanks @MajP for suggesting a solution.

However, after a bit of sleep, I think you actually need another table, rather than a self-reference:

tblProducts
ProductPK
ProductNo
ProductName

tblProductBatches
BatchPK
ProductFK
BatchIdentifier (if required)

tblStockMovement
StockMovementPK
BatchFK
MovementDate
Quantity (positive for In, negative for out)

You can track the stock status of individual batches using aggregate queries, eg with MIN(MovementDate) for the first 'In', SUM(Quantity) grouped by BatchFK for current stock level of a particular batch.

To track Product stock levels you join the tblProductBatches table and group on the ProductFK.

@cheekybuddha
Thanks for your suggestion.
Is it what you have in mind?

What is BatchIdentifier? And what do you mean if required? Is it a serial number we give to a batch?
Am I correct if I say with this design, for each BatchFK there should be only one positive quantity and the rest are all negative?

Thank you.