Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: Generate an interesting report ...

    Hi all.

    Well ... Got an interesting one here ... I'm trying to develop a report with 5 columns where the items arrange in alphabetical order BY COLUMN ... Example:

    [/CODE]
    Col1 Col2 Col3 Col4 Col5

    AA DD FF GG HH
    AB DE FG GH HI
    AC DF FH GI HJ
    AD DG FI GJ HK
    [\CODE]

    I have alphanumeric codes to display in columnar order ordered by column from left to right ... Any ideas on doing this evil report????

    Thanks a bunch.

    - Mike
    Back to Access ... ADO is not the way to go for speed ...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Duh....

    Code:
    Order By col1, col2, col3, col4, col5
    I suspect I have missed something. Is that the output you want or the unsorted column contents? If the latter, what do you hope to get?

    Ah - ok - I got it - you want to order the columns independantly of each other? We are looking at 5 sorted columns not 4 sorted rows?

    Ok - challenage accepted
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Duh....

    Code:
    Order By col1, col2, col3, col4, col5
    I suspect I have missed something. Is that the output you want or the unsorted column contents? If the latter, what do you hope to get?

    Ah - ok - I got it - you want to order the columns independantly of each other? We are looking at 5 sorted columns not 4 sorted rows?

    Ok - challenage accepted
    NOT QUITE Dan ... The column names are the OUTPUT on the report ... The data is a list of UOM Codes ... A single column. The trick is to put the codes in alpha order in 5 columns on the report from left to right ... Look at my example again. See how they are in alpha order from left to right down the page?
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I hate to say it: But, this is a report (generally) that I've seen numerous posters request help on (usually students too boot) ... I've never bothered helping before now because I've not had to write one (plus it is difficult to do right). Well now I have to ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    NOT QUITE Dan ... The column names are the OUTPUT on the report ... The data is a list of UOM Codes ... A single column. The trick is to put the codes in alpha order in 5 columns on the report from left to right ... Look at my example again. See how they are in alpha order from left to right down the page?
    Eek - so that sinlge column also needs to always be evenly divided into 5 columns? Each column will be (Total no. of rows/5) deep? Are there a fixed number of rows or is it variable? And can we use VB ?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Eek - so that sinlge column also needs to always be evenly divided into 5 columns? Each column will be (Total no. of rows/5) deep? Are there a fixed number of rows or is it variable? And can we use VB ?
    YES

    Variable # of rows (just to make it interesting )

    Use anything you want ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    will there always be the same number of "rows" for each column? Ie: do you expect to see nulls at the end of certain columns?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    will there always be the same number of "rows" for each column? Ie: do you expect to see nulls at the end of certain columns?
    Yes. If the # of items is less than a full row then the lastmost cells of the last row will not be filled.

    Like I said: this is a queer duck ... It displays ordered by column from left to right BUT it fills by row (left to right and top to bottom) ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would cheat and manually write values to an excel object, then suck it back in if you need further processing. Since there's no real relationship between any of the values in a given row, it's pretty tricky to define how to put them together to a relational database.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    I would cheat and manually write values to an excel object, then suck it back in if you need further processing. Since there's no real relationship between any of the values in a given row, it's pretty tricky to define how to put them together to a relational database.
    Tricky certainly - I've gotta leave work now but I'm 75% there (....i think....). Messy though - as you say the requirements pretty well violate the fundamentals of a relational database so "relating" them is tricky. I'm playing with a table created at run time with an autonumber to create an artificial relationship.

    Hey - this isn't homework is it Mike?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Tricky certainly - I've gotta leave work now but I'm 75% there (....i think....). Messy though - as you say the requirements pretty well violate the fundamentals of a relational database so "relating" them is tricky. I'm playing with a table created at run time with an autonumber to create an artificial relationship.

    Hey - this isn't homework is it Mike?
    No ... I hope not. I did grad from college 15 years ago ... This is for work.

    Dan, why are you relating???
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yeah, I was persuing a similar value. I was basically giving a "rank" or "order id" to each value by slapping them in a temporary table to create integers, then left join all the values together to create rows.

    It would be far less of a headache to create an excel object, pull a recordset for each unique column sorted ascending, loop through each record and append the appropriate values into the spreadsheet object, then push the entire object into a temporary table to do what you will. It saves a couple steps that the ranking process requires.

    Strange that both methods use almost the exact same logic though, I wonder which will turn out to have less overhead...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Confession time: I already wrote the report this morning ... I was (and am) looking for perhaps a better way of doing it than I did ...

    As with every report of mine, I have a template table that it is bound to. So, I create a temp table and run-time bind the report to that temp table ... Following is the code I wrote to do this report:
    Code:
    Function RetrieveUOM() As Integer
        On Error GoTo Err_RUOM
        
        Dim UOMCount As Long, RptRowCount As Long, Ndx As Long, OffsetNdx As Long, ColNdx As Long
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorLocation = adUseClient
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        
        ' Delete any existing records ...
        SQLString = "DELETE FROM " & RptTbl & ";"
        CurrentProject.Connection.Execute SQLString, , adCmdText
        
        UOMCount = 0
        RptRowCount = 0
        
        ' Retrieve the # of UOM Codes
        SQLString = "SELECT COUNT(*) FROM [Units Of Measure];"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            UOMCount = MyRecSet.Fields(0).Value
        End If
        MyRecSet.Close
        
        If UOMCount <> 0 Then RptRowCount = UOMCount / 5 + IIf(UOMCount Mod 5 <> 0, 1, 0)
        
        ' Retrieve the UOM Codes
        SQLString = "SELECT * FROM [Units Of Measure] ORDER BY [UOM Code] ASC;"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            Ndx = 0
            While MyRecSet.EOF = False
                If Ndx < RptRowCount Then
                    SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
                    TrgRecSet.Open SQLString, CurrentProject.Connection
                    TrgRecSet.AddNew
                    ' Row #
                    TrgRecSet.Fields(0).Value = Ndx
                    ' 1st UOM Code
                    TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value & ""
                    ' 1st Description
                    TrgRecSet.Fields(2).Value = MyRecSet.Fields(2).Value & ""
                    ' 2nd UOM Code
                    TrgRecSet.Fields(3).Value = ""
                    ' 2nd Description
                    TrgRecSet.Fields(4).Value = ""
                    ' 3rd UOM Code
                    TrgRecSet.Fields(5).Value = ""
                    ' 3rd Description
                    TrgRecSet.Fields(6).Value = ""
                    ' 4th UOM Code
                    TrgRecSet.Fields(7).Value = ""
                    ' 4th Description
                    TrgRecSet.Fields(8).Value = ""
                    ' 5th UOM Code
                    TrgRecSet.Fields(9).Value = ""
                    ' 5th Description
                    TrgRecSet.Fields(10).Value = ""
                    TrgRecSet.Update
                Else
                    OffsetNdx = (Ndx Mod RptRowCount)
                    ColNdx = Ndx \ RptRowCount
                    SQLString = "SELECT * FROM " & RptTbl & " WHERE (Row=" & OffsetNdx & ");"
                    TrgRecSet.Open SQLString, CurrentProject.Connection
                    If TrgRecSet.BOF = False Then
                        TrgRecSet.MoveFirst
                        Select Case ColNdx
                            Case 1
                                ' 2nd UOM Code
                                TrgRecSet.Fields(3).Value = MyRecSet.Fields(1).Value & ""
                                ' 2nd Description
                                TrgRecSet.Fields(4).Value = MyRecSet.Fields(2).Value & ""
                            Case 2
                                ' 3rd UOM Code
                                TrgRecSet.Fields(5).Value = MyRecSet.Fields(1).Value & ""
                                ' 3rd Description
                                TrgRecSet.Fields(6).Value = MyRecSet.Fields(2).Value & ""
                            Case 3
                                ' 4th UOM Code
                                TrgRecSet.Fields(7).Value = MyRecSet.Fields(1).Value & ""
                                ' 4th Description
                                TrgRecSet.Fields(8).Value = MyRecSet.Fields(2).Value & ""
                            Case 4
                                ' 5th UOM Code
                                TrgRecSet.Fields(9).Value = MyRecSet.Fields(1).Value & ""
                                ' 5th Description
                                TrgRecSet.Fields(10).Value = MyRecSet.Fields(2).Value & ""
                        End Select
                        TrgRecSet.Update
                    End If
                End If
                TrgRecSet.Close
                Ndx = Ndx + 1
                MyRecSet.MoveNext
            Wend
        End If
        MyRecSet.Close
        
        Set TrgRecSet = Nothing
        
        RetrieveUOM = 1
    Exit_RUOM:
        Exit Function
        
    Err_RUOM:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveUOM = 0
        Resume Exit_RUOM
    End Function
    Basically, I count the # of records. divide by 5. if a remainder add 1 more row.

    Then it gets interesting: I write out the 1st column of each row. After that, I determine by the record number which cell to place the value in ...

    Done.
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    My solution works for a variable number of columns too...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    My solution works for a variable number of columns too...
    The key being "Variable # of columns" ... You know full well that is not a healthy thing to try to do in Access Ted ...

    Also there per se is no "ranking" involved here ... The query orders the records so all that is needed is and counter for the row assignment ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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