It was suggested that I repost this questions as I didn't do a very good job discribing my issue the first time. (http://stackoverflow.com/questions/2921286/access-question)
THE SITUATION: I have inspections from many months of many years. Sometimes there is more than one inspection in a month, sometimes there is no inspection. However, the report that is desired by the clients requires that I have EXACTLY ONE record per month for the time frame they request the report. They understand the data issues and have stated that if there is more than one inspection in a month to take the latest one. If the is not an inspection for that month, go back in time untill you find one and use that one. So a sample of the data is as follows:
(I am including many records because I was told I did not include enough data on my last try)
equip_id month year runtime date
1 5 2008 400 5/10/2008 12:34 PM
1 7 2008 500 7/12/2008 1:45 PM
1 8 2008 600 8/20/2008 1:12 PM
1 8 2008 605 8/30/2008 8:00 AM
1 1 2010 2000 1/12/2010 2:00 PM
1 3 2010 2200 3/24/2010 10:00 AM
2 7 2009 1000 7/20/2009 8:00 AM
2 10 2009 1400 10/14/2009 9:00 AM
2 1 2010 1600 1/15/2010 1:00 PM
2 1 2010 1610 1/30/2010 4:00 PM
2 3 2010 1800 3/15/2010 1:00PM
After all the transformations to the data are done, it should look like this:
equip_id month year runtime date
1 5 2008 400 5/10/2008 12:34 PM
1 6 2008 400 5/10/2008 12:34 PM
1 7 2008 500 7/12/2008 1:45 PM
1 8 2008 605 8/30/2008 8:00 AM
1 9 2008 605 8/30/2008 8:00 AM
1 10 2008 605 8/30/2008 8:00 AM
1 11 2008 605 8/30/2008 8:00 AM
1 12 2008 605 8/30/2008 8:00 AM
1 1 2009 605 8/30/2008 8:00 AM
1 2 2009 605 8/30/2008 8:00 AM
1 3 2009 605 8/30/2008 8:00 AM
1 4 2009 605 8/30/2008 8:00 AM
1 5 2009 605 8/30/2008 8:00 AM
1 6 2009 605 8/30/2008 8:00 AM
1 7 2009 605 8/30/2008 8:00 AM
1 8 2009 605 8/30/2008 8:00 AM
1 9 2009 605 8/30/2008 8:00 AM
1 10 2009 605 8/30/2008 8:00 AM
1 11 2009 605 8/30/2008 8:00 AM
1 12 2009 605 8/30/2008 8:00 AM
1 1 2010 2000 1/12/2010 2:00 PM
1 2 2010 2000 1/12/2010 2:00 PM
1 3 2010 2200 3/24/2010 10:00 AM
2 7 2009 1000 7/20/2009 8:00 AM
2 8 2009 1000 7/20/2009 8:00 AM
2 9 2009 1000 7/20/2009 8:00 AM
2 10 2009 1400 10/14/2009 9:00 AM
2 11 2009 1400 10/14/2009 9:00 AM
2 12 2009 1400 10/14/2009 9:00 AM
2 1 2010 1610 1/30/2010 4:00 PM
2 2 2010 1610 1/30/2010 4:00 PM
2 3 2010 1800 3/15/2010 1:00PM
I think that this is the most accurate dipiction of the problem that I can give.
I will now say what I have tried. Although if someone else has a better approach, I am perfectly willing to throw away what I have done and do it differently...
STEP 1: create a query that removes the duplicates from the data. Ie. only one record per equip_id for each month/year, keeping the latest one. (done successfully)
STEP 2: create a table of the date ranges the client wants the report for. (This is done dynamically at runtime) This table two field, Month and Year. So if the client wants a report from FEb 2008 to March 2010 the table would look like:
Month Year
2 2008
3 2008
.
.
.
12 2008
1 2009
.
.
.
12 2009
1 2010
2 2010
3 2010
I then left joined this table with my query from step 1. So now I have a record for every month and every year that they want the report for, with nulls(or blanks) or sometimes 0s (not sure why, access is weird, but sometiems they are nulls and sumtimes they are 0s...) for the runtimes that are not avaiable. I don't particurally like this solution, but ill do it if i have to. (this is also done successfully)
STEP 3: Fill in the missing runtime values. This I HAVE NOT done successfully. Note that if the request range for the report is feb 2008 to march 2010 and the oldest record for a particular equip_id is say june 2008, it is O.K. for the runtimes to be null (or zeros) for feb - may 2008.
I am working with the following query for this step:
SELECT equip_id as e_id,year,month,
(select top 1 runhours from qry_1_c_One_Record_per_Month a
where a.equip_id = e_id order by year,month)
FROM qry_1_c_One_Record_per_Month
where runhours is null or runhours = 0;
UNION
SELECT equip_id, year, month, runhours
FROM qry_1_c_One_Record_per_Month
WHERE .runhours Is Not Null And runhours <> 0
However I clearly can't check the a.equip_id = e_id ... so i don't have anyway to make sure i'm looking at the correct equip_id
SUMMARY: So like i said i'm willing to throw away any part, or all of what I tried. Just trying to give everyone a complete picture.
I REALLY apreciate ANY help!
Thanks so much in advance!
