Record Tracking

[jlgray0127]

I have three tables that have identical structure. Lets say tbl 1 and tbl 2 and tbl 3
The data is exactly the same, as well, with the exception the user may have made changes to some of the fields in Tbl 1. What I need to do, is look at tbl 1, and find out what is different than tbl 2, and append this to table 3.

I have 3 tables with identical structure. Schedule, Temp Schedule, and Schedule Changes.
So... when the user opens the database, it copies the data in Schedule to Temp Schedule. The user will go in and make changes to some of the records in Schedule.
Now, what I am doing here, I want them to click a button, (called update) and for it to compare records in Schedule and Temp Schedule, only pulling out the records where the data does not match. This is just to identify the records where changes were made. I want these to then be appended to Schedule Changes.

I know this is fairly simple, but for some reason, I jus can not get it to click in my head, how to do this simply.



PLEASE HELP!!! :P
Thanks!

[June7]

Why this convoluted process for data entry/edit?

Start with a Find Unmatched query and use that as the source for an INSERT SELECT action.

http://www.blueclaw-db.com/accessque...nsert_into.htm

[jlgray0127]

Thanks, June7!
So... first, when I went back and read what I asked, the first paragraph should have been deleted before I sent it! I was trying to explain what I was doing, then reworded and forgot to delete the first paragraph, as I had to run to a meeting!

Are you familiar with how scheduling production orders works in manufacturing? This is what I'm doing. So, let me give a better background.
I have a schedule on a mainframe. This schedule gets updated daily, because we have to change our plan when we have issues with staffing, material availability, or hot orders, etc... We can not change the schedule on the main frame, as it is set up to be firm, so we work around that issue today, with a spreadsheet schedule. This is all manual and requires us to go search multiple screens to verify we can change a single order.

This is an attempt to help make it a bit more systematic by tying the main frame to the access database rather then trying to schedule in a spreadsheet.

The way this is set up, I established the starting point of this tool by taking a snap shot of the current schedule and creating the table we are going to be scheduling in, (aka table 1, Schedule) and then created two indentical tables (with no records) and named them Temp Schedule and Schedule Changes.

Now, whenever the database is opened, I run three queries. The first one clears the records on the table Temp Schedule (if any) and another query appends the current records on the table Schedule and appends those records to the blank table, Temp Schedule.

The third query that runs, looks at the mainframe for new orders and appends them to the table Schedule.

What this has all done, is given me a copy of the schedule before I made any changes to it, either by the user, or by adding any new orders. It has also looked at the main frame and appended and any new orders to the table Schedule.

Now, there are several things that can happen. The person who manages the schedule for our business, can, through several different forms, procedures, modules, etc, change the orders around on the schedule table. This all works fine. At anytime, they can click a button on any form they are in, and update the schedule.

This refreshes any forms that are open, and activates other controls on the forms, which change calculations that are on the forms, highlights orders in some cases if they decide to make them hot orders, etc.

This is where my question came in, and the suggestion for the unmatched query and Insert Into.

What else I would like to happen here, is for a query to run that looks at the table Schedule, AFTER CHANGES, and the Table Temp Schedule, BEFORE CHANGES, and pull out the records that are different to append to Schedule Changes.

The problem I am having with the unmatched query, is we are talking about changes that could happen in 1 - 18 fields.

So, here is where I am unsure how to find the one change made in one of these fields and append it to a table.

Hopefully, I am not too confusing in my description. Thank you so much in advance for your help!!!

[June7]

The Find Unmatched query can consider multiple fields in the matching. 18 fields is a lot but I guess that can be done. Try it and let me know what happens. http://office.microsoft.com/en-us/ac...010205132.aspx