Originally Posted by
Salty Mariner
on you last remark what I did was create 2 simple aggregate query's one that creates aggregate totals for stock-in, and one for stock-out. I use a 3rd query to bring in the previous 2 aggregate queries and subtract stock-out from stock-in. It works great and does not require a single line of code. This saves my users from having to think about positive vs negative numbers.
My intention is to keep this as simple as possible with probably only around 2 forms so that (hopefully) it will require little maintenance once deployed.
My advice about vessel table is, designate it properly anyway. Keep the table with info about all vessels in in separate DB, and copy the info from there into specific vessel DB, or copy all vessels info into every vessels databases, and have in every vessel's DB a SetUp table where the vessel is determined, and this ID of this vessel is added into other tables of this DB where needed. When you do this, and don't use possibilities it gives, nothing bad happens. But when you in future have a need to consolidate databases of different vessels, having every vessel a certain ID will help enormously.
Now about transactions table/form:
Let's assume you have transations table (I added VesselID too - when you don't use it, you can simply skip it):
tblTransactions: TransactionID, VesselID, ItemID, TType, TDate, TQty
(Quantityes in all entries are entered as positive numbers!)
tblTransactionTypes: TType, TDirection, TypeText (tDirection is 1 for all incoming transaction types, and -1 for all outgoing transaction types. NB! With his schema, you must have separate types for incoming entries, and for outgoing entries! Or users have to cope with positive and negative values!)
To calculate e.g. incoming quantities for certain time period, all you need is a single query - like:
Code:
SELECT [tran.VesselID,] tran.ItemID, it.ItemName, it.PartNo, SUM(ttyp.TDirection*tran.TQty) AS IncomingQty
FROM (tblTransactions tran INNER JOIN tblTransactionTypes ttyp ON tran.TType = ttyp.TType) INNER JOIN tblItems it ON tran.ItemID = it.ItemID
WHERE ttyp.TDirection = 1 AND tran.TDate BETWEEN StartDate AND EndDate
GROUP BY [tran.VesselID,] tran.ItemID, it.ItemName, it.PartNo
NB! A single query!
For outgoing quantities for certain time period, all is same except you use -1 in WHERE clause.
To calculate saldo of all items at certain date:
Code:
SELECT [tran.VesselID,] tran.ItemID, it.ItemName, it.PartNo, SUM(ttyp.TDirection*tran.TQty) AS ItemSaldo
FROM (tblTransactions tran INNER JOIN tblTransactionTypes ttyp ON tran.TType = ttyp.TType) INNER JOIN tblItems it ON tran.ItemID = it.ItemID
WHERE tran.TDate <= SaldoDate
GROUP BY [tran.VesselID,] tran.ItemID, it.ItemName, it.PartNo
Again a single query! And of-course all this is managed in single form!