Re: [MS_AccessPros] How to query record considering modification date

 

Valentino-

Ah, I assumed the code that writes to the history table simply copies the record BEFORE it gets changed.  It greatly complicates matters that it changes the date before writing to history.  When it does that, you lose track of the date that the value in the history record was originally established.

So what you have is a current record and the date that value became valid, and a history record with the same change date but with the value that was correct BEFORE the date.  You would have to find the previous history record (if any) to figure out when that history value originally was established.  You might be able to reconstruct the history table as it should be like this:

SELECT Key, Information, (SELECT Max(ModDate) FROM tblHistory As H2 

 WHERE H2.Key = tblHistory.Key And H2.ModDate < tblHistory.ModDate) As RealModDate

FROM tblHistory;

That should return each history record with the date from the *previous* record, which should be the date the value in the history record became valid.  If there is no previous record, you will get a Null value.  You could use the NZ function to substitute an artificial "first" date like January 1, 1900.

You could dump that into another table that ha the same structure as tblHistory and then use that in my original solution.  Bottom line is you need to fix the code that writes to the history table.

John Viescas

Sent from my iPad

On Jul 21, 2015, at 10:39, valentino.avvisati@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thank you for the answer John,  

It is unbelievable like you are going beyond  the question and also how fats you are 

I studied your answer and I found interesting the use of union query

 

My issue is that unfortunately, the program that save previous record  that I cannot modify,  insert in the field ModDate"  the modification date before to insert in the record in historical table

this mean that if I have the record

1; 150; 03/03/2015; 21/07/2015

 

 

That was created the 3 of March and changed today 07/21/2015

With unionquery I can retreive 2 records one is coming from Hisorical table tblOpenCreditOLD and the other one is coming from tblOpenCredit

 

1; 2000; 03/03/2015; 07/21/2015

1; 150; 03/03/2015; 07/21/2015

If I do the query you suggested to retrieve data how was before 03/15/2015

 

SELECT Key, Information

FROM qryAllRecs

WHERE Key = 1 And qryAllRecs.ModDate =

(Select Max(ModDate) From qryAllRecs As Q2

 Where Q2.ModDate <= #03/15/2015#)

 

I will not get any record because there are no record with modification date <= to 03/15/2015

In reality the record that I would like to see is the

1; 2000; 03/03/2015; 07/21/2015

That is the record how it was before that change the 21 of march occur.

 

If in place of one modification there were 2 modification

My union query would give me 3 records

 

1; 2000; 03/03/2015; 07/21/2015

1; 150; 03/03/2015; 07/21/2015

1;500; 03/03/2015; 07/10/2015

 

And the record that I would like to see is  the

1;500; 03/03/2015; 07/10/2015

Because this was the record before that the change  of 07/10/2015 (occur)

 

Probably the mistake it is in the program that change the field modDate,  but this is like I have the data

hope is a litle bit  more clear

 

 

 

 

 

 

 

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___