-
December 30th, 2005, 08:32 AM
#1
Searching a recordset and returning TOTALS
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
Code:
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
Code:
'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
-
December 30th, 2005, 08:41 AM
#2
Re: Searching a recordset and returning TOTALS
You could use a Group BY query to get the total by each Field
Code:
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.
-
December 30th, 2005, 09:45 AM
#3
Re: Searching a recordset and returning TOTALS
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:
Code:
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.
Last edited by Buddy008; December 31st, 2005 at 04:14 PM.
-
December 30th, 2005, 10:11 AM
#4
Re: Searching a recordset and returning TOTALS
Looks like I'm not quite understanding how this works:
Code:
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:
Code:
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
-
January 2nd, 2006, 02:11 AM
#5
Re: Searching a recordset and returning TOTALS
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.
Originally Posted by 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?
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
Code:
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.
Last edited by Shuja Ali; January 2nd, 2006 at 04:11 AM.
Reason: Corrected the Quote Tag
-
January 2nd, 2006, 03:57 AM
#6
Re: Searching a recordset and returning TOTALS
Originally Posted by Shuja Ali
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
Code:
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
Code:
Select [Level], Count(Rate) From myTable Group By [Level] Order By Cast([level] as int)
-
January 2nd, 2006, 04:13 AM
#7
Re: Searching a recordset and returning TOTALS
Originally Posted by Silly Star
i think to use "Count(FiledName)" will make the query more optimized instead of using "Count(*)" like this
Code:
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
-
January 5th, 2006, 06:56 AM
#8
Re: Searching a recordset and returning TOTALS
Originally Posted by Shuja Ali
I would suggest that you open you query analyzer and try to run this query once and see what the results you get
Code:
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
Code:
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
Code:
'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.
Last edited by Buddy008; January 5th, 2006 at 07:42 AM.
-
January 5th, 2006, 07:53 AM
#9
Re: Searching a recordset and returning TOTALS
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
Code:
Debug.Print rsLevel(0)
Will print the data in the First Field and
Code:
Debug.Print rsLevel(1)
Will print the data in the second field.
-
January 5th, 2006, 10:18 AM
#10
Re: Searching a recordset and returning TOTALS
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:
Code:
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
Last edited by Buddy008; January 6th, 2006 at 07:15 AM.
-
January 6th, 2006, 07:25 AM
#11
Re: Searching a recordset and returning TOTALS
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
Code:
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
-
January 6th, 2006, 07:48 AM
#12
Re: Searching a recordset and returning TOTALS
Originally Posted by Buddy008
Code:
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
Code:
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.
-
January 6th, 2006, 12:04 PM
#13
Re: Searching a recordset and returning TOTALS
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
-
January 9th, 2006, 04:43 AM
#14
Re: Searching a recordset and returning TOTALS
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.
-
January 9th, 2006, 08:34 AM
#15
Re: Searching a recordset and returning TOTALS
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???)
Code:
'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.
Last edited by Buddy008; January 9th, 2006 at 11:39 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|