Results 1 to 6 of 6

large table - need to split but still want same report

  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    327

    large table - need to split but still want same report

    I have a large table that i need to move data to another table because it is so big. I can export the data to another table just like it but I want to run the same report and pull data from both tables.

    Details:


    Two tables that have the same data fields but on is for 2012 data and one is for2013 data.
    I want to run a single report that will pull all the data I need from both tables at the same time.

    Please let me know if you need more clarification

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,802
    How is splitting the table into two tables going to be more efficient if you have to bring the data back together anyway for reporting purposes?
    If anything, I think that will be more inefficient.

    Why is it that you think the data needs to be split into multiple tables?

    Also, are all your data tables normalized (if you have unnecessary redundant data because your data is not normalized, that could be causing performance issues)?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    327
    Sorry, not more efficient but Access can only handle 2GB file sizes. unless that has changed.

    I have removed most of the data that was not normalized but the table only has about 12 columns. It has a lot of rows though.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,802
    If you are exceeded the 2 GB limit on data alone, that is a good sign that you might want to use something other than Access to store the data (you can still use Access as the front-end). It will typically run more efficiently if you do so. I find when I have a ton of data stored in Access tables, performance really slows down.

    We currently store most of our data in SQL databases, and use Access as the front-end, but you can also use Oracle, or even free products like MySQL.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    327
    Yes, I am looking at moving to SQL Server Express but until that time i am just wondering if there is a way to have linked tables for years 2011, 2012, 2013 and each table would have the same fields. Then create a report that pulls the data from all the tables at the same time.

    So if I wanted a report that looked for a specific car and I wanted the lease records for it the report would check all the tables for relevant information.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,050
    So you need to split into multiple backend files. Can have a frontend that links to multiple backends and use UNION query to merge the multiple tables into one dataset for report. Performance might be slow.
    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 Update
    By drunkenneo in forum Programming
    Replies: 1
    Last Post: 01-21-2014, 12:37 AM
  2. Split Records From Table/Query in Report
    By Rick5150 in forum Queries
    Replies: 1
    Last Post: 06-02-2013, 09:53 AM
  3. Replies: 7
    Last Post: 10-31-2012, 04:58 PM
  4. Converting large report to PDF
    By jgelpi16 in forum Reports
    Replies: 2
    Last Post: 07-05-2011, 12:09 PM
  5. Replies: 1
    Last Post: 08-22-2010, 01:53 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
  •  
Tech Forums: Microsoft Office Forums