Complicated Sub-query

[oshlonger]

I'm querying a history table using Access 2000, and I'm having trouble getting the results in the format that I need. I can't seem to figure out how to build the query. Here's the issue:

The history table is structured as follows:

cust_num    cust_name    name    start        end
2184087        tbd        jim    1/1/2005    5/29/2007
2184087        tbd        matt    5/30/2007    6/6/2007
2184087        tbd        bob    6/7/2007    6/17/2007
2184087        tbd        steve    6/18/2007    12/31/1999

I need to get the orignal and new value (name) on the same line like this:

cust_num    cust_name    name_old    name_new    chng_date
2184087        tbd        jim        matt        5/29/2007
2184087        tbd        matt        bob        6/6/2007
2184087        tbd        bob        steve        6/17/2007

Currently, I'm joining the table to itself and selecting name from the second instance of the table if the start date is greater than the start date of the first instance. This only works if there were 2 changes. The results are incorrect if there are more than 2 changes.

Any ideas?

 

[lameid]

Select h1.cust_num, h1.cust_name, h1.name as name_old, h2.name as name_new, h1.end As ChngDate
From History as H1 Inner JOin History H2 ON h1.cust_num = h2.cust_num
Where h1.end = Dateadd("d",1,h2.start) Or 
   h1.end Not In (Select Dateadd("d",1,h3.startFrom History As H3 
   where h3.cust_num = h1.cust_num)

Note that the Not in is what gives you a result even if there is only one record or no changes. You can remove it if it is unnecessary.

 

[oshlonger]

Yes, I believe this will work for most instances and for my example, but not every record change falls on the day after the previous value ended.

For example, the name on a record could end and the record could be nameless for a period of time before a new name was appended to the record.

How can I handle this?

 

[lameid]

If your data is not consistent then I would suggest when you add a record it write what the previous record is... You would end up adding a unique key (autonumber?) and adding it as a FK for a relationship to itself.

Beyond that you might populate the same with VBA using recordsets... I have to run for now... Postback if you need more ideas/help.