Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Unhappy Unanswered: MS Access summarising data

    HELP!! I have a database which holds the inofrmation we gather when we scan our stock held at a remote warehouse. We scan the barcode and enter how many of each item are left.

    This puts the following info into an access table: -

    Department
    PartNumber
    Description
    DateOfScan
    TimeOfScan
    StockAtTimeOfScan

    One part may exist in a few departments.

    I need to find a way of telling me what the usage per part per dept is PER MONTH and PER YEAR

    There must be an easy way but its mashing my head

    Help anybody please...

    Thanks
    Paul

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Paul,
    a cross tab query would help you here there is a wizard for the fields and you can use formulas to pull the year and month

    Dave

  3. #3
    Join Date
    Nov 2004
    Posts
    10

    Thanks for the reply

    i have used the cross tab but i need to get usage for the month - so if the stock is in weeks

    5 4 2 4
    then the usage is
    1 from week 1 to 2
    2 from week 2 to 3
    then we restock up to 4

    therefore total usage has been 3 for the month so far and
    restock has been 2

    confused i am....

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ignore that im wrong, this will need more thought maybe creating a table to hold intermediate values comparing 2 record sources this would be a doddle in excel but is not as easy in access, Ill get my thinking cap on
    Last edited by DavidCoutts; 12-01-04 at 07:51.

  5. #5
    Join Date
    Nov 2004
    Posts
    10

    Thanks David

    Thanks David i appreciate your thinking....

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    would it be ok to do this in excel (with access controling everything) i could probably get a soloution by tommorrow if you are willing to let me do this?
    my Excel skills far outstrip my access skills,
    I think the main problem im having is that you have to compare each individual row against each other and thats not something that access is really designed to do

    Dave

  7. #7
    Join Date
    Nov 2004
    Posts
    10

    excel is my tool of chioce as well

    david i am also much better at excel - your help is really appreciated

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Here's a start at creating a table (the codes shocking as i cant spend to much time looking at it) you should be able to do a join on your table and this new one and count the numbers of usage
    Code:
    Sub CrtTable()
        'object Variables
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim tdfUsage As TableDef
        Dim tdfNew As TableDef
        Dim qdftemp As QueryDef
        
        'Other Variables
        Dim RecNumber As Long
        Dim myUsage As String, myRestock As String, myPart As String
        Dim bolFirst As Boolean, bolStop As Boolean
        Dim strSQL As String
        Dim prevPart() As String
        
        'Turn Warnings off
        DoCmd.SetWarnings False
        'set reference
        Set db = CurrentDb
        
        'delete table if it exists
        For Each tdfNew In db.TableDefs
            If tdfNew.Name = "tblUsage" Then
                db.TableDefs.Delete tdfNew.Name
            End If
        Next tdfNew
        
        'create the new table
        strSQL = "create table tblUsage" & _
                "( PK int" & _
                ", Usage int" & _
                ", Restock int)"
        DoCmd.RunSQL strSQL
        
        'create recordset
        On Error Resume Next
        db.QueryDefs.Delete "Temp"
        On Error GoTo 0
        Set qdftemp = db.CreateQueryDef("Temp", "SELECT * FROM tblstock ORDER BY DateofScan")
        
        Set rs = db.OpenRecordset(qdftemp.Name)
        
        bolFirst = True
        With rs
            Do
            
                .MoveFirst
                If bolFirst = True Then
                    'get first value
                    myPart = .Fields("Partnumber").Value
                    RecNumber = 1
                Else
                    'move to the next field
                    Do
                        bolStop = False
                        .MoveNext
                        'get next part number
                        myPart = .Fields("Partnumber").Value
                        'if part number already exsits get a new part number
                        For i = 1 To UBound(prevPart)
                            If myPart = prevPart(i) Then
                                bolStop = True
                                Exit For
                            End If
                        Next i
                        If .EOF Then Exit Do
                    Loop Until bolStop = False
                    'test to see that last recod there
                    If bolStop = True Then GoTo clseSub
                End If
                Do Until .EOF
                    myval = .Fields("StockAtTimeOfScan").Value
                    On Error Resume Next
                    Do
                        .MoveNext
                        If Err.Number <> 0 Then
                            bolStop = True
                            Exit Do
                        End If
                    Loop Until myPart = .Fields("PartNumber").Value
                    If bolStop = False Then
                        'calculate values
                        If myval - .Fields("StockAtTimeOfScan").Value > 0 Then
                            myUsage = Abs(myval - .Fields("StockAtTimeOfScan").Value)
                            myRestock = 0
                        Else
                            myUsage = 0
                            myRestock = Abs(myval - .Fields("StockAtTimeOfScan").Value)
                        End If
                        'put values into new table
                        strSQL = "INSERT INTO tblUsage (PK,Usage,Restock) " & _
                                    "VALUES(" & .Fields("ID").Value & ", " & myUsage & ", " & myRestock & ");"
                        DoCmd.RunSQL strSQL
                    End If
                Loop
                'reset variables
                bolStop = False
                bolFirst = False
                ReDim Preserve prevPart(RecNumber) As String
                prevPart(RecNumber) = myPart
                RecNumber = RecNumber + 1
            Loop Until RecNumber = .RecordCount
        End With
        
    clseSub:
        'Get rid of Object Variables
        Set db = Nothing
        Set rs = Nothing
        Set tdfUsage = Nothing
        Set tdfNew = Nothing
        Set qdftemp = Nothing
    End Sub
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •