每年，每月分组Visual Basic [英] Yearly, monthly grouping Visual Basic

问题描述

我有一个论文项目，应该将我的数据库按年和月分组

I have this thesis project where I should group my database to yearly and monthly

这是字段和数据示例

表名是:tblsell

The table name is : tblsell

Date_Saved | Total Price

2-5-2013        5
2-5-2013        10
2-5-2013        5
2-6-2013        6
2-7-2013        3
2-8-2013        2
3-1-2013        6
3-2-2013        11
3-3-2013        2
3-5-2014        5
3-6-2014        4

现在，我打算创建2个按钮，以便当我单击每月按钮时，该按钮应显示在datagridview上

Now, I'm planning to create 2 buttons so that when I click on the monthly button this should show on datagridview

Date_saved | Total_Earnings

2-2013 or february 2013       31
3-2013 or march 2013          19
3-2014 or march 2014          9

然后，如果我单击年度按钮，它将显示在datagridview

Then if I click the yearly button this should show on datagridview

Date_saved | Total_earnings

2013        50
2014        9

我不确定是否要声明正确的输出，但是至少应该在该输出附近进行操作.

I am not sure if I'm stating the correct output but at least near that output should do it.

date_saved的数据类型是文本，total_earnings是访问时的数字.我通过使用datestring获得date_saved.像.add... = datestring.我不知道它是否正确，所以我可以得到日期.

The datatype of date_saved is text and total_earnings is number on access. I get the date_saved by using datestring. like .add... = datestring. I don't know if its correct so I can get the dates.

我正在reportviewer上尝试使用它们，但是我不知道该如何在reportviewer上进行操作，因此我放弃了，只是认为在datagridview和按钮上更方便.

I'm trying them on reportviewer but I have no idea what to do on reportviewer so I gave up and just thought its easier on datagridview and buttons.

推荐答案

如果您希望将此作为数据网格，那么这是您的两个查询选项

If you want this as a datagrid then these are your two query options

基表Table1如此设置

您可以像这样创建新查询

you can Create a New Query like this

哪个生成此代码.

SELECT MonthName(Month([Date_Saved])) & ' ' & Year([Date_Saved]) AS [Year], Sum(Table1.[Total Price]) AS [SumOfTotal Price]
FROM Table1
GROUP BY MonthName(Month([Date_Saved])) & ' ' & Year([Date_Saved]);

并产生这些结果.

分组是这里的关键.有几种分组方式，而我在那做的方式使您的datagrid看起来格式化"，但比诸如

the grouping is the key here. There are several ways to group and they way I did it there makes your datagrid look "formatted" but is slower than methods such as

SELECT  DATEADD('m', DATEDIFF('m',0,date_saved), 0) as [Month], sum([total price]) as [Month's Total Price]
FROM Table1
group by DATEADD('m', DATEDIFF('m',0,date_saved), 0)

在功能上等效，但是由于它不执行任何数据类型转换，因此运行速度更快.此外，它还有一个好处，就是您现在可以在报表的文本框中设置日期的格式，但是感觉却并不决定查询时间.缺点是与使用month，monthname，year函数相比，dateadd/datediff方法有点钝.

Which is functionally equivalent but because it doesn't do any datatype conversions it runs faster. Plus it has the added benefit that you can now format your date in a textbox on a report however you feel and not decide a query time. The downside is that the dateadd/datediff method is a little obtuse compared to using the month, monthname, year functions.

这篇关于每年，每月分组Visual Basic的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！