Stock valuation query

[Senjen]

oh boy, I hope I'm not repeating someone elses post. I have searched without luck.
I am trying to pull a valuation of our stock on the first of each month and report this with the following 2 columns: Month/Year, stock (inventory) value (I want a total, not the value of inventory on each item). what I want would look like this:
February 05 $330,000.00
March 05 $275,000.00
April 05 $315,000.00
My problem is that my inventory table does not have an entry for each day. Inventory on each part is only recorded when there is a change. So I may have a record for part A on 3/3/05 but the next entry may not be until 5/15/05. If I try to run the report based on the date it will show none in inventory on 4/1/05 because there is no entry listed with that month. I want it to show me the last recorded value prior to the month beginning. I have a query to pull the maximum date of each sku for each month with an entry and the value of that inventory. The results look like this:
Date sku Month OnHand Cost Worth
5/4/2005 Hxxx 24064 62100 $0.06 $3,477.60
9/16/2005 Hxxx 24056 78700 $0.06 $4,407.20
4/8/2005 Hxxy 24063 18800 $0.13 $2,444.00
etc...
Help would be greatly appreciated.

 

[Remou]

Hi
I have read your post a couple of times and I'm still a little confused! Your current data is:
[tt]5/4/2005 Hxxx 24064 62100 $0.06 $3,477.60
9/16/2005 Hxxx 24056 78700 $0.06 $4,407.20
4/8/2005 Hxxy 24063 18800 $0.13 $2,444.00[/tt]

But you want the above to read:
[tt]4 2005 Hxxy 24063 18800 $0.13 $2,444.00
5 2005 Hxxx 24064 62100 $0.06 $3,477.60
[red]6 2005 Hxxx 24064 62100 $0.06 $3,477.60
7 2005 Hxxx 24064 62100 $0.06 $3,477.60
8 2005 Hxxx 24064 62100 $0.06 $3,477.60[/red]
9 2005 Hxxx 24056 78700 $0.06 $4,407.20[/tt]

Yet this seems very strange. What point am I missing?

 

[Senjen]

I was afraid I wouldnt make myself clear, allow me to try again. You are correct as far as the data I have, what I would like the report to do is give me totals by month of all the values combined:
Month Value
1 24063 $330,000.00
2 24064 $275,000.00
3 24065 $315,000.00
I want to know the value of all my inventory as of March 1st 2005 compared to the total value of all my inventory on February 1st 2005 and so on.
If I could fill in the missing months (similar to what you show above) like this:
Year Part Month OnHand Cost Value
4 2005 Hxxy 24063 18800 $0.13 $2,444.00
5 2005 Hxxx 24064 62100 $0.06 $3,477.60
6 2005 Hxxx 24065 62100 $0.06 $3,477.60
7 2005 Hxxx 24066 62100 $0.06 $3,477.60
8 2005 Hxxx 24067 62100 $0.06 $3,477.60
9 2005 Hxxx 24068 78700 $0.06 $4,407.20
where lines 6-8 are created based on the last actual entry (line 5) in my inventory table then I could pull my report grouped by month and all would be well. But if I try to pull it without those entries:
Year Part Month OnHand Cost Value
4 2005 Hxxy 24063 18800 $0.13 $2,444.00
5 2005 Hxxx 24064 62100 $0.06 $3,477.60
6 2005 Hxxx 24068 78700 $0.06 $4,407.20
then months 24065-24067 show that I had no part # Hxxx in inventory and my value total is incorrect.

 

[Remou]

Hi
I cannot see any way to do this with a query, but it would be pretty easy to code. Is this an option for you?

Sub InventoryValue()
Dim rsSum As Recordset, rsStock As Recordset
Dim dblVal As Double
'Stock by month
Set rsStock = CurrentDb.OpenRecordSet("SELECT Month.Month, StockQ.SumOfValue " _
& "FROM StockQ RIGHT JOIN [Month] ON StockQ.Month = Month.MnthNo;")
'Summary table
Set rsSum = CurrentDb.OpenRecordSet("Summary")
dblVal = 0
Do While Not rsStock.EOF
    rsSum.AddNew
    rsSum!Month = rsStock!Month
    If Not IsNull(rsStock!SumOfValue) Then
        rsSum!Value = rsStock!SumOfValue
        dblVal = rsStock!SumOfValue
    Else
        rsSum!Value = dblVal
    End If
    rsSum.Update
    rsStock.MoveNext
Loop
End Sub

I hope I have got what you are after this time. The above is very rough, but might show you a direction.