Results 1 to 2 of 2
  1. #1
    shanea.kr is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2012
    Posts
    3

    Need help to simplify this process

    I am trying to simplify this process. Every month, we have to make copies of the old file and change all of the data, the links from the tables to the new month and it takes too long. Is there another faster way? below are the steps that we take but I can not upload the database file.

    1. Prior to importing new data into Access, make a copy of the previous month’s database file. Paste it into the new month’s folder and rename it with the new month.


    2. Open your newly copied file in Access.
    3. In Access Table menu, make 3 copies of the Template file and name them resp.: Applications, Renewals, and Renew withDocuments. Select Structure ONLY. (Right Click>Copy>Paste to make copies of new files)
    4. Click External Data Tab > Import Section > Text file > [Location of the TPA text files](That we receive from our IT department)
    5. Navigate to Where the text files are saved. Select Applications
    6. A dialog box is displayed:
    a. Select Append option and in the drop down select the current month’s file.
    b. Select Delimited – click Next to go to the next tab
    c. Select Tab – click Next to go to the next tab
    d. Navigate through the wizard until it asks you to import to your NewMonth’s template. Verify that it is the correct file and hit finish.
    7. Repeat steps 4 through 6 to import the remaining two text files: Renew and RenewDocs.
    8. Do not delete existing tables from previous reporting month for now
    9. Go to Queries View
    10. Open First Query in Design View
    11. Click Design Table>Show Table button
    12. Unlink the previous months table and select the new months table .
    13. In the bottom half, in the table field, change all of the drop downs to the new month’s table
    14. Change any other “Fields” drop down if needed
    15. Right click on Previous Month’s table box and hit remove
    16. Once selected save your changes, close the individual query and rename the query to the new month.
    17. Repeat steps 2-8 for the remaining 5 queries. (so many steps)
    18. Go to Reports view.
    19. Open MonthYr/AppbyCounty report under Design view
    20. In the Tools section of the Ribbon, click on Property sheet and change Record Source to the current Query.On the format tab, change the caption to the new month’s title.
    21. Close and save changes. Then Rename the entire file to the new Monthyear Applications_Report
    22. Repeat steps for RenewalsbyCounty_Report
    23. When all queries and reports have been renamed and updated with the new reporting month’s data, compact and repair the database. This will be the source database for that month. Click the Microsoft Office Button, point to Manage, and then, under Manage this database, click Compact and Repair Database.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From what you wrote, it sounds like the mdb is in serious need of normalization; having tables with month names is an indication of a non-normalized database.
    Without being able to see your tables/ structure, you should/could add an additional field to store the date. Then you could select a month/year from a combo box and have the queries use that date as a parameter to limit the records for forms/reports. No more editing forms/queries/reports.

    What do you do if you need to look at data 2 months in the past??

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

Similar Threads

  1. Form/Report process
    By pg13Reader in forum Forms
    Replies: 6
    Last Post: 12-19-2011, 06:40 PM
  2. Efficient Process?
    By compooper in forum Database Design
    Replies: 1
    Last Post: 06-14-2011, 03:01 PM
  3. Replies: 3
    Last Post: 03-28-2011, 11:49 AM
  4. Automate Compact and Repair process
    By tracerbullet in forum Security
    Replies: 2
    Last Post: 01-14-2010, 05:12 PM
  5. Process that requires 2 users on 2 machines
    By Rstarr1 in forum Programming
    Replies: 0
    Last Post: 12-17-2008, 03:14 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