Results 1 to 7 of 7
  1. #1
    AlGreko80 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    3

    Splitting large table into smaller tables

    Hi




    I was after some help from an utter Access beginner.

    I have a large table of data in Access, it is GPS data for a fleet of vehicles, called "Vehicle_GPS". I would like to split this large table into several smaller tables, one for each vehicle. Each vehicle is identified by its registration, in a field called "Reg".

    There are a lot of vehicles so it would be good to automate the process rather than manually selecting and copying into new tables.

    I want to split the large table into smaller tables so each of the smaller tables contains the data only for one vehicle (Reg). So if there are 40 vehicles, I would like to create 40 separate tables each containing the data for that specific vehicle, possible naming the table by the registration as well if possible.

    Any help would be gratefully received!
    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why do this? Really doesn't make sense to split. At some point you will need to have all vehicles in one dataset and will have to use UNION.

    My advice is to NOT do this.

    Are there dependent tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    AlGreko80 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    3
    Hi

    Thanks for the reply. I really need to split these into separate tables. It is just one single large table, no dependencies or links. And I won't need to rejoin them in the future.

    Cheers

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I still question that you "need" to do this. However, can certainly be accomplished with VBA procedure. Something like:
    Code:
    Sub SplitTable()
    Dim db As DAO.Database, rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Reg FROM Vehicle_GPS")
    Do While Not rs.EOF
        db.Execute ("SELECT * INTO [" & rs!Reg & "] FROM Vehicle_GPS WHERE Reg='" & rs!Reg & "'")
        rs.MoveNext
    Loop
    End Sub
    

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    AlGreko80 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    3
    Thanks, I'll give it a try. The table is huge and will not open in Excel, and I need to break it down into manageable tables to send to others to use in Excel. Is there another way you would do this?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How many fields are in the Access table?
    Have you tried normalizing the table?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Export filtered queries instead of creating a bunch of tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Large table into smaller
    By Thompyt in forum Programming
    Replies: 15
    Last Post: 05-04-2019, 12:55 PM
  2. Replies: 4
    Last Post: 03-08-2018, 03:27 PM
  3. Replies: 3
    Last Post: 02-23-2018, 05:52 PM
  4. Replies: 5
    Last Post: 11-07-2015, 03:53 PM
  5. Splitting tables
    By MTSPEER in forum Access
    Replies: 7
    Last Post: 06-04-2013, 06:47 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
  •  
Other Forums: Microsoft Office Forums