Duplicating data according to Dates

[S Davis]

Well, that's the best description I can think of anyway.

I need help with a solution. I have a set of data (vehicles) that I
have managed to extract a complete history for. This dataset shows the
month and year of every inspection that was completed on the vehicle
according to the location that it was at when the inspection took
place. Here is an example:

Vehicle -- Location -- Year -- Month
Car1 -- Location1 -- 2006 -- 9
Car1 -- Location2 -- 2006 -- 12
Car2 -- Location1 -- 2007 -- 2
Car2 -- Location2 -- 2006 -- 11
Car2 -- Location3 -- 2007 -- 5
Car3 -- Location1 -- 2006 -- 9

My goal is to populate a historical and future outlook based off this
list of locations that these vehicles have been in the past, and then
based on the most recent info to project into the future. For Car1, it
was at Location1 in 2006 month 9 and moved to Location2 in 2006 month
12. What I would like to assemble would be the following for Car1:

Vehicle -- Location -- Year -- Month
Car1 -- Location1 -- 2006 -- 9
Car1 -- Location1 -- 2006 -- 10
Car1 -- Location1 -- 2006 -- 11
Car1 -- Location2 -- 2006 -- 12
Car1 -- Location2 -- 2007 -- 1
Car1 -- Location2 -- 2007 -- 2
Car1 -- Location2 -- 2007 -- 3
Car1 -- Location2 -- 2007 -- 4
Car1 -- Location2 -- 2007 -- 5
Car1 -- Location2 -- 2007 -- 6
Car1 -- Location2 -- 2007 -- 7
Car1 -- Location2 -- 2007 -- 8
Car1 -- Location2 -- 2007 -- 9
Car1 -- Location2 -- 2007 -- 10
Car1 -- Location2 -- 2007 -- 11
Car1 -- Location2 -- 2007 -- 12

Take away all of the 'Relativity' I've thrown into this description,
and I essentially need a method of Access adding month/years to my
list of history in order to populate any gaps, by referencing it
against a simple table of months and years. Something along the lines
of (logicwise anyway):

Add +1 Month to Record until Next Record is found with same Vehicle,
then Add +1 Month to that Record, etc...

If you can think of any creative angles, I will take that. I am posing
this to the community not for a full solution, but possible ideas
while I continue to work on this. Many thanks!

 

[KARL DEWEY]

I do not unserstand why you are doing this but how can you assume that Car1
will remain in Location2 for the rest of the time?

 

[S Davis]

I do not unserstand why you are doing this but how can you assume that Car1
will remain in Location2 for the rest of the time?
--
KARL DEWEY
Build a little - Test a little












- Show quoted text -

This is part of a multipart process. Essentially, the car is assumed
to be at whatever location that it is recorded as last having been at.
Once I build this list out for the rest of the year I have a seperate
query that will update the Loction field based on a distribution-by-
location table