I am trying to streamline the production of a document that has to be created on a regular basis. There are tasks associated with detailed instructions on how to complete them, and each of these has a specific ID number that is reused across various projects. I sucked up as many of these task IDs as I could into an access database.
For each project, somebody else pulls a list of all of the required tasks from another database that I cannot access and puts them into an Excel workbook with a standard format. For each project, I need to:

Pull in all relevant tasks by ID number

Compare them to the standard list in my database which has the steps to complete the task and some other info

Generate a document with a specific format using the specific information about the tasks

Writing a macro in excel seemed like a lot of work, but the best bet, but I can't be sure that the access database will always stay in the same place that it belongs. Since the database is the consistent item across all projects, I thought about writing a macro in Access and having a dialog asking where the spreadsheet is would work, but using a table to compare the two lists seemed more complex than simply having a comparison sheet generated within excel. I didn't really consider writing a macro within Word, though that is a possibility too.

I am trying to find the most logical way to create this setup. Using 3 different office programs seems fairly convoluted, but I am limited in the software that I have available to accomplish this:

3 Answers
3

If you had Crystal Reports, you could join the Excel table to the Access tables; then print 2 reports, one with data match on ID, and the other report to list records without ID matches. Formatting is awsome. I write reports where I join Excel files to an Oracle database for reporting. You should be able to join the Excel file to ACCESS and create the same type of reports assuming both have the ID fields to match on.

If your native work environment is the database, and you can work on automating the input of those new fields of data, you can use simple queries to select the unmatched (new) data and work from there. It's a shame that you can't simply punch out of the first database to your own, or have the first set of data exports simply export a CSV of new or changed data.

On either side, you can append the new data, and compare it to the existing (with queries in Access, or a Pivot Table in Excel). If you create a new column/field to act as a simple boolean flag, you can differentiate between datasets and merge them into the same table/document. Alternatively apply a code for weekly (or whatever period) imports so you can use this "flag" field for future queries.

The pivot table would be my first port of call, as you can work faster on the scoping stage than with database queries, and you can view your reporting/comparison requirements in the one view.

If you can "wash" your data to anonymise it, please post some examples for more specific solutions.