Access Qry Questions

Question

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!

Answer 1

To clarify - you are trying to get one record per month?

The issue being that some months don't have any data?

Could you not create a table containing all date-month combinations from the past and into the future and link that date-month table to your dataset?

Answer 2

Your sample data includes year, month, and date fields.

1. All 3 names are reserved words. See Problem names and reserved words in Access.
2. Year and month can be derived from date whenever needed, so storing them redundantly is not good database practice.

Here is a SELECT statement you could use without year and month stored in your Inspections table:

SELECT i.equip_id, Year(idate) AS iyear, Month(idate) AS imonth, i.idate, i.runtime
FROM Inspections AS i;

Admittedly, this suggestion doesn't answer your question. Whether you use SQL alone or use VBA code to populate a scratch table with the results you want, I think you should start with a solid table design for your Inspections data.

Answer 3

Create a query with the following SQL:

SELECT runhours.equip_id, First(YrMos.Mo) AS [month], First(YrMos.Yr) AS [year], 
  (SELECT TOP 1 runtime FROM runhours AS rh WHERE runhours.equip_id=rh.equip_id AND rh.date <= YrMos.MoEnd ORDER BY rh.date DESC) AS runtime, 
  (SELECT TOP 1 rh.date FROM runhours AS rh WHERE runhours.equip_id=rh.equip_id AND rh.date <= YrMos.MoEnd ORDER BY rh.date DESC) AS [date]
FROM runhours, YrMos
WHERE (((YrMos.MoStart) Between #2/1/2008# And #3/1/2010#))
GROUP BY YrMos.MoEnd, runhours.equip_id
HAVING ((((SELECT TOP 1 runtime FROM runhours AS rh WHERE runhours.equip_id=rh.equip_id AND rh.date <= YrMos.MoEnd ORDER BY rh.date DESC)) Is Not Null))
ORDER BY runhours.equip_id;

This query requires a table named YrMos with the following fields:

• Yr: Number
• Mo: Number
• MoStart: Date/Time
• MoEnd: Date/Time

The fields should be self-explanatory. For what it's worth, I keep a local copy of a table like this in most of the mdbs I create for situations similar to this one. I populate it with rows from 1/1/1991 to 12/31/2050, but obviously you can adjust that as needed. Also, you don't need it for this case, but I add a fifth column: DaysInMo (Number between 28 and 31) to my table, as I have often found that to be useful.