多个 ID 按日期排列的平均值表

发布于 2024-08-08 02:40:42 字数 382 浏览 8 评论 0原文

我有一个充满项目的表，每个项目都有一个唯一的 ItemID。还有另一个表，其中包含每月一次对这些项目进行的测试的信息（有些可能每月进行多次测试，有些可能几个月没有），每个测试条目都有完整的日期 mm/dd/yyyy。我想显示每月所有数据中某个字段的平均值。因此，假设我要平均的是物品重量，我想最终得到一个像这样的表：

Date AvgOfWeight  
1/09 24.55  
2/09 28.64  
3/09 22.39

等等...

我知道我可以执行查询来求平均值，并且可以按某些标准对平均值进行分组，但是有没有一种简单的方法可以按月对 mm/dd/yyyy 日期进行分组？

原文

I have a table full of items that each have a unique ItemID. There is another table with information on tests that are done on these items around once a month (some may have multiple tests per month and some may have none some months) and each test entry has a full date mm/dd/yyyy. I would like to display the average of one of the fields over all the data on a monthly basis. So assuming what I'm averaging is the item weight, I want to end up with a table like this:

Date AvgOfWeight  
1/09 24.55  
2/09 28.64  
3/09 22.39

and so on...

I know that I can do a query to do averages and I can group the averages by certain criteria, but is there an easy way to group mm/dd/yyyy dates by month?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝海似她心 2024-08-15 02:40:42

我喜欢这个 strzero 函数，可以这样使用：

m_monthYear = strZero(year(date()),4) & "-" & strZero(month(date()),2)

结果看起来像 2009-09，并且可以按升序排序，因为它们从年份开始。您只需按如下方式创建此 strZero 函数：

Public function strZero(x_myLong as long, x_length as integer) as string
Dim m_strZero as string

m_strZero = trim(str(x_myLong))
do while len(m_strZero) <= x_length
    m_strZero = "0" & m_strZero
loop
strZero = m_strZero
end function

这是即时编写的。请检查str的所有参数（如果有），添加错误管理，并适应您自己的需求。您可能希望将 x_myLong 作为变体传递，以便返回“0000”字符串。将数字转换为固定长度字符串时，此函数还有多种其他用途。

I love this strzero function that can be used here this way:

m_monthYear = strZero(year(date()),4) & "-" & strZero(month(date()),2)

Results look like 2009-09 and can be ordered ascending, as they begin with the year. You'll just have to create this strZero function as follows:

Public function strZero(x_myLong as long, x_length as integer) as string
Dim m_strZero as string

m_strZero = trim(str(x_myLong))
do while len(m_strZero) <= x_length
    m_strZero = "0" & m_strZero
loop
strZero = m_strZero
end function

This was written on the fly. Please check all parameters (if any) for str, add error management, and adapt to your own needs. You might like to pass x_myLong as a variant, in order to return a '0000' string for example. This function has multiple other uses when converting numbers to fixed length strings.

回复收藏 0 原文

~没有更多了~