Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Exclamation Unanswered: Populate Array with the query results and sort it

    Good morning!

    The question is already in the thread title. The code is bound to the On-Click event of a form, Access 2003 version. Now comes the confusing part:

    The SQL string extracts fields CostCentre, CostCategory, SumOfCosts and Month from the table/another query. I'm struggling to populate the array with Month records only. It should look somewhat like this:

    31.10.2007
    31.10.2007
    31.10.2007
    30.11.2007
    31.12.2007

    Then the code should loop through the array records and create a new textbox on the form once the month changes. For example, 31.10.2007 is unequal to 30.11.2007 --> create a new textbox.

    If you don't understand why the heck anyone needs to do something this confusing and inefficient, I'll be happy to explain the whole background of the task to you - maybe, there's a more elegant solution. Arrays are just the only idea I've got.

    This what I've got so far:

    Code:
    Set db = CurrentDb()    
                       
       'SQL String to display results:
        
        strSQL = "SELECT DISTINCT [Kostenart],[KoArt_Beschreibung],Sum([Kosten]) AS [Wert], [Bis]" & _
                "FROM qryBasicSearch WHERE [KArtGr_Beschreibung] " & _
                "IN(" & Criteria & ") AND [Kostenstelle] IN(" & Criteria1 & ")" & _
                "AND [Bis]IN(#" & Periode & "#) " & _
                "GROUP BY [Kostenart], [KoArt_Beschreibung], [Bis] " & _
                "ORDER BY [Bis];"
                
         Set rst = db.OpenRecordset(strSQL)
        
        'count the records
      With rst
            i = .Fields.Count
            j = .RecordCount
        End With
        
        'populate array
      MyArray = rst.GetRows()
    
       Dim x, y As Integer
        
        'trying to display what's stored in the array
    
      For x = 0 To UBound(MyArray) + 1
            For y = 0 To j
                MsgBox MyArray(y, x)
            Next y
        Next x
    
        
        'clean up
       rst.Close
        Set rst = Nothing
    Any suggestions would be very much appreciated!!!!!!!!!!! Getting really desperate here.

    Best,
    OfficeDummy

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you thought about using a listbox with the row source of
    Code:
    SELECT DISTINCT month FROM myTable
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what would happen if you were to add a group by which specified the year and the month?

  4. #4
    Join Date
    Jan 2008
    Posts
    74
    Hello, georgev! Unfortunately that doesn't apply here. See, I have two multiselect listboxes on the form + 12 checkboxes that are Months... And I need to populate the array with the results that apply to all criterias only.

  5. #5
    Join Date
    Jan 2008
    Posts
    74
    EDIT: Sorry, I'm a bit slow today...
    That'd be possible, but does it split the results into different columns?
    Last edited by OfficeDummy; 03-10-08 at 09:44.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're determined to go this way then perhaps this will help you

    http://www.dbforums.com/showpost.php...77&postcount=5
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2008
    Posts
    74
    Thanks! But will it work for just one field of a 4-dimensional array?

    What I'd actually need would be creating a query that splits the SumOfCosts results into different columns grouped by month. E.g. 3 columns for november, december and january if those months were selected on the form. I didn't think this was possible, that's why I went into the whole array situation...

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE Month(yourDateField) IN (commaseparatedlistofvalues)
    GROUP
        BY Month(yourDateField)
         , Year(yourDateField)
    ??
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2008
    Posts
    74
    Code:
    SELECT DISTINCT [Kostenart],[KoArt_Beschreibung],Sum([Kosten]) AS [Wert], [Bis]FROM qryBasicSearch 
    WHERE [KArtGr_Beschreibung] IN("Materialkosten","Unterhaltskosten","EDV-Kosten") 
    AND [Kostenstelle] IN("3108","3115","3125")
    AND [Bis]IN(#10/31/2007#,#11/30/2007#,#12/31/2007#) 
    GROUP BY [Kostenart], [KoArt_Beschreibung], [Bis] 
    ORDER BY [Bis];
    But this yields [Wert] results in one column grouped by 31.10.2007, 30.11.2007 and 31.12.2007.

    What I need is to split the [Wert] results in three different columns, the first of which calculates only october, the second only november, and the third only december. is it possible using a single SQL query, oder do I have to sort the array in VBA?

    Thanks,
    OfficeDummy

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Sum(Kosten) AS [Wert]
         , Bis
    FROM   qryBasicSearch
    WHERE  KArtGr_Beschreibung IN ('Materialkosten', 'Unterhaltskosten', 'EDV-Kosten')
    AND    Kostenstelle] IN ('3108', '3115', '3125')
    AND    Bis IN (#10/31/2007#,#11/30/2007#,#12/31/2007#) 
    GROUP
        BY Bis
    ORDER
        BY Bis
    ??

    Languages were never my strong point
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2008
    Posts
    74
    Thanks, georgev, but it's basically the same I've got. I really need to display monthly costs (= [Wert]) in separate columns. Or try to sort that unsortable array.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you provide us with some sample data and required output?
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2008
    Posts
    74
    That's what my query looks like:

    Costs -- Month
    300 -- 31.12.2007
    20 -- 30.11.2007
    356 -- 30.11.2007
    567 -- 31.12.2007

    What I'd love to achieve is:

    December -- November
    300 -- 20
    567 -- 356

    I zipped the database below if you'd like to take a look at it. The search form is called FrmSuche_Monat; the query it's based on is called qrySearch_MonatGetrennt.
    Attached Files Attached Files

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ohhh!

    What you awnt is called a cross-tab; which I'm afraid I have never used... I imagine the query wizard might be useful to you though!
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2008
    Posts
    74
    Yes, it does something very close to what I'm trying to achieve, but I also have two other criteria on the form - how do I implement them into the crosstab?

Posting Permissions

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