Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: Expand to Monthly

    I have a table (tblMonths) that looks like this:

    Months Percent
    2 .05
    2 .10
    2 .15
    6 .70

    It always adds up to 12 months. I want to create a query that will expand those 4 records to 12 records as follows:

    Month Percent
    1 .05
    2 .05
    3 .10
    4 .10
    5 .15
    6 .15
    7 .70
    8 .70
    9 .70
    10 .70
    11 .70
    12 .70

    Seems simple but I cannot figure it out. Thanks for any help.

    Vince

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You'll need to use a VBA script to manipulate the data to 'expand' out the (effectively) quantities.

    How good are you with VBA?

    You'll need to open a recordset to search through the 'Months' field, checking if its value is greater than 1. If it is, then it needs to be inserted into a new 'tmp' table. Once the recordset hits the end of the line, the script can then import everything back from the 'tmp' table.

    There are other ways to achieve these results, but this is probably the simplest.

    You'll also need to keep the order going, probably easiest to insert the data into the 'tmp' table with an autonumber setup to count the months for you.

  3. #3
    Join Date
    Feb 2009
    Posts
    4
    Thanks for the quick response. Yes, I know some VBA and I can probably figure it out. However, I was hoping to avoid that and use a query instead. I've done something similar before where I do a right join from a smaller table (less records) to a larger table and achieved the "expansion" I am referring to.

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Unfortunately I'm more a VBA man than an SQL one, so LEFT JOIN and things are little out of my comfort zone.

    I started learning Java and C/C++ a long time ago, and SQL just isn't powerful enough for me to use properly.

    I wrote a function a while back in VBA that I used to achieve something similar (although without the months). Here's the code:

    Code:
    Public Function numQuantify(aTable As String)
    
    Dim strQntSQL As String
    Dim rs As DAO.Recordset
    Dim id As Long
    Dim iQnty As Long
    Dim sDate As Date
    Dim iOrderNum As Long
    Dim sCompany As String
    Dim sCode As String
    Dim i As Long
    
    strQntSQL = "SELECT * " & _
                "FROM " & aTable
    
    Set rs = CurrentDb.OpenRecordset(strQntSQL)
    rs.MoveLast
    rs.MoveFirst
    
    Do While Not rs.EOF
        iQnty = rs.Fields("Quantity").Value
        If (iQnty > 1) Then
            rs.Edit
            rs.Fields("Quantity").Value = 1
            rs.Update
    
            'Requires update with each field added
            sDate = rs.Fields("Order Date").Value
            iOrderNum = rs.Fields("Order Number").Value
            sCompany = rs.Fields("Company").Value
            sCode = rs.Fields("Product Code").Value
            
            For i = iQnty To 2 Step -1
                rs.AddNew
                rs("Order Date").Value = sDate
                rs("Order Number").Value = iOrderNum
                rs("Company").Value = sCompany
                rs("Product Code").Value = sCode
                rs("Quantity").Value = 1
                rs.Update
            Next i
        End If
        rs.MoveNext
    Loop
    
    'Tidy up to avoid memory leaks
    rs.Close
    Set rs = Nothing
    
    End Function
    Will require a bit of adaptation for your use, like moving them to a newly created table with autonumbering for the months, clearing the existing table, and reinserting them back with the autonumber values carried over.

    Nothing special, but thought it might help a little.

  5. #5
    Join Date
    Feb 2009
    Posts
    4
    Thanks! Thats a great start. I'll see if I can work with that.

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    No worries.

    If you need some more help (I didn't really comment my code, and it could be shortened a little bit [which would make it more complicated to follow]), I'm more than happy to oblige.

Posting Permissions

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