Database design assistance needed

[ddskorupski]

Hello Everyone! I am in desperate need of assistance. I am trying to create a database to track date changes to 8 fields. I think I am making this harder than it needs to be.

I am attaching a spreadsheet with the fields that I need to track. BAsically, I will receive a spreadsheet that lists the applications with baseline and proposed dates. Once the proposed dates are approved, they will become my new baseline dates.

I need to keep a table that lists all apps with the most current baseline dates; and a table that lists everytime a date change has happened.

Can you help?

 

[MSAccessRookie]

Hello Everyone! I am in desperate need of assistance. I am trying to create a database to track date changes to 8 fields. I think I am making this harder than it needs to be.

I am attaching a spreadsheet with the fields that I need to track. BAsically, I will receive a spreadsheet that lists the applications with baseline and proposed dates. Once the proposed dates are approved, they will become my new baseline dates.

I need to keep a table that lists all apps with the most current baseline dates; and a table that lists everytime a date change has happened.

Can you help?

I think the Post-It Note containing the attachment fell off?

 

[ddskorupski]

oh, sorry. try this one...

 

[ddskorupski]

Got it. file size was to big. Had to compress.

 

[Olav]

Hi ddskorupski,

I would create a Update query that tests if the date is approved, either by checking the approval signature, or better, adding a Yes/no field.

See the SQL code below:
UPDATE table SET table.date1 = table.date2, table.date3 = table.date4
WHERE ((table.Approved) Is Not Null);

(Replace table with the real name of the table, all date fields by the real date field names, and Approved by the name of the signature field.)

If the changes are constant, I would add a yes/no field called approved, and reset it with the same query:

UPDATE table SET table.date1 = table.date2, table.date3 = table.date4, table.UpdateRecord = 0
WHERE ((table.UpdateRecord) = -1);

The last query will only update the newly approved records, but the first will update all approved records. This may take time if the database is big.

If you do not need to know the proposed date, you may combine the proposed, and approved date fields to one single date field, and add a Approved Yes/No field to save a few bytes. -If so, you do not need to run the query.