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

 

Valentino-

Your data example does not match what you described.  I would expect to find the record with the latest "changed" date in the current table - tblOpenCredit.  Instead, you show records with later dates in tblOpenCreditSTO.  So, I would expect to find in the main table a record like:

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

I would also expect to find a record in the history table like this:

1; 2000; 03/03/2015; 03/03/2015

That would be the record showing the initial date for this account along with the first value set.

If my example is correct, then you can indeed find the value for any date by searching on the UNION ALL of both tables.  Let's say your Union query is named qryAllRecs.  Find the value on March 15, 2015 for Key 1 like this:

SELECT Key, Information

FROM qryAllRecs

WHERE Key = 1 And qryAllRecs.ModDate =

(Select Max(ModDate) From qryAllRecs As Q2

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

Keep in mind that date liberals in SQL in Access must be in mm/dd/yyyy format.

John Viescas 

On Jul 20, 2015, at 23:52, Valentino Avvisati valentino.avvisati@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Good evening to all, 

I have a question, I try to explain hoping that  somebody can give me some solution as happen in the past.

 

I have a  2 tables, one  main table  that I call  Tblopencredit and another one called TblopencreditOLD that stock  previous version of the record and it  is fulfilled each time the records  in main table, change.

 

The table  has the following fields:

1. key

2. information field

3. creation date

4. modification date

each time the record  in table Tblopencredit is changed, the previous record is copied and saved in table  tblopencreditSTO and the date when change occur  is reported in "modification date" field  of tblopencreditSTO.

 

 

below one example of record in  tblopencredit

(the format of the date is dd/mm/yyyy)

 

                             

1; 900; 03/03/2015;10/07/2015;

2;1000; 1000; 11/02/2015; 11/02/2015

3;1500; 06/07/2015; 11/02/2015

 

In reality in my database the date  is reported also with seconds that I deleted to simplify

 

 

Below an example of tblopencreditSTO where the record with key 1 has been changed 3 times and the other record did not have any change and consequently there are no record in tblopencreditSTO

 

 

1;1000; 03/03/2015;10/07/2015

1;800; 03/03/2015; 15/07/2015

1;200; 03/03/2015; 18/07/2015

 

 

My goal is   retrieve the data how was in a precise given date

 

For example if I want to know how was data the 17/07/2015 (July 17)

 

 

This mean that I need to take the record 1 in tblopencreditSTO (second position with changing date:  15/07/2015)

And for record 2 and 3 I need to take the data in tblopencredit because the record were created before the query date and did not occur   any modification.

 

I can also merge the two tables in only one table but I need a way to extract only one record for each key that fit my question "How was the data in a certain date)

 

 

Any Idea?

 

Thank you

 

 Valentino

 

__._,_.___

---

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 (2)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___