Results 1 to 4 of 4
  1. #1
    Wilbert is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    2

    Stock running database where product recieve is not equal to product sold

    Hope someone can help.



    Part 1:

    There are numerous databases for stock control, but it only works on same stock in to same stock out.

    We recieve products as a specific unit and repack it to different products and it is sold to the same size units, but different names. So the stock runs on total units in versus total units out, doesnt matter what type of product was recieve to the type products sold.

    Can anyone help me on this on how to run such a database?

    Part 2:

    We receive returns on the products sold as well, and i haven't found a database that handles returns back into stock. Is there any help/info on this?

    Regards

    Wilbert

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I can help you out with the database provided I get some more details. Running Stock can be calculated so can returns. I just need some more details e.g.

    "We recieve products as a specific unit and repack it to different products and it is sold to the same size units"

    can you explain this a little bit more clearly. Regarding Sales Returns that can be handled easily.

    Please feel free to contact me at my gmail address in my signature.

  3. #3
    Wilbert is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    2
    In short

    The main unit is dozen. We recieve all products as 30 dozen units. Then it is repacked to 30, 15, 5, 4, 2.5, 2 , 1.5, 1 & 0.5 dozen sizes. The order specify the various amount of each unit size and when it comes to the total at admin and other values do we look at the amount of dozens sold only and not the various size units we sell. We recieve also the returns as the same variations of the units and this must also be calcualted back to dozens recieved.

    Other templates cannot use stock control for products that is changed from recieving to sold.

    The idea with running stock can one then determine the total amount of stock on the floor at any given time.

    Regards

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Location
    India
    Posts
    931

    Calculating Stock

    This is my understanding of your problem:

    1) Product received is in units that is 1 unit = 30 dozen
    2) Product Sold are in dozens that can be of various denominations ranging from 30 - 0.5
    3) Stock at any point of time will be calculated in Dozens.

    (I have not yet addressed the Sales returns issue which I will if my current assumptions and understanding of your problem is correct)

    I have very limited information to your approach and I will try to demonstrate the solution with a very simple example:

    I have three tables:

    Table 1: tbl_in
    ID (Auto Number PK), in_date (Date/Time), in_units (Number) (assumed 1 unit = 30 dozen)

    Table 2: tbl_out
    ID (Auto Number PK), in_date (Date/Time), in_units (Number) (assumed unit are in dozen)

    Table 3: Table 1
    ID (Auto Number PK), stock_date (Date/Time), stock_units (Number) (dozen)



    I have a Form with two Text Boxes where I type my starting date and ending date. I have a command Button with the following Code:

    Code:
    Private Sub Command0_Click()
    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim myDate As Date
    Dim myCounter As Integer
    Dim mystockin As Integer
    Dim mystockout As Integer
    
    'Clears Data from the Table for Current Data
    
    strSQL = "Delete * From Table1"
    CurrentDb.Execute strSQL, dbFailOnError
    
    
    myCounter = -1
    Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
    
    Do While Not myDate = Me.Text3
    myCounter = myCounter + 1
    
    myDate = DateAdd("d", myCounter, Me.Text1)
    mystockin = IIf(IsNull(DSum("[in_units]", "tbl_in", "[in_date]<=#" & myDate & "#")), 0, DSum("[in_units]", "tbl_in", "[in_date]<=#" & myDate & "#"))
    mystockout = IIf(IsNull(DSum("[out_units]", "tbl_out", "[out_date]<=#" & myDate & "#")), 0, DSum("[out_units]", "tbl_out", "[out_date]<=#" & myDate & "#"))
    rst.AddNew
    rst!stock_date = myDate
    'Assuming 1 unit received = 30 dozens
    'Assuming Product Sold are recorded in dozens
    rst!stock_units = (mystockin * 30) - (mystockout)
    rst.Update
    Loop
    rst.Close
    Set rst = Nothing
    End Sub

    What does the Code do:

    1) Creates Dates Between the Start and the End Dates in Table Table 1 e.g. Starting Date 01/01/2013 End Date 15/01/2013 all dates in between are auto populated and the stock on that date is populated in the field stock_units.

    rst!stock_units = (mystockin * 30) - (mystockout)

    I have multiplied mystockin with 30 as 1 unit received = 30 dozens.

    2) Code Clears old data and populates the table with current data each time you type the Starting Date and Ending Date and run the code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Product Order/Pricing Database
    By atg in forum Database Design
    Replies: 3
    Last Post: 06-28-2012, 05:45 AM
  2. product hierarchy
    By Lata in forum Access
    Replies: 13
    Last Post: 08-03-2011, 02:56 AM
  3. Total by product
    By Alexpi in forum Queries
    Replies: 1
    Last Post: 05-24-2011, 03:19 PM
  4. Update product parts
    By honey2wood in forum Forms
    Replies: 17
    Last Post: 04-15-2011, 02:20 AM
  5. Product / Stock Management
    By dale.90 in forum Access
    Replies: 1
    Last Post: 09-30-2010, 11:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums