Query question or maybe VB required

[carlmack]

Hi All,

I am relatively new to Access having created my first database a few months ago. I have hit a problem and I am not sure if I can solve this with queries or I need to move to Visual Basic.

I want to create a table containing the stock a product for each day for the next sixty days. I have the current stock in one table, a monthly forecast of sales in another and purchase orders in another.

I want to use the monthly forecast divided by how many days in that month and move forward day by day allowing for purchases coming in.

The tables (simplyfied) are :-

Stock Table.
eg.
Code Stock Qty Date
101 10 11/9/05
102 200 11/9/05


Forecast Table.
eg.
Code Month Fcast Qty
101 P09 30
102 P09 90

Purchase Order Table.
eg.
Code Due_Date PO Qty
101 13/9/05 100

The table that I would like to create would contain Code, Date and Stock.

eg.

Code Date Stock
101 12/09/05 9
101 13/09/05 108
101 14/09/05 107 ...........

102 12/9/05 197 ...........

Any ideas/direction would be welcome.

Carl

 

[carlmack]

Pat,

Sorry I will try and explain better. This is for fast moving consumer goods. The idea is that I will use the latest forecast data and the latest stock data to look into the future and see if products are going to run out of stock (or have too much stock).

I want this to be displayed graphically (stock v time : probably in Excel) and when a user can see a negative stock situation coming up he/she will take action to try to revise a purchase order or move sales orders back.

I want to delete the data and redo the calculation every day.

I am I making more sense now ?

Carl

 

[carlmack]

Pat/anybody,

Thanks for the direction. I have set out down the ADO path using code found on previous posts but I could do with a little more advice.

I am working sequentially through the products in the stock table and need to dive into the forecast table and purchase order table to get data. Can you give me a clue how to kick off some sql and handle the records that are returned. I guess I could just loop through the both tables with if code = code but there are 000s of records.

Code so far

Sub testfcast()

Dim cn As ADODB.Connection
Dim fcode As String
Dim fqty As Single
Dim fdate As Date
Dim startdate As Date
Dim fyear As Integer
Dim fmonth As Integer
Dim fmonth_days As Integer
Dim fmonth_qty As Single
Dim rsstock As ADODB.Recordset
Dim rsp_order As ADODB.Recordset
Dim rsforecast As ADODB.Recordset
Dim rsf_result As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rsstock = New ADODB.Recordset
Set rsp_order = New ADODB.Recordset
Set rsforecast = New ADODB.Recordset
Set rsf_result = New ADODB.Recordset

rsstock.Open "stock", cn
rsp_order.Open "p_order", cn
rsforecast.Open "forecast", cn
rsf_result.Open "f_result", cn, adOpenDynamic, adLockOptimistic

' start date will come from a form or query but hard coded for testing
startdate = #9/20/2005#

rsstock.MoveFirst
Do While Not rsstock.EOF
fdate = startdate
fqty = rsstock![stock]
Do While startdate + 60 > fdate
fmonth = Month(fdate)
fyear = Year(fdate)
' HELP need sql query on forecast table to get forecast for current month - need to do same with orders but may have multiple records returned
fmonth_qty = 30
' HELP is there a function to find out how many days in a month ?
fmonth_days = 30
fqty = fqty - (fmonth_qty / fmonth_days)
rsf_result.AddNew
rsf_result!

 = rsstock![code]
        rsf_result![fstock] = fqty
        rsf_result![Date] = fdate
        rsf_result.Update
        fcode = rsstock![code]
        fdate = fdate + 1
        Loop
    rsstock.MoveNext
    Loop

rsstock.Close
rsp_order.Close
rsforecast.Close
rsf_result.Close

Set rsstock = Nothing
Set rsp_order = Nothing
Set rsforecast = Nothing
Set rsf_result = Nothing

End Sub