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:
I need to get the orignal and new value (name) on the same line like this:
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?
The history table is structured as follows:
Code:
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/1999I need to get the orignal and new value (name) on the same line like this:
Code:
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/2007Currently, 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?
