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.