Page 1 of 7 123 ... LastLast
Results 1 to 15 of 100
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: Count column and query

    Hello, im importing a .csv file into a table and running queries on the fly. I have 2 static columns and the rest are dynamic. I want to count the amount of fields from the original import, remove the 2 static fields from the count, then use the count number to make that amount of column queries.

    So if i have 8 columns, i want to remove the first 2, then use the count to run my queries on the last 6 columns. This output should then be stored to a workbook, with each different set of queries for each column being stored in its own worksheet.

    At the moment I have the data imported into the table, the queries setup, the count function written, and 1 set of queries being written into a workbook.

    I need help making the count function apply to the export process. Has anyone any idea what im talking about?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could use DAO tabledefs and fielddefs to generate your metadata. What does your current export function look like?
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Feb 2006
    Posts
    213
    Heres the export code

    Code:
    Sub ManageExports()
    On Error GoTo ExportData_Error
     
    'Excel objects to manipulate Excel
        Dim exApp As Excel.Application
        Dim exBook As Excel.Workbook
        Dim exSheet As Excel.Worksheet
        Dim RowNo As Integer
        
    'String for QName
        Dim QName As String
     
    'Integer for the row numbers
        Dim StartRow As Integer
     
    'Iterant for misc loops
        Dim i As Integer
     
    'Instantiate the excel objects
        MsgBox "The export process will now begin, this should take a couple of minutes"
        
        Set exApp = New Excel.Application
        
        Set exBook = exApp.Workbooks.Open("C:\temp\Template.xls")
        exApp.Visible = True
     
        exApp.Interactive = False
        
        Set exSheet = exBook.Worksheets(1)
        
        exSheet.Cells(6, 3).Formula = "Location"
        
        exSheet.Cells(10, 3).Formula = Date
    'Loop to call the export process, passing a different query name each time - make the upper limit the number of queries you have.
        For i = 1 To 4
     
            If i = 1 Then QName = "TotalNumberOfTimesteps": StartRow = 14
            If i = 2 Then QName = "NumberOfSpillTimestepsOver01": StartRow = 16
            If i = 3 Then QName = "SpillTimestepAsA%OfTotalTimesteps": StartRow = 18
            If i = 4 Then QName = "NumberOfHoursSpill": StartRow = 20
            
            
    '......
    'and so on for all queries
     
    'Call export proc
            Call ExportData(QName, StartRow, 3, exSheet)
     
        Next i
     
    'Save it
        exBook.SaveAs "C:\Temp\SpreadsheetOuput.xls"
        
    exApp.Visible = True
     
    ExportData_Exit:
    'Very important - always account for in error trap
        exApp.Interactive = True
     
    'Clean Up
        Set exSheet = Nothing
        Set exBook = Nothing
        Set exApp = Nothing
     
        Exit Sub
    ExportData_Error:
     
        MsgBox Err.Description
        Resume ExportData_Exit
     
    End Sub
     
    Sub ExportData(QName As String, ByRef StartRow As Integer, ColNo As Integer, ByRef exSheet As Excel.Worksheet)
     
    'DAO objects to get the data
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
     
    'variables to use for formatting loops
        Dim NoOfCols As Integer
        Dim NoOfRows As Integer
     
    'Iterant for misc loops
        Dim i As Integer
     
        Set db = Application.CurrentDb
     
    'Get the data
        Set rs = db.OpenRecordset(QName)
     
        NoOfRows = rs.RecordCount
        If Not rs.EOF Then rs.MoveLast: rs.MoveFirst
     
    'Populate the variables
        NoOfCols = rs.Fields.Count
        NoOfRows = rs.RecordCount
     
    'Pop the data into Excel
        exSheet.Range(ExcelCodes(ColNo) & StartRow).CopyFromRecordset rs
     
    'Write in the column headings
    '    For i = 0 To NoOfCols - 1
    '
    '        exSheet.Cells(StartRow - 1, i + 1).Value = rs.Fields(i).Name
    '
    '    Next i
    'No need to format anymore as we are only concerned with getting data into a proforma file.
    'Use our variables to format the data populated cells ONLY
    '    exSheet.Cells.Range("A" & StartRow - 1, ExcelCodes(NoOfCols) & StartRow - 1).Interior.Color = vbYellow
     
    'And again - using both this time
    '    exSheet.Cells.Range("A" & StartRow - 1, ExcelCodes(NoOfCols) & (StartRow + NoOfRows - 1)).Borders.Color = RGB(0, 0, 0)
     
    'Adjust column widths
        exSheet.Columns.EntireColumn.AutoFit
     
    'No need to reclculate - the row number is now fixed by the template
    'Return the new starting value
    '    StartRow = StartRow + NoOfRows + 4 '4 is an arbitary number of rows to put between the result sets
     
    'Clean Up
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
     
    End Sub
    
    Function ExcelCodes(ByVal intColNo As Integer) As String
     
        Dim strCol As String
     
        Do While intColNo > -1
            If intColNo > 26 Then
                strCol = Chr(64 + ((intColNo - 1) \ 26))
                intColNo = intColNo - (26 * ((intColNo - 1) \ 26))
            Else
                strCol = strCol & Chr(64 + intColNo)
                Exit Do
            End If
        Loop
     
        ExcelCodes = strCol
     
    End Function
    and heres what ive got so far for the count


    Code:
    Public Sub addtablenames()
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
     
        Set db = CurrentDb
        Set tdf = db.TableDefs("tblStagingTable")
     
        For Each fld In tdf.Fields
            Debug.Print fld.Name
        Next
    
        Debug.Print tdf.Fields.Count
     
        Set tdf = Nothing
        Set db = Nothing
    End Sub

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That export function looks a little overly complex for what you described, you're on the right track with getting your field counts.

    could you provide a bit more detail about what you're storing in the excel worksheets? I'm curious about what those queries look like too. There could be a much more elegant way of getting the data you want...
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Feb 2006
    Posts
    213
    The orginal .csv file that is imported in the database is the results of a 10 year rainfall cycle.

    The data is Time, Seconds, then several nodes (locations where the rainfall was measured from).

    The amount of nodes varies with each .csv.

    The imported data now has 7 queries run on each node.

    I want the results of these queries to be exported into a formatted spreadsheet template.

    With each node having its own worksheet but all nodes being in 1 workbook.

    - I'll attach a small version of the database and some test data so you can see what it looks like. Just unzip em both, run db, goto import, and select the test data.
    Attached Files Attached Files
    Last edited by marleyuk; 03-09-06 at 10:43.

  6. #6
    Join Date
    Feb 2006
    Posts
    213
    Is there a way to use my column count as i import data in to a holding table? If i can will i be able to store the count result and recall this later in a statement that tell the database how many columns to query?

  7. #7
    Join Date
    Feb 2006
    Posts
    213
    bump bump bump

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Marley

    I've been watching you continue to struggle with this for the last week and I can't sit back and watch any longer

    Good job on the column counting function btw.

    Please can you post your db pls (usual rules - dump the data)?

    BTW - I don't think your last post is the way to go
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You know, I have this REALLY asinine question: Could you not PULL the data from Excel using the File->Get External Data importer? Then make a form/routine that can extract the raw data from that table for your purposes ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Feb 2006
    Posts
    213
    Thanks. Ive been spending about 10 hours a day researching it but im stuggling.

    Its attached here.
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    You know, I have this REALLY asinine question: Could you not PULL the data from Excel using the File->Get External Data importer? Then make a form/routine that can extract the raw data from that table for your purposes ...
    Not asinine at all - in fact I would welcome any input. I put in a lot of work on the last two Marley threads but I am aware that I cast a long shadow over his rather idiosyncratic and specific requirements. As it happens the initial import of the table is the one thing I think we actually nailed last time round!

    Marley - I have changed things to how I believe you want\ need them.
    1) The importing table is now normalised.
    2) The importing routine now normalises your data as it is imported into the table.
    3) I have deleted all your old queries (they are no use now other than as a reference).
    4) I have recreated one query to give you an idea of how your new table structure needs to be queried. Note how much simpler the SQL syntax is now the table is normalised!

    You must now write all your required queries and be certain they are all spot on before we carry on. The culmination of 150 posts last time was to find the queries were all wrong.

    BTW - I see you have read up on debugging code - good job.
    Attached Files Attached Files
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Not asinine at all - in fact I would welcome any input. I put in a lot of work on the last two Marley threads but I am aware that I cast a long shadow over his rather idiosyncratic and specific requirements. As it happens the initial import of the table is the one thing I think we actually nailed last time round!

    Marley - I have changed things to how I believe you want\ need them.
    1) The importing table is now normalised.
    2) The importing routine now normalises your data as it is imported into the table.
    3) I have deleted all your old queries (they are no use now other than as a reference).
    4) I have recreated one query to give you an idea of how your new table structure needs to be queried. Note how much simpler the SQL syntax is now the table is normalised!

    You must now write all your required queries and be certain they are all spot on before we carry on. The culmination of 150 posts last time was to find the queries were all wrong.

    BTW - I see you have read up on debugging code - good job.
    Dan. Reason I asked is because I have a billing function(process) that comes from Excel. My prob is that the Excel lib is not available for the 2000 version of A that I'm using (it was stripped out - go figure!) So, the ONLY way I could get the data in was to use the import function and then have my processing form massage the data a bit and run the process operation ...

    Here's the point: right now someone has to decide which file(s) to extract from right? Can this be done one at a time? How idiot proof does this need to be?What about this: (this is MY current spec) Import excel file to a SPECIFIC predefined table name. Massage the data (add a boolean column). Process data. The routine cleans up after itself ...

    How about it Marley????
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Feb 2006
    Posts
    213
    The user will choose a file to import. This file is added to a table, queried then added to a formatted worksheet. As long as my current method works, then this is ideal.


    Poot? For some reason the onclick events dont work (I havent touched anything!). I just simply dled it and opened it.

    Any idea what has been changed?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by marleyuk
    Poot? For some reason the onclick events dont work (I havent touched anything!).
    Lol - do I tell you off that much?

    Quote Originally Posted by marleyuk
    Any idea what has been changed?
    Yup. Works for me (obviously - otherwise I wouldn't have posted). I forgot point 5

    5) I have commented out the export code as this will now fail due to your schema changes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2006
    Posts
    213
    Right..Ive had a go at making these queries. They wont all be correct, i couldnt test them because the import function isnt working but ill post what i have and you might be able to spot any obvious errors.



    Query 1 - Number of spill timesteps >0.001

    SELECT TestName, Count(*) AS Number_Of_Spill_Timesteps_Over_01
    FROM tblImportTableTest
    WHERE Spill>0.001
    GROUP BY TestName;

    ----------------------------------------

    Query 2 - Total Number Of Timesteps

    SELECT COUNT([Seconds])
    FROM tblImportTableTest;

    --------------------------------------

    Query 3 - Spill Timestep As A % Of Total Timesteps

    SELECT COUNT(Spills) from tblImportTableTest/COUNT([Seconds]) ) * 100 AS Percentage_Spills
    FROM tblImportTableTest;

    --------------------------------------

    Query 4 - Number Of Hours Spill (Depends on the output timestep)

    SELECT Count([Seconds]) AS CountOfSeconds, [Seconds]/3600 AS MyHours
    FROM tblImportTableTest
    GROUP BY [Seconds]/3600;

    --------------------------------------

    Query 5 - Number Of Descrite Spills

    Not sure. (A discrete spill is when the spill stops and starts again later in the column.)

    ------------------------------------

    Query 6 - Total Spill Volume (Only of timesteps where spill rate is >0.001)

    SELECT [TestName], Count(Spill) AS TotalSpillVolume
    FROM tblImportTableTest
    WHERE Spill>0.001
    GROUP BY [TestName];

    -------------------------------------

    Query 7 - Peak Spill Rate (In litres per second)

    Not sure.




    This is what each query should actually do.

    1. Number of spill timesteps >0.001
    Is this the number of records where any value is greater than this value, the total is greater

    2. Total number of timesteps
    Total rows minus the header row

    3. Spill timesteps as a % of total timesteps
    id there are 1000 spills out of 2500 timesteps then its 1000 / 2500 * 100%

    4. Number of hours spill (depends on the output timestep)
    If the timestep is an hour then the total number hours spill is the same as the timestep, but if the short or longer it needs to be factored.

    5. Number of discrete spills
    A discrete spill is when the spill stops and starts again later in the column.

    6. Total spill volume (only of timesteps where spill rate is >0.001)
    A total of spills > 0.001. Again is this considered per node or for all nodes in a timestep

    7. Peak spill rate in litres per second.
    Per node




    As for the onclick error, i downloaded it again and same problem - The expression On Click you entered as the event property setting, produced the following error: Cant find project or library.
    Last edited by marleyuk; 03-14-06 at 05:13.

Posting Permissions

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