Access, 2 date ranges in one querry

[GierekG]

I have a central store data containing products that were taken out (90% of all) or returned (10% of all) from the last 3 years. I need to do an analysis that will contain the most expensive items (qt * price) taken out in the certain time period minus the items that were returned. I also want to represent this data on a monthly chart (1 Serie = 1 year). Right now I display all of it in a ListView (monthly data is hidden in the list view so when I click on an item it draws a chart to show monthly usage) and everything works great including the chart. What I cannot do is when I use the datetimepickers to narrow down the date ranges it also narrows down my chart results.

My question is: Is it possible to somehow combine 2 queries (one with date range) to display those results and one without it (so it will contain the whole 3 yers) to display on a chart.

Basicaly what I want to do is to use one Listview to display data from selected time period (first 4 columns: PartNo, Name, Quantity, TotalValue) and the rest of the columns will contain usage from 36 previous months (1 column = 1 month) and those columns should not be narrowed with date range.

I hope it does make sense.

[dunfiddlin]

No. How could it? You're asking to search simultaneously for a complete set and one of its subsets which is completely illogical. Clearly you need to add all items to the listview or a datatable and then use subsets of that information in your calculations/charting etc.

[Shaggy Hiker]

I think that a datatable would be the place to store the whole set, then I'd look at using dataviews to filter for subsets. Dataviews are pretty convenient for this because you can have several different ones for a single datatable, you can filter them with a SQL-like statement in the RowFilter property, and you can bind them just as you would a datatable.

[jmcilhinney]

I concur with the previous suggestions but I think that all you need is one DataTable. If you use a data adapter or data reader to populate a DataTable with all the data then that can be the source for your chart. Every DataTable is associated with a DataView by default, via its DefaultView property. You can simply set the RowFilter of the DefaultView of the DataTable and then get the filtered data from that DataView for your ListView.

That said, you really shouldn't be using a ListView at all. Man, so many beginners love using ListViews when they should be using a DataGridView. If you were using a DataGridView then you could bind your DataTable to it and setting the RowFilter would automatically filter the data in the grid without your having to use any code at all.

[GierekG]

Thank you guys for all the responses. Obviously I am a beginner in this. I've been trying to use datagridview but I could not get it to work as I wanted, I did not like the Querry Builder which always gave me an error that it does not recognize the '=' sign. And maybe I got something wrong but for me the row filter in data view worked only for columns that were included in the SELECT statement and also since my querry groups the results together I could not filter the date range. The only way I found around this is to run 2 simultaneous querries at once and populate them into 2 different views (one for results and one for charts).

The querry I am running right now looks like this:

sql = "SELECT TOP 200 SerialNo as Serial," & _
" Name as Name," & _
" SUM(IIF(Smb = 'RW', QT, QT*(-1))) as Quantity," & _
" SUM(IIF(Smb = 'RW' , Value, Value*(-1))) as TotalValue," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 1 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 1_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 2 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 2_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 3 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 3_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 4 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 4_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 5 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 5_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 6 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 6_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 7 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 7_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 8 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 8_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 9 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 9_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 10 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 10_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 11 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 11_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 12 AND YEAR([Data Dok]) = 2012 , QT, 0)) as 12_2012," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 1 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 1_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 2 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 2_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 3 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 3_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 4 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 4_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 5 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 5_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 6 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 6_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 7 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 7_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 8 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 8_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 9 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 9_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 10 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 10_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 11 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 11_2013," & _
" SUM(IIF(Smb = 'RW' AND MONTH([Data Dok]) = 12 AND YEAR([Data Dok]) = 2013 , QT, 0)) as 12_2013" & _
" FROM Data_All" & _
" WHERE Dept LIKE '%" & ComboBox1.Text.Replace("ALL", "%") & "%' AND Cost_Type LIKE '%" & ComboBox4.Text.Replace("ALL", "%") & "%'" & _
" AND WO LIKE '%" & Label8.Text.Replace("0", "%") & "%'" & _
" AND [Data dok] BETWEEN #" & Format(date1, "MM/dd/yyyy") & "# AND #" & Format(date2, "MM/dd/yyyy") & "#" & _
" GROUP BY SerialNo, Name" & _
" ORDER BY 4 Desc"

Other than running second querry in the same time to populate different results I really don't know what else I could do.

PS. The [Data dok] is the document date and SMB is a type of document (whenever something was taken or returned)

[Shaggy Hiker]

When you say that the rowfilter only worked on items in the SELECT statement, what did you expect it to do? The dataview is based off the datatable, and the datatable has only the columns that you indicate in your SELECT statement. The datatable is no longer attached to the database, and has no knowledge of the database other than that which it gathered from the query itself. Therefore, you have to have all the data you need in that datatable for the rowfilter to work on it, and it will most certainly be restricted to only the fields present in the datatable, which are the fields you supplied in the SELECT statement, so it sounds right. Of course, you can have fields in the datatable that don't get displayed and are just there for convenience, but considering how you are aggregating this data, I don't think any extra fields would make sense. Therefore, I think you will have to have a second query to build up a second datatable with the other information you need.

Alternatively, you might consider rethinking your strategy. This may not be a viable option, but it seems to me that the query you are showing is all about aggregating the data in a certain way, but the problem you are having is that at times you don't want to have the data aggregated at all. If that is correct, then there is really only one option, though it can appear as two: Get all the data you need for both purposes.

What you might consider is whether you can make one query that fills a datatable with all the information you need, then aggregate it for display from that datatable. That may not perform all that well, though, especially since you want to get all those columns for display. Therefore, it may just be best to have one query that gets the aggregates as you have now, and a second query that gets all the raw data for all other uses. It's really just a question of versatility versus performance. You are probably going to get better performance the way you are going now, but it isn't the most versatile. That's a trade that is often worth making.

[GierekG]

Ok, I think I will stick with running 2 queries separately. First will fill the data into ListView/DataGridView so it will be possible to see and review it and once the first querry is done a background querry starts (without date range) to fill the data for the charts. I just narrowed down the first query to 4 columns (PartNo, Name, QT, TotalValue) and the other querry selects the rest of the data (PartNo, quantity in every month) but they both are aggregated in some way.

Thank you Shaggy Hiker and everybody else for all your help.