Results 1 to 14 of 14
  1. #1
    dseg42 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6

    Need help with reducing time on current process

    I need some help...



    So I have one big table that I import from Excel. This table has about 10 columns but in one column I have "Customer IDs" (about 60 different ones).
    From this big table, I need to make 60 separate smaller tables. My current process is; Create > Query Design > Make Table > Customer ID criteria "1" > Blah > Blah
    Then it makes one table with my first Customer ID. I do this 60 times...

    Them make a macro to run all these 60 Make Table queries. Is there an easier way to make these 60 tables without making 60 queries?

    I need to do it the other way too. I have 60 separate tables and I need to Append them but I have 60 Append queries to make one big table.
    There has to be an easier way... Please let me out.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    For the big table, when extracting CustomerID, how do you use the data? Can't you just use the query as a recordsource for a form, report or whatever? Why make a new table at all?

  3. #3
    dseg42 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6
    Quote Originally Posted by davegri View Post
    For the big table, when extracting CustomerID, how do you use the data? Can't you just use the query as a recordsource for a form, report or whatever? Why make a new table at all?
    I am extracting CustomerID into separate ID tables because I have Excel files link to the database, but only to a specific table.
    So I will have Excel files link only to CustomerID "500" - instead of the massive table with every ID.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I know you can't update a linked table, but you could copy the linked table to a local table and work with that.
    Assuming you do that:
    I understand that you want to separate out records by CustomerID. With your local table shown in a form you could filter by CustomerID, or create a query with criteria for a given CustomerID to use as needed.
    A single querydef could be modified in code to extract each CustomerID as needed, and you would actually need only ONE basic query for a recordsource, with the specifics entered via the form.

  5. #5
    dseg42 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6
    Quote Originally Posted by davegri View Post
    I know you can't update a linked table, but you could copy the linked table to a local table and work with that.
    Assuming you do that:
    I understand that you want to separate out records by CustomerID. With your local table shown in a form you could filter by CustomerID, or create a query with criteria for a given CustomerID to use as needed.
    A single querydef could be modified in code to extract each CustomerID as needed, and you would actually need only ONE basic query for a recordsource, with the specifics entered via the form.
    Are you able to provide any samples on how to do this?

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    We're trying here to avoid taking the big table apart, updating 60 separate tables and putting the 60 tables back together into a big table? I don't blame you for wanting a better way.
    But first, tell us more of what you want your database to do. What gets updated - when and how? What forms do you have? Is the big table the only table you start with?
    We need to know a bit about the process to make sure we start on the right track.

  7. #7
    redpenner is offline Novice
    Windows 7 32bit Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    23
    Note this question is also posted on UtterAccess currently. http://www.utteraccess.com/forum/ind...wtopic=2038141
    Last edited by redpenner; 07-21-2016 at 04:33 PM. Reason: Add link

  8. #8
    dseg42 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6
    Quote Originally Posted by davegri View Post
    We're trying here to avoid taking the big table apart, updating 60 separate tables and putting the 60 tables back together into a big table? I don't blame you for wanting a better way.
    But first, tell us more of what you want your database to do. What gets updated - when and how? What forms do you have? Is the big table the only table you start with?
    We need to know a bit about the process to make sure we start on the right track.
    Alright - I have a massive table in Access already. It has 60 CustomerIDs, I need to export the big table into these 60 CustomerIDs, one table for each. Then there will be Excel files that link to the database, but only to a specific CustomerID.

    I also know the CustomerIDs that need to be separated, it goes from 1-60.

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You keep repeating the same request. We understand that you want to separate the file by CustomerID. You asked for a better solution, and we are unable to make an informed suggestion without being informed.
    You've never explained WHY you import an Excel spreadsheet into Access, separate it into 60 tables, then combine the 60 tables again to export to Excel. On the face of it, this makes no sense. WHAT BUSINESS PROBLEM IS BEING SOLVED? Who are your users? What are their needs? If we understand this, we may be able to offer a more suitable alternate solution.

  10. #10
    dseg42 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6
    Quote Originally Posted by davegri View Post
    You keep repeating the same request. We understand that you want to separate the file by CustomerID. You asked for a better solution, and we are unable to make an informed suggestion without being informed.
    You've never explained WHY you import an Excel spreadsheet into Access, separate it into 60 tables, then combine the 60 tables again to export to Excel. On the face of it, this makes no sense. WHAT BUSINESS PROBLEM IS BEING SOLVED? Who are your users? What are their needs? If we understand this, we may be able to offer a more suitable alternate solution.
    Each Customer has access to an Excel file that can only contain their specific data.
    Access to other CustomerIDs is confidential.
    So the Excel files are linking to this database but it would only link to their specific table of data with their ID.

    The master table (with all 60) is generated by our IT department, so that is just loaded to Access in a big table.
    The users are the people using 60 separate Excel files.
    Their need is to link to a Access database to pull only their data in an efficient way because their Excel files are big.

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Why can't a customer link to ANY of the confidential 60 tables he chooses?

  12. #12
    dseg42 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6
    Quote Originally Posted by davegri View Post
    Why can't a customer link to ANY of the confidential 60 tables he chooses?
    I'm trying to use this code but I get "Compile Error: Invalid outside Procedure"
    I simply have two files in the folder location, simple test data on 'Sheet1'






    Option Compare Database


    Dim strPathFile As String, strFile As String, strPath As String
    Dim blnHasFieldNames As Boolean
    Dim intWorksheets As Integer


    ' Replace 3 with the number of worksheets to be imported
    ' from each EXCEL file
    Dim strWorksheets(1 To 2) As String


    ' Replace 3 with the number of worksheets to be imported
    ' from each EXCEL file (this code assumes that each worksheet
    ' with the same name is being imported into a separate table
    ' for that specific worksheet name)
    Dim strTables(1 To 2) As String


    ' Replace generic worksheet names with the real worksheet names;
    ' add / delete code lines so that there is one code line for
    ' each worksheet that is to be imported from each workbook file
    strWorksheets(1) = "Sheet1"
    strWorksheets(2) = "Sheet1"




    ' Replace generic table names with the real table names;
    ' add / delete code lines so that there is one code line for
    ' each worksheet that is to be imported from each workbook file
    strTables(1) = "Test.xls"
    strTables(2) = "Test1.xls"




    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = True


    ' Replace C:\Documents\ with the real path to the folder that
    ' contains the EXCEL files
    strPath = "C:\Users\dsegedin\Desktop\Test\Folder"


    ' Replace 3 with the number of worksheets to be imported
    ' from each EXCEL file
    For intWorksheets = 1 To 2


    strFile = Dir(strPath & "*.xls")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferSpreadsheet acImport, _
    acSpreadsheetTypeExcel9, strTables(intWorksheets), _
    strPathFile, blnHasFieldNames, _
    strWorksheets(intWorksheets) & "$"
    strFile = Dir()
    Loop


    Next intWorksheets


    Sub Tests()


    End Sub

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So the overall goal is to import all data from 60 excel files into Access(in separate records or tables) and customers can only see their data based on CustomerID? You said they will need to link back again to these excel files or you will then combine all the data for the 60 customers back into 1 table? Do the Excel files have the CustomerID in them as well that match the file you get from IT?

    Still not clear on why you need the 60 tables. Can you have just 1 main table, one record per Customer and have these customers login to your database? Then based on their ID, only show the 1 record out of the 60 based on the CustomerID.

    Not sure on your error, maybe remove this part and see if it compiles to see if it is this section causing the issue:
    DoCmd.TransferSpreadsheet acImport, _
    acSpreadsheetTypeExcel9, strTables(intWorksheets), _
    strPathFile, blnHasFieldNames, _
    strWorksheets(intWorksheets) & "$"


  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My thoughts:

    Please review this link on why crossposting is frowned upon.
    http://www.excelguru.ca/content.php?184


    Despite several requests for clarification of your problem, I am still confused. Yes, I know there is a "big" Excel SS that you import and you want to break it into 60 smaller tables. But breaking the big table into 60 smaller tables does nothing for security. I can look at 60 small tables just as easily as looking at one big table. You need to limit records available to be viewed by good programing.


    As far as the code, I saw several problems:
    The top two lines in EVERY module (form, report, standard) should be
    Code:
    Option Compare Database
    Option Explicit
    There are two major errors in this code snippet:
    Code:
        ' Replace C:\Documents\ with the real path to the folder that
        ' contains the EXCEL files
        strPath = "C:\Users\dsegedin\Desktop\Test\Folder"
    
        ' Replace 3 with the number of worksheets to be imported
        ' from each EXCEL file
        For intWorksheets = 1 To 2
    
            strFile = Dir(strPath & "*.xls")  'this will always return ""  . see next line
            Debug.Print strPath & "*.xls"  'I added this to see what strFile should be
            ' this is what is returned  -> C:\Users\dsegedin\Desktop\Test\Folder*.xls
            ' back slash is missing between the last folder name and the file name.
    
            strFile = Dir(strPath & "\*.xls")  "<<== this is what the line should be
    
            '        Debug.Print strWorksheets(intWorksheets) & "$"
    
            Do While Len(strFile) > 0
                strPathFile = strPath & strFile "<<== this line is also missing the backslash
                DoCmd.TransferSpreadsheet acImport, _
                                          acSpreadsheetTypeExcel9, strTables(intWorksheets), _
                                          strPathFile, blnHasFieldNames, _
                                          strWorksheets(intWorksheets) & "$"   '<-- the range is Sheet1$ ?????
                strFile = Dir()
            Loop
    
        Next intWorksheets
    The other thing I don't like is the logic of having the "DO...While" inside the "For... Next"
    I would have it the other way

    Code:
    Do While Len(strFile) > 0
         For intWorksheets = 1 To 2
    
        Next
    Loop
    You've found a valid file, now load all of the sheets.

    BTW, from the code, it looks like if there were 5 spreadsheets, all of the data from "Sheet1" in all if the spreadsheets are loaded into the same table and all of the data from the Sheet2 sheets are loaded into the another table??

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

Similar Threads

  1. Replies: 1
    Last Post: 08-20-2013, 11:39 PM
  2. Keep Time Current
    By dargo72 in forum Forms
    Replies: 4
    Last Post: 11-26-2012, 07:30 AM
  3. Replies: 1
    Last Post: 08-14-2012, 03:22 AM
  4. Replies: 4
    Last Post: 04-05-2011, 06:12 PM
  5. Reducing code
    By Mclaren in forum Programming
    Replies: 7
    Last Post: 03-16-2011, 02:45 PM

Posting Permissions

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