Results 1 to 5 of 5

Compile Stats (number of records across several tables)

  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    31

    Compile Stats (number of records across several tables)


    Within our Access DB, my team imports several files each month which each go into a different table.
    The majority of these files are Excel Spreadsheets located on a network drive. I have a module in the DB that uses the "DoCmd.TransferSpreadsheet acImport" function to find the file (in a specified location), and import into a specified table.
    What I'm looking to do is come up with a way to dump some statistics - concerning the number of records imported -into a single spot.
    So in other words, let's say I have 10 different files imported into the database on the 1st of each month - and I want to see how many records were imported into each of those tables.
    Now obviously, I can create 10 very basic queries using the "COUNT" function. But I'd like to avoid that if possible. Is there a fairly easy way to program something wherein the number of records in each of these tables can be dumped into a single table?
    I'm picturing a single table with 10 fields with the "number" data type - each one representing the total # of records in these 10 tables.

    Any help is appreciated as always.
    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,797
    Import the 10 files into 1 access table.
    run count on that.
    or
    append the count of the 10 XL files into 1 access table. Get total from that.

  3. #3
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    38
    VBA code can run 10 count queries (coded in the module, not saved queries) like this:

    set QueryRst1=db.OpenRecordset("select count(*) as [row count] from [table1]")
    set QueryRst2=db.OpenRecordset("select count(*) as [row count] from [table2]")
    etc.

    TotRst.addnew
    TotRst![count1]=QueryRst1![row count]
    TotRst![count2]=QueryRst2![row count]
    etc.
    TotRst.Update

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,695
    Create a table to hold the import statistics:
    tblStatistics
    ------------
    StatisticID_PK - Autonumber
    ImportFileName - Text
    ImportTableName
    ImportDate - Date/Time
    BeforeImportRecCount - Long
    AfterImportRecCount - Long

    Then modify your code; you will have to insert the lines in the proper place:

    WARNING: The following is Air code!
    Code:
    Dim d as DAO.Database
    Dim BeforeImport as Long
    Dim AfterImport as Long
    DimRecDiff as Long
    Dim sSQL as String
    
    
    Set d = Currentdb
    
    'First Import
    BeforeImport = d.TableDefs!TableName1.RecordCount
    Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName1, xlFileName1, HasFieldNames
    AfterImport = d.TableDefs!TableName1.RecordCount
    
    sSQL = "INSERT INTO tblStatistics (ImportFileName, ImportTableName, ImportDate, BeforeImportRecCount, AfterImportRecCount)"
    sSQL = sSQL & " VALUES ( 'xlFileName1', ' TableName1', #" & Date() & "#, " & BeforeImport & ", " & AfterImport & ");"
    
    d.Excute sSQL, dbfailonerror
    
    
    'second Import
    BeforeImport = 0
    AfterImport = 0
    
    BeforeImport = d.TableDefs!TableName2.RecordCount
    Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName2, xlFileName2, HasFieldNames
    AfterImport = d.TableDefs!TableName2.RecordCount
    
    sSQL = "INSERT INTO tblStatistics (ImportFileName, ImportTableName, ImportDate, BeforeImportRecCount, AfterImportRecCount)"
    sSQL = sSQL & " VALUES ( 'xlFileName2', ' TableName2', #" &  Date() & "#, " & BeforeImport & ", " & AfterImport &  ");"
    
    d.Excute sSQL, dbfailonerror
    
    
    'Third Import
    'as above - and repeat 7 more times
    
    
    'clean up
    set d = Nothing
    Create a query on table tblStatistics. In the query do the subtraction to get the number of records imported.

  5. #5
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    31
    Thank you!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Soccer/Football stats
    By spyfire14 in forum Access
    Replies: 5
    Last Post: 05-23-2018, 11:48 PM
  2. Replies: 3
    Last Post: 01-05-2012, 11:04 AM
  3. Replies: 6
    Last Post: 07-25-2011, 12:54 PM
  4. Replies: 8
    Last Post: 01-19-2011, 03:48 AM
  5. Generating stats - newbie question
    By FavouredEnemy in forum Reports
    Replies: 3
    Last Post: 04-15-2010, 07:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums