Append lookup? Other ideas?

[aranj]

I’m really stuck! I have a table of current and previous owners of certain items.

What I have in the ownership table is lots of rows like this:

Item - Owner - DateTakenOver - Last Owner1 - DateTaken Over1 - LastOwner2 - DateTakenOver2 - etc.. to Last Owner10 and last date10

Eg

Item 1 - Mr A - 1/8/03 - Mr B - 1/5/03 - Mr C - 1/12/02
Item 2 - Mr X - 1/4/03 - Mr Y - 1/1/03 etc.

Some items may only ever have had one owner. Some owners may have owned more than one item.

What I have in a separate table is:

Item 1 - Date - Empty to put the owner on that date into
Item 1 - Date a week later - Empty " " "
Item 2 - Date - Empty " " "
Item 2 - Another date - Empty " " "
Item 2 - Another date - Empty " " "

I need to fill that last field with whoever was the owner on that date into that table with an append query.

I'm not sure I've described this very well ;-)

Can anyone help with any ideas - been annoying me all day!
Thanks.

 

[aranj]

Hi Pat, and thanks.

Yes, it is an inappropriate structure, but I have to work with what I've got; I'm just a junior "tinkerer" !

It has probably been set up this way for simplicity. When a new owner comes along, the user types in his reference and transfer date on a form and the old owners are shifted "down the line" (from memory using .oldvalue).

Is there an easy way of using make table queries to dump this data into the table structure you recommend? I am thinking I could put this in an autoexec macro, thus leaving the original poorly designed table unaltered.

Thanks for the links by the way; I shall certainly have a browse through them.

 

[aranj]

Thanks Pat. You've been really helpful.

I have created 10 append queries and now have a table:

Item - Owner - DateTakenOver
--------------------------------------------
Item1 - Mr A - 31/10/02
Item1 - Mr B - 01/01/03
Item2 - Mr B - 28/02/03
Item3 - Mr C - 01/01/03

Etc.


I now need to populate the last field of a separate table with whoever was the owner on that date. Some items may only ever have had one owner. Not as simple as I thought (it never is!) as many owners may have owned more than one item, but only one item at a time.

Owner - Date - What owned on that date
-----------------------------------------------------------------------------
Mr A - A date - To do
Mr A - Date a week later - To do
Mr B - Date - To do
Mr B - Another date - To do
Mr B - Another date - To do

 

[aranj]

Ah, I found this thread.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=41650

Very, very useful indeed.

Thanks Pat, and thanks Jon K for that thread.