CODEWITHSUNDEEP
vb.netvbagrouping
Billy Otsuka
Billy Otsuka

Reputation: 21

Yearly, monthly grouping Visual Basic

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

Here is a the fields and data sample

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

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

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.

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.

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.

Upvotes: 0

Views: 2054

Answers (1)

Brad
Brad

Reputation: 12253

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

With your base table Table1 set up like this

enter image description here

you can Create a New Query like this enter image description here

Which generates this code.

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]);

And produces these results.

enter image description here

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)

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.

Upvotes: 2

Related Questions