访问查询以使用上一期间的值添加缺失的行 [英] Access query to add missing rows using values from prior period

问题描述

我有很多年很多月的检查.有时一个月检查不止一次，有时没有检查.但是，客户需要的报告要求我在他们要求报告的时间范围内每月只拥有一份记录.他们了解数据问题，并表示如果一个月内检查一次以上，则取最新一次.如果不是那个月的检查，请及时返回，直到找到并使用那个.所以一个数据样本如下:

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...

第 1 步:创建一个查询，从数据中删除重复项.IE.每个月/年每个装备 ID 只有一个记录，保留最新的.(成功完成)

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)

第 2 步:创建客户想要报告的日期范围的表格.(这是在运行时动态完成的)这个表有两个字段，Month 和 Year.因此，如果客户想要一份从 2008 年 FEb 到 2010 年 3 月的报告，表格将如下所示:

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

然后我离开并加入了我在步骤 1 中的查询的表.所以现在我有他们想要报告的每个月和每年的记录，带有空值(或空白)或有时是 0(不知道为什么，访问很奇怪，但对于不可用的运行时，有时它们是空值和 sumtimes 是 0...).我特别不喜欢这个解决方案，但如果我必须这样做，我会这样做.(这也成功了)

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)

第 3 步:填写缺少的运行时值.这我还没有成功完成.请注意，如果报告的请求范围是 2008 年 2 月到 2010 年 3 月，并且特定装备 ID 的最早记录是 2008 年 6 月，则可以.2008 年 2 月至 5 月的运行时间为空(或零).

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

但是我显然无法检查 a.equip_id = e_id ...所以我无论如何都无法确保我正在查看正确的装备 ID

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!

非常感谢！

推荐答案

使用以下 SQL 创建查询:

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;

此查询需要一个名为 YrMos 的表，其中包含以下字段:

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

• 年份:数字
• 莫:数字
• MoStart:日期/时间
• MoEnd:日期/时间

这些字段应该是不言自明的.就其价值而言，我在为与此类似的情况创建的大多数 mdb 中保留了这样的表的本地副本.我用 1/1/1991 到 12/31/2050 的行填充它，但显然你可以根据需要调整它.此外，在这种情况下您不需要它，但我在我的表中添加了第五列:DaysInMo(28 到 31 之间的数字)，因为我经常发现它很有用.

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.

这篇关于访问查询以使用上一期间的值添加缺失的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！