Manny:
You should really break down your Storage table into 3 normalized tables,
Categories, Models and Stock say, a follows:
Ctaegories
….CategoryID (PK)
….Category
Models
….ModelID (PK)
….Model
….CategoryID
For the Stock table you only need the one column to record the stock count as
the previous cont will be in the row with the same ModelID and the latest
previous date counted. Recording the same value in two rows introduces
redundancy and the possibility of inconsistent data being entered, so the
table would be like this:
Stock:
….ModelID (part of composite PK)
….StockDate (part of composite PK)
….StockInHand
For the stock used over date range you can then use a query like this:
PARAMETERS
[Enter start date:] DateTime,
[Enter end date:] DateTime;
SELECT DISTINCT [Enter start date:] AS DateFrom,
[Enter end date:] AS DateTo, ModelID,
(SELECT StockInHand
FROM Stock As S2
WHERE S2.ModelID = S1.ModelID
AND S2.StockDate =
(SELECT MIN(Stockdate)
FROM Stock As S3
WHERE S3.ModelID = S1.ModelID
AND S3.Stockdate >=[Enter start date:]))
-(SELECT StockInHand
FROM Stock As S4
WHERE S4.ModelID = S1.ModelID
AND S4.StockDate =
(SELECT MAX(Stockdate)
FROM Stock As S5
WHERE S5.ModelID = S1.ModelID
AND S5.Stockdate <=[Enter end date:]))
AS StockUsed
FROM Stock AS S1;
This will give you the stock used per model over the date range defined by
the parameters even if there have been several intervening counts between the
start and ends of the range.
For the form first create another query, qryStockUsed say, this time without
parameters to give you the stock used per item since the latest previous
stock count:
SELECT S1.ModelID, S1.StockDate,
(SELECT StockInHand
FROM Stock As S2
WHERE S2.ModelID = S1.ModelID
AND S2.StockDate =
(SELECT MAX(Stockdate)
FROM Stock As S3
WHERE S3.ModelID = S1.ModelID
AND S3.Stockdate < S1.StockDate))-StockInHand
AS StockUsed
FROM Stock AS S1;
You can then look up the StckUsed value for the current ModelID and StockDate
in an unbound text box in a form based on the Stock table by setting its
ControlSource property to:
=DLookUp("StockUsed","qryStockUsed","ModelID = " & Nz([ModelID],0) & " And
Stockdate = #" & Format(Nz([StockDate],0),"yyyy-mm-dd") & "#")
To update the value in this text box, put the following in its Enter event
procedure:
If Me.Dirty Then
Me.Dirty = False
Me.Recalc
End If
Relying on the difference between two stock counts to give you the number
used does of course assume that nothing is added to stock, which is a rather
unrealistic scenario. If items are added to stock between two counts then
the values of the later count may well exceed the values of the former; in
fact they are likely to do so as normally restocking would be triggered by a
low count being reached. You could cater for this with the following table:
StockReceived
….ModelID (part of composite PK)
….DateReceived (part of composite PK)
….Quantity
The above queries would then be amended as follows:
SELECT S1.ModelID, S1.StockDate,
(SELECT StockInHand
FROM Stock As S2
WHERE S2.ModelID = S1.ModelID
AND S2.StockDate =
(SELECT MAX(Stockdate)
FROM Stock As S3
WHERE S3.ModelID = S1.ModelID
AND S3.Stockdate < S1.StockDate))
+NZ((SELECT SUM(Quantity)
FROM StockReceived
WHERE StockReceived.ModelID = S1.ModelID
AND StockReceived.DateReceived BETWEEN
(SELECT MAX(Stockdate)
FROM Stock As S4
WHERE S4.ModelID = S1.ModelID
AND S4.Stockdate < S1.StockDate)
AND S1.StockDate),0)
-StockInHand AS StockUsed
FROM Stock AS S1;
PARAMETERS
[Enter start date:] DateTime,
[Enter end date:] DateTime;
SELECT DISTINCT [Enter start date:] AS DateFrom,
[Enter end date:] AS DateTo, ModelID,
(SELECT StockInHand
FROM Stock As S2
WHERE S2.ModelID = S1.ModelID
AND S2.StockDate =
(SELECT MIN(Stockdate)
FROM Stock As S3
WHERE S3.ModelID = S1.ModelID
AND S3.Stockdate >=[Enter start date:]))
-(SELECT StockInHand
FROM Stock As S4
WHERE S4.ModelID = S1.ModelID
AND S4.StockDate =
(SELECT MAX(Stockdate)
FROM Stock As S5
WHERE S5.ModelID = S1.ModelID
AND S5.Stockdate <=[Enter end date:]))
+NZ((SELECT SUM(Quantity)
FROM StockReceived
WHERE StockReceived.ModelID = S1.ModelID
AND StockReceived.DateReceived BETWEEN
[Enter start date:] AND [Enter End date:]),0)
AS StockUsed
FROM Stock AS S1;
Note that in the StockReceived table a row should not be inserted with the
same DateReceived date as a StockDate in a row for that model in the Stock
table. This is because the stock count on that day could have been before or
after the stock count.
Also the initial rows in StockReceived for each item should be before the
first stock counts for the items to initialise the stock.
Ken Sheridan
Stafford, England
Hello community ^_^
I'm in the need of some help!
I got 2 questions that hopefully you guys can help me find the answer
First,
The function of the database is to store the information of printer
cartridges. We normally do storage count every month, so I need a report to
print out how many cartridges were used based on the count in the storage
room and the new count. So basiclly OldCount-NewCount.
NewCount: will be inputted by user.
Total Used: NewCount-OldCount
OldCount: after the calculation, it will then get the value from NewCount
How can I be able to do that in a form?
Second,
I would like to know how can I create a report that will ask me an StartDate
and an EndDate and depending on the info inserted it will display the
records. This is to display the TOTAL USED cartridges used per month.
Database Structure:
- Table (Storage) the table contains these fields: Manufacturer, Model,
Category (all referring to the cartridges) a field for Storage (I use this
for oldcount) and the Count field (we can use this as newcount), Total Used
(empty, but on the query I set it as Storage-Count) StartDate and EndDate
fields empty.
- Form (Storage) is based on the Table.
- Query (Storage) displays all fields from the table.
I really appreciate any help, and I'm willing to mail a copy of the database
if anybody wants to take a deeper look into it.
~Manny
