vb script needed to do multiple tasks

[ddskorupski]

Hello. I have three tables: tblMaster; tblTransformation and tblhistory.

The tblMaster table has all of the master records that we report off of. The date fields in the master table contain our baseline dates.

The tblTransformation table has the same fields and records but the date fields will have different dates because they dates have changed. This table is used to make changes to the dates when the original dates cannot be met.

The tblhistory table compares the tblMaster and tblTransformation records and will record the history of the date change. So it will contain the date fields from tblmaster that have our original baseline dates and the date fields from tbltransformation that record the new dates so we can trend the changes.

I am using an append query with some rather eloborate expressions that will compare the two tables and append to the tblhistory. However, it is not doing everything I want and it isn't efficient.

Here is what I am trying to achieve through vba.

1. I want a script to compare the tblmaster date fields to the tbltransformation date fields and then append them to the tblhistory

2. I want a script to then make a backup copy of the tblmaster table

3. then I want a script that will update the tblmaster date fields with the new dates from the tbltransformation

Can anyone help me figure out what this code should look like?

 

[HiTechCoach]

It is probably possible to do what you want with your current table structures with enough VBA coding.

IMHO, the reason you are having to copy the same data around is that you have a design issue. If properly normalized, you should not need to duplicate the data between tables.

If it we me, I would fix the table design and most of your issue will go away. I would think it would be very difficult to keep any integrity with the data if you are duplicating data between tables.

If you must keep the table structure you have, then it would probably help to post the table design. This will hep us when giving you help to do what you want.

 

[ddskorupski]

Thanks HITechCoach. However, I am not duplicting any data. The data in the transformation table has the same application name and id but different dates. That is why I need to compare the two tables and record history change.