Results 1 to 7 of 7
  1. #1
    msumrall is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    2

    ***HELP***Working across 8 Databases


    I am stuck. I have 8 databases of data - one db of employee data and 7 db's of payroll data spanning 3 1/2 years. Due to the size of the data sets I had to split the data into 6 month batches to stay within the 2GB limit of Access. The seven payroll databases have exactly the same structure of 5 tables - Deductions, Earnings, Special Earnings, Other Earnings, and Taxes. I need to be able to query this collection to summarize payroll for an individual employee upon demand for legal research. Ex - I will get a call saying they need the historical payroll register for employee #123456 (or a group of employee numbers) and I need to be able to extract it. I have linked all of the 35 tables of payroll data to the Employee table using the Employee ID as the common thread. I think I need to "join" the tables together so I can get the earnings from 7 Joined tables, Deductions from 7 joined tables, and the same for the remaining 3 table types. Has anyone tackled a problem like this? I am thinking it might be easier to dump it all into sql but i don't have a tool for that so if that is the answer a recommendation would be appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Are you compacting the databases? I was able to store 2 million records and still be under the 2 gig limit.

    you may try attaching the external tables into 1 db, then build a union query as the base data to run your queries.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You may want to think about an SQL server.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So do you need to sum up say Earnings values, tax values, etc. from the 7 database for a single employee or is there a date range involved? So you want the output to be EmpNbr, Total_Earnings, Total_Taxes, etc.?

  5. #5
    msumrall is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    2
    Quote Originally Posted by Bulzie View Post
    So do you need to sum up say Earnings values, tax values, etc. from the 7 database for a single employee or is there a date range involved? So you want the output to be EmpNbr, Total_Earnings, Total_Taxes, etc.?
    yes for a single employee and there could be a date range involved. The output would be the earnings, taxes, etc, per pay period.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have 7 BEs over about 3.5 years? That is a lot of data! I was going to suggest SQL Server Express (free), but the limit is 10 GB. You might have to go to SQL Server Standard ($$$).

    Moving to SQL Server is not hard. Install SQL Server Standard and the Management Studio on the server. Might want to install the SQL Management Studio on the local computer also so you can make changes to the database from your desk. (I did)
    - On the local computer, create an ODBC connection to SQL Server.
    - Access has a wizard to help move to SQL Server. Using the wizard helps the the field types correct because there are a few differences in field types.
    - Once you have the SQL dB created, you link the SQL tables to Access. The links will have a "dbo_" prefix on each table, but the tables can be renamed to your current names so there won't have to be any code/query/form/report changes.
    - Then you can use update queries to add the data from the other 6 databases to SQL Server.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I agree with Steve, this really needs to be a SQL back-end for that much data. If you need to keep it Access, maybe do a Union query for each section, then link that to the Employee table to get your data. On union queries, the fields for each table must match, you can't include more fields on one versus the other.
    qryDeductionsAll (in sql Code window try something like this)

    Select * from tblDeducations1
    UNION ALL Select * from tblDeducations2
    UNION ALL Select * from tblDeducations3
    UNION ALL Select * from tblDeducations4
    UNION ALL Select * from tblDeducations5
    UNION ALL Select * from tblDeducations6
    UNION ALL Select * from tblDeducations7

    Create qryEmployeeData, link the Employee table and this query by EmployeeID to get all your records in one recordset. Do this for all the other sections.

    Another Idea would be to do the data mining in code. With the EmployeeID, loop though each table to pull out that Employee's record/data and write to a temp table. Then base your queries on that temp table. Before you run it each time, just wipe out the previous data.

    I'm not sure what will work with the data and linkings involved but you will just have to try some things.

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

Similar Threads

  1. Working query stops working after importing
    By Abacus1234 in forum Import/Export Data
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  2. What is the best way to protect databases
    By AzizSader in forum Forms
    Replies: 2
    Last Post: 11-27-2013, 11:55 PM
  3. Replies: 3
    Last Post: 01-29-2013, 04:34 AM
  4. databases
    By graciemora in forum Access
    Replies: 1
    Last Post: 10-25-2010, 07:34 PM
  5. Working With Huge Databases?
    By soma in forum Access
    Replies: 2
    Last Post: 02-16-2010, 06:27 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