Searching a recordset and returning TOTALS

[Buddy008]

I have a database populated from users. I have another administrative app that we use to view what information the users have offered. I am now working on making a Totals report window, where it will show the "Totals" of certain things.

Take for example one of the user fields is level and it is basically a rating from 1 to 10. In my report I have a list labeled 1,2,3...,10 and beside each number, a total of how many people selected each such as:

10 7
9 0
8 3
7 2

etc, etc

Private Sub muViewAll_Click()
'first set my var to 0
   
   level1 = 0
   level2 = 0
   level3 = 0
   ...
   level10 =0

'next make sure that the recordset is on the first record

   rs.movefirst

'I'm having a problem here, because if the user has been browsing 
'through the records and absoluteposition <> 1 then the form is all 
'jacked, but i think i can fix that one easily.
'ok start checking and collecting

   do while not rs.EOF
   if rs.Fields("level") = "1" then
        level1 = level1 + 1
   end if
   if rs.fields("level") = "2" then
        level2 = level2 + 1 
   end if
   ...
   if rs.fields("level") = "10" then
        level10 = level10 + 1
   end if
'tried with
   rs.movenext
'if i rem that line out, i get an overflow error
   loop

   frmReportAll.show
End Sub

now all of that works, but if you close out the report, and try to browse through the records using the first form, depending on what the absoluteposition was when you clicked on muViewAll, things go crazy. Is there a way to say

'before the loop see where absolutepostion is
    strAP = rs.AbsolutePostion
'and when you come back from your report go back to the record you were at.  because the loop throws off your actual position in the recordset.

Also.. is there an easier way to search and collect mass info from the recordset. I somewhat know how to use SQL statements, but I don't really know limitations or what not. It doesn't seem like you could use a SQL statement to check every record at once, and if true do something.

Alright well, any help is greatly appreciated. This is a very important project.

Thanks in Advance,

Buddy

[Shuja Ali]

You could use a Group BY query to get the total by each Field

 Select Level, Count(*) From yourTableName Group By Level

You will have to open a recordset using this statement and that recordset will contain grouped data. Don't forget to check the field name and table in my query before you use it.

[Buddy008]

Great, but in my seems like eternal browsing of these forums, I have seen, a few times, some negativity pointing toward having more than one recordset open at a time. So to use this Groupby command and open a new recordset (pulling from the same table), it would probably be a good idea for the to close the first session on click of the menu item, and then maybe to reopen the original connection on exit of the report... right?

Thanks again,

Buddy

Edit: I just remembered something else

so that query will return a grouped record set, but do you have to add:

 mySQL "Select Level, Count(*) from myTable group by level  where level = "1" 

and if you had to add the "where level = num" statement to the end of that, would i have to create a recordset with every value that I want to retrieve? In this instance alone 10 different recordsets.
For another instance we have approx. 35 apps that our users "could" be using on the network and then another 35 fields for something else. So if this were true i'm looking at a total of about 80 recordsets... yikes.

[Buddy008]

Looks like I'm not quite understanding how this works:

mySQL = "Select Level, Count (*) from myTable group by level"

I know obviously what myTable is, and I am assuming the second "level" is field name.
I am also now assuming that the first Level, Count are arguments that require something specific.

On a second note, once we get this SQL statement working, how would I say make:

label1.caption = 'all instances of 1 from FN level
label2.caption = 'all instances of 2 from FN level
'...
label10.caption = 'all instances of 10 from FN level

*FN = field name

Thanks again

[Shuja Ali]

Take for example one of the user fields is level and it is basically a rating from 1 to 10. In my report I have a list labeled 1,2,3...,10 and beside each number, a total of how many people selected each such as

Your rquestion was that you would like to get the number of each levels that the user has selected. So in this case Group By Query will be the most efficient way.

Great, but in my seems like eternal browsing of these forums, I have seen, a few times, some negativity pointing toward having more than one recordset open at a time. So to use this Groupby command and open a new recordset (pulling from the same table), it would probably be a good idea for the to close the first session on click of the menu item, and then maybe to reopen the original connection on exit of the report... right?

Well I don't see anything wrong in opening two recordsets using the same table. And anyway the second recordset you open will be just a readonly one.

