This is the relationship between 3 tables:
Some data from tblIn:
Some data from tblOut:
I'm trying hard to write a query that shows the following result:
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.
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.
Last edited: