Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: crosstable query

    I have a crosstab query whose results are put into a table. I created this table by running another make-table query with my crosstab query's information. So the line of information goes something like this:

    tblMain (original table) - qryAging (crosstab query) - qryCreateAging (make-table query) - tblAging (end result).

    This creates the table but I noticed that if i try to query from the new table (tblAging) Access will not recognize the table's format. Its as if the crosstab part was removed. Can someone explain this?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by cc3658
    Can someone explain this?
    Just a hunch...
    Quote Originally Posted by cc3658
    ...the crosstab part was removed.


    On a more serious tip... Have you checked the table to ensure it is indeed formated they way you intended, and that it retained the data you expected?
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Yes- ( I referenced the wrong table before- its tblTotals) I usually export this table to Excel (manually- TOOLS:OFFICE LINKS:ANALYZE WITH MS EXCEL) The information has always gone over fine. I can see the table now with all the information i need. when I look at the design of the table- all of the fields appear the way i want it to. I was hoping to automate this procedure (transfering to Excel) with VBA. I have a line of SQL (in VBA) that I have been trying to assign to a recordset.

    strSQL5 = "SELECT * FROM [tblTotals] WHERE ([tblTotals].[Division])=" & Division(i) & ";"

    Set rs = db.OpenRecordset(strSQL5, 2)


    It looks like it works until it gets to the first number FIELD and then i get an error that says "Item not found in this collection" however, It finds the first two FIELDS of my table. I know this because in the EXCEL spreadsheet that this code is building I can see the header line that i have assigned and then the first two fields of the first record. It looks like it has trouble with the third FIELD. and i get the error. I have used this SQL string and recordset line before with great luck. What would cause ACCESS to stop creating a recordset in mid-stream?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What's this FIELD business?

    Could you post your code in it's entirety and point out exactly where it's bombing out?
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    I have 'bolded' the line where it crashes.
    The error reads 'ITEM NOT FOUND IN YOUR COLLECTION'




    Dim Division(3) As Integer
    Dim i As Integer

    Dim objXL As Object
    Dim objActiveWkb As Object
    Dim db As Database
    Dim rs As Recordset

    Dim TotalsRow As Integer

    Dim strSQL As String




    Set db = CurrentDb

    Division(1) = 248
    Division(2) = 1054
    Division(3) = 1103

    For i = 1 To 3


    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = False
    objXL.Application.workbooks.Add
    objXL.Application.Sheets.Add Count:=3
    Set objActiveWkb = objXL.Application.ActiveWorkBook


    strSQL = "SELECT [tblTotals].*,* FROM [tblTotals] WHERE ([tblTotals].[Division])=" & Division(i) & ";"

    Set rs = db.OpenRecordset(strSQL, 2)


    With objActiveWkb

    .Worksheets(1).Cells(5, 1) = "Type"
    .Worksheets(1).Cells(5, 2) = "Sales Route"
    .Worksheets(1).Cells(5, 3) = "1 - 30 #"
    .Worksheets(1).Cells(5, 4) = "1 - 30 $"
    .Worksheets(1).Cells(5, 5) = "31 - 60 #"
    .Worksheets(1).Cells(5, 6) = "31 - 60 $"
    .Worksheets(1).Cells(5, 7) = "61 - 90 #"
    .Worksheets(1).Cells(5, 8) = "61 - 90 $"
    .Worksheets(1).Cells(5, 9) = "91 - 120 #"
    .Worksheets(1).Cells(5, 10) = "91 - 120 $"
    .Worksheets(1).Cells(5, 11) = "121 - 150 #"
    .Worksheets(1).Cells(5, 12) = "121 - 150 $"
    .Worksheets(1).Cells(5, 13) = "151 - 180 #"
    .Worksheets(1).Cells(5, 14) = "151 - 180 $"
    .Worksheets(1).Cells(5, 15) = "181+ #"
    .Worksheets(1).Cells(5, 16) = "180+ $"

    TotalsRow = 6
    While rs.EOF <> True

    .Worksheets(1).Cells(TotalsRow, 1) = rs![Type]
    .Worksheets(1).Cells(TotalsRow, 2) = rs![SALES-ROUTE]
    .Worksheets(1).Cells(TotalsRow, 3) = rs![Count0f1:30] .Worksheets(1).Cells(TotalsRow, 4) = rs![SumOf1:30]
    .Worksheets(1).Cells(TotalsRow, 5) = rs![Count0f31:60]
    .Worksheets(1).Cells(TotalsRow, 6) = rs![SumOf31:60]
    .Worksheets(1).Cells(TotalsRow, 7) = rs![Count0f61:90]
    .Worksheets(1).Cells(TotalsRow, 8) = rs![SumOf61:90]
    .Worksheets(1).Cells(TotalsRow, 9) = rs![Count0f91:120]
    .Worksheets(1).Cells(TotalsRow, 10) = rs![SumOf91:120]
    .Worksheets(1).Cells(TotalsRow, 11) = rs![Count0f121:150]
    .Worksheets(1).Cells(TotalsRow, 12) = rs![SumOf121:150]
    .Worksheets(1).Cells(TotalsRow, 13) = rs![Count0f151:180]
    .Worksheets(1).Cells(TotalsRow, 14) = rs![SumOf151:180]
    .Worksheets(1).Cells(TotalsRow, 15) = rs![Count0f181]
    .Worksheets(1).Cells(TotalsRow, 16) = rs![SumOf181]

    rs.MoveNext
    TotalsRow = TotalsRow + 1
    Wend
    .Worksheets(1).Range("A5").AutoFilter
    .Worksheets(1).Columns("A:II").AutoFit

    With .Worksheets(1).PageSetup
    .PrintTitleRows = "$1:$5"
    .Orientation = xlLandscape
    .LeftMargin = 20
    .RightMargin = 20
    .TopMargin = 40
    .BottomMargin = 35
    .HeaderMargin = 17
    .FooterMargin = 17
    .RightFooter = "&D"
    End With

    .Worksheets(1).Cells(2, 3) = "DIVISION OVERVIEW"
    .Worksheets(1).Range("C2").Font.Bold = True
    .Worksheets(1).Cells(3, 3) = "Division: " & Division(i)
    .Worksheets(1).Range("C3").Font.Bold = True
    .Worksheets(1).Cells(1, 7) = "RUNDATE: " & DATE
    .Worksheets(1).Name = "OverView"

    End With

    objActiveWkb.SaveAs Filename:="F:\" & Division(i) & "AR STATUS.xls"
    objActiveWkb.Close savechanges:=True

    Set objActiveWkb = Nothing
    Set objXL = Nothing

    Next i


    End Sub

  6. #6
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Here is the half-finished EXCEL sheet:

    Type Sales Route 1 - 30 # 1 - 30 $ 31 - 60 # 31 - 60 $ 61 - 90 # 61 - 90 $ 91 - 120 # 91 - 120 $ 121 - 150 # 121 - 150 $ 151 - 180 # 151 - 180 $ 181+ # 180+ $
    COA 0

Posting Permissions

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