I would suggest that you open you query analyzer and try to run this query once and see what the results you get

Select [Level], Count(*) From myTable Group By [Level] Order By Cast([level] as int)

Here both [Level] are field names. Take a Look at SQL Server Books online to see the documentation of rest of the keywords.

[Silly Star]

Your rquestion was that you would like to get the number of each levels that the user has selected. So in this case Group By Query will be the most efficient way.
Well I don't see anything wrong in opening two recordsets using the same table. And anyway the second recordset you open will be just a readonly one.

I would suggest that you open you query analyzer and try to run this query once and see what the results you get

Select [Level], Count(*) From myTable Group By [Level] Order By Cast([level] as int)

Here both [Level] are field names. Take a Look at SQL Server Books online to see the documentation of rest of the keywords.

i think to use "Count(FiledName)" will make the query more optimized instead of using "Count(*)" like this

Select [Level], Count(Rate) From myTable Group By [Level] Order By Cast([level] as int)

[Shuja Ali]

i think to use "Count(FiledName)" will make the query more optimized instead of using "Count(*)" like this

Select [Level], Count(Rate) From myTable Group By [Level] Order By Cast([level] as int)

The OP says that the Data should be presented in this way

Level Count
==== =====
10 7
9 0
8 3
7 2

[Buddy008]

I would suggest that you open you query analyzer and try to run this query once and see what the results you get

Select [Level], Count(*) From myTable Group By [Level] Order By Cast([level] as int)

Here both [Level] are field names. Take a Look at SQL Server Books online to see the documentation of rest of the keywords.

Ok so I forgot to mention I was using MS Access and not a SQL Server. Maybe that could be the reason my app is bugging out at the

mySQL = "Select [Level], Count(*) From myTable Group by [Level]"

It's having a problem with the first instance of [Level]. I'll post the specific error here in a bit, I'm developing on a different PC.
::EDIT:: Exact error:
Run-time error '-2147217900(80040e14)':
The LEVEL clause includes a reserved word or argument
that is misspelled or missing, or the punctuation is incorrect.

I figured out that I was getting this error because I didn't have the [] around all instances of the word Level. Go figure hehe..

So now my report shows, but all values are equal to zero. I know that is because I don't fully understand the SQL statement
::END EDIT::



Also, I think I'm getting a little confused as to what this line actually does. Should I assume that this new recordset will contain all numbers, and the counts of each of those numbers. Let's say label1 through label10 are the actual numbers 1-10, and lblCnt1 through lblCnt10 are the counters for each of those numbers respectively.

Ok so this is what we got so far

'conn is my connection to the DB and is already open
Dim rsLevel as New ADODB.Recordset
Dim mySQL as string

Set rsLevel = New ADODB.Recordset

mySQL = ""Select [Level], Count(*) From myTable Group by [Level]"

rsLevel.open mySQL, conn, adOpenDynamic, adLockOptimistic

label1.caption = "1"
lblCnt1.caption = rsLevel  ???  'how do i separate the values here?
label2.caption = "2"
lblCnt2.caption = rsLevel ???
'and so on

Oh and I was thinking... what I saw in the forums was about having multiple connections opened... not recordsets, but thanks for setting me straight there

Well Happy New Year... and thanks a ton for all of your help so far.

[Shuja Ali]

Try this. Open your access database, goto Queries, create a new query and run this Query in Access to see what kind of result it returns. This way you will get an idea of what you are trying to achieve with this query.

Try to post the structure of your Table. That will makes things much more clear.

You do not have to give any condition in your query because this will return the count of all the levels.

Also to show it I would Rather use a DataGrid. Just bind the recordset to the DataGrid and all your data will be shown. No need to loop through the recordset.

And to access Fields in the recordset you need to either use the Index or the Field Name

 Debug.Print rsLevel(0)

Will print the data in the First Field and

Debug.Print rsLevel(1)

Will print the data in the second field.

[Buddy008]

Alright just tested some things out.

I tried testing out the SQL statement in access, but it had to go from
Select [Level, count(*) from Survey Group By [Level]
to
Select [Level] FROM Survey GROUP BY [Level] HAVING Count(*)
For whatever reason, using ",count(*)" after level in Access causes an error, but using that in VB works just fine"


This returned a table with the numbers in a column.

The next thing I tried was:
Printing to debug using the following:

Dim sGroup as String
Dim rsGropu As New ADODB.Recordset
sGroup = "Select [Level], Count(*) from Survey Group By [Level]"

Set rsGroup = New ADODB.Recordset
rsGroup.Open sGroup,conn, adOPenKeyset, adLockOptimistic

Debug.Print rsGroup(0)
Debug.Print rsGroup(1)
...
Debug.Print rsGroup(10)

I get values returned from 0 and 1 being 10 and 1 respectively... I am assuming that 0 is a value from the field and 1 is the count of how many times that number has shown up. However these are the only two (what do I want to say here "controls in the array" maybe??) that have a value associated with them.

Right now I only have a few test records in my database... One has a level of "10" and two have a level of "8". I would think that rsGroup(2) should return a value of "8" and rsGroup(3) should return a value of "2"

Try to post the structure of your Table. That will makes things much more clear.

Not really sure what you mean here. It's just a normal table. It has a total of 12 Fields.... with Level being one of those fields. Every user that takes this survey adds a new record to the table. So basically I am querying through all the records to find how many entries of "1" "2", ... "10" are in the Field "Level".

I also could not get Order By Cast([level] as int) to work for me in Access, so I didn't even try to add that in my app.

Thanks for all your help so far... and the quick responses... this is a very important project for me.

UPDATE**
Ok a final test helped me hugely!! I created a datagrid on my form, and used this rs as the datasource. When you use the Group By parameter, it creates a Field Named "Expr1001". So now what I am doing, is using my field name for the first caption, and the new field name as the second.

Thanks for all your help

[Buddy008]

I was going to just edit this into my last post, but it's almost a different problem.

Alright I may have run into another small bump in the road. I wasn't looking far enought into the future I suppose. So let me explain a little bit. I have 3 tables in this DB. One for random info... like everything discussed above.

The next two tables are where my concern comes into play.

We have approx. 31 apps that may or may not be used on our network. With this survey, we are hoping to get some info back as to you uses what and how much. So I have one table that says if they use the app or not, and another table on whether this particular app is essential for the job. Now using the Group BY parameter, I know I can't use

mySQL = "SELECT[*], Count(*) from Software Group By[*]"
'I also Tried
mySQL = "SELECT[*], Count(*) from Software Group By [ID]"

how do I know this... because I have already tried Apparently it would be too good to be true if this actually worked. So, as I see it, my other option is to do a group by SQL statement for every app x2 and that means we're looking at around 62 extra SQL statements and Recordsets being opened.

Which could or could not be problematic...
Since everything executes so quickly, running say 62 + 10(from the other table) = 72 queries + opening RS's in a short amount of time could cause some chaos. Especially if one of our admins is browsing info while a user is inputing.

I guess my question is, is there any way to get around having to write and open 62 RecordSets and SQL statements??

If not ... lots-o-typin ahead

[Shuja Ali]

Dim sGroup as String
Dim rsGropu As New ADODB.Recordset
sGroup = "Select [Level], Count(*) from Survey Group By [Level]"
 
Set rsGroup = New ADODB.Recordset
rsGroup.Open sGroup,conn, adOPenKeyset, adLockOptimistic
 
Debug.Print rsGroup(0)
Debug.Print rsGroup(1)
...
Debug.Print rsGroup(10)

I get values returned from 0 and 1 being 10 and 1 respectively... I am assuming that 0 is a value from the field and 1 is the count of how many times that number has shown up. However these are the only two (what do I want to say here "controls in the array" maybe??) that have a value associated with them.

Right now I only have a few test records in my database... One has a level of "10" and two have a level of "8". I would think that rsGroup(2) should return a value of "8" and rsGroup(3) should return a value of "2"

You need to understand that when you run this query it will return multiple rows depending upon the number of records in Table but all the rows will have just two Columns. So when you try to write rsGroup(2) this will return an Error. Your code should rather look like this

Dim sGroup as String
Dim rsGropu As New ADODB.Recordset
sGroup = "Select [Level], Count(*) from Survey Group By [Level]"
 
Set rsGroup = New ADODB.Recordset
rsGroup.Open sGroup,conn, adOPenKeyset, adLockOptimistic
'Loop through the recordset printing the two columns
While Not rsGroup.EOF
'Print First Column
Debug.Print rsGroup(0)
'Print Second Column
Debug.Print rsGroup(1)
'Move to the next record
rsGroup.MoveNext
Wend

Regarding your second posts, I am totally not able to understand what you are trying to achieve.

I would suggest that you take a look at Group By clause in MS-Access documentation. And see whether it will be helpful to you in cuttin down the number of queries.

I don't think that you would need 72 Queries to get the data from the database. There has to be an easier way and you need to be more clear as to what you are trying to do.

[Buddy008]

You need to understand that when you run this query it will return multiple rows depending upon the number of records in Table but all the rows will have just two Columns. So when you try to write rsGroup(2) this will return an Error. Your code should rather look like this

Yes, I realized that. My fix was ... I created a Datagrid, and bound it to the new RecordSet. When I ran my application, I saw the one column with the actual data in my table, and a second column which the Group By parameter creates. It's field name is "Expr1001". So now I have some If Then statements that make label1.caption = FN Apps and lblcount1 = FN Expr1001.

This works perfectly!!

Regarding your second posts, I am totally not able to understand what you are trying to achieve.

I would suggest that you take a look at Group By clause in MS-Access documentation. And see whether it will be helpful to you in cuttin down the number of queries.

I don't think that you would need 72 Queries to get the data from the database. There has to be an easier way and you need to be more clear as to what you are trying to do.

Ok let me try to explain this better...
I am creating a form that is basically a totals report. Taking certain fields from my tables, counting the total number of times they appear, and showing that value as well.

My database has 3 different tables... One with various info. That's easy I make a SQL statement for each FN that I want to collect totals for, and work with that.

The other two tables are where it goes beyond this.

The information in these tables is related to software... so 31 different applications, and the value will either be "Yes or No"

So Using the Group By parameter, collect all the instances of "Yes" and all the instances of "No" to calculate the total number of people that use each piece of software.

The second table is exactly the same, but only for applications which people deem essential to their work...

Each piece of software is in its own Field... so altogether that is 62 fields.

What I didn't realize before was I was simply getting the totals from one field. I wasn't thinking about my table where I needed to calculate totals for every Field.

So I was wondering if anyone knew other uses for Group By... You obviously can't use a wildcard (*), so the only other way I can see to do this is to create a SQL statement and recordset for each individual field.

I hope this makes things at least a little more clear

Thanks again,
Buddy

[Shuja Ali]

I would suggest redesigning the Table structure. This way you will have to write 62 queries to group each data.

I am not sure if you could do this in one query.

[Buddy008]

What if I restructured the table to have 3 Fields

Field1 = Software
Field2 = Yes
Field3 = No

Manually add a record for each piece of software, and set Fields 2 and 3 to 0

Then when a user selected yes, it would take the val of .Fields("Yes") and add 1 to it.

That way, I would only have one simple SQL statement.

However for this to work, I would need a way of opening, field1 with field2 for example (this doesn't work, but something like this maybe???)

'already have connection and recordset open
if check1.value = "1" then
     rs.fields("Software"), rs.fields("Yes") = rs.fields("Software"), rs.fields("Yes") + 1
else
     rs.fields("Software"), rs.fields("No") = rs.fields("Software"), rs.fields("No") + 1

Well typing that made me realize something. All you would have to do is open up the record having software name, and add to either yes or no.

So I guess I need to search modifying a record in access.

Does any of this sound feasible... better than my last choice??

Thanks a bunch yet again

**EDIT**
I have gotten this to work somewhat. I can modify the value in the field itself such that if I say rsfields("No") = rsfields("No") + 1, it will add the value in there.

My problem now is that obviously won't work, because it will just hit the first record in the set. I'm kind of confused about a proper sql statement to run here. It would be great if there was away other than SQL statement to get the result I want here, but from browsing for an answer, there doesn't really seem to be another option.

So what do I need the "Search" to do:

User says they use outlook.

Find Field1 = Software
Outlook
Find Field2 = Yes + 1
Find Field3 = No ... No change

Any help is greatly apprectiated as this is becoming time sensitive.