AlexN
Registered User.
- Local time
- Today, 20:26
- Joined
- Nov 10, 2014
- Messages
- 302
I’m trying to design a database that will keep track of the value of products in stock, for several stores.
So we have days with buys, days with sells, days with buys/sells, and days with nothing.
1st issue is that, for those fine days of no transaction, there will be no record, none, nowhere in the database. So, after several weeks, months or years someone wants to see what the total product value was for such a day it would be a problem.
2nd issue is that, having a bunch of transactions, the DSum in a query will only aggregate the products referred in each transaction, and there’s no way of bringing up the stock already existing.
In simple words, let’s say we bought 20 units of Product A on Monday, 30 units of Product A on Tuesday, and then on Thursday we bought 40 units of Product B. On the aggregate query I’ll have a column that will show that I have 20 units of Product A on Monday, 50 units of Product A on Tuesday, and 40 units of product B on Thursday. But on Thursday I also have the 50 units of Product A, and they won’t show.
I’ve been searching everywhere in the web to find some guide of how to overcome this, I carefully examined Northwind database which almost everyone suggested I should study, but came with no solution (at least I didn’t recognize one).
So, if anyone of you kind gentlemen had some enlightening idea it would be much appreciated.
Thank you.
Nick
So we have days with buys, days with sells, days with buys/sells, and days with nothing.
1st issue is that, for those fine days of no transaction, there will be no record, none, nowhere in the database. So, after several weeks, months or years someone wants to see what the total product value was for such a day it would be a problem.
2nd issue is that, having a bunch of transactions, the DSum in a query will only aggregate the products referred in each transaction, and there’s no way of bringing up the stock already existing.
In simple words, let’s say we bought 20 units of Product A on Monday, 30 units of Product A on Tuesday, and then on Thursday we bought 40 units of Product B. On the aggregate query I’ll have a column that will show that I have 20 units of Product A on Monday, 50 units of Product A on Tuesday, and 40 units of product B on Thursday. But on Thursday I also have the 50 units of Product A, and they won’t show.
I’ve been searching everywhere in the web to find some guide of how to overcome this, I carefully examined Northwind database which almost everyone suggested I should study, but came with no solution (at least I didn’t recognize one).
So, if anyone of you kind gentlemen had some enlightening idea it would be much appreciated.
Thank you.
Nick