Trying to find the highest number in a field....
-
Hello everyone, I am running VB.Net 2005 looking at an Access 2003 database. I have a task that I am trying to get my head around and I need some help with. I have to read through a table and get the highest number from a certain field. I am kind of doing this already but I am doing a Count, I count the number of records returned from a filter/SQL statement and then using that amount, I add 1 to it and that is my Encounter number. But now I need change this process because it wasn't working out so great. Can someone look at this code and explain how I can change it to accomplish my task? Or point me to an article that will show me how to do this? What you will see is that I am filtering out based on values in 2 fields, then counting the records returned. Instead I need run this filter/sql statement, but then look at a 3rd field in the table called PatSurgEnNo. and of the records returned with the filter/sql statement of the first 2 fields (MR number and VisitDate), I need to find the highest number in the PatSurgEnNo fields, so I can add 1 to it and that will become my new/current Encounter number. Thanks in advance for any help that you can give! Code I have now: Dim PatSurgDate As String MR = Me.txtMRNo.Text PatSurgDate = Me.cbobxVisitDate.Text.ToString() 'Set Encounter to 0, count how many encounters for this date there are ' then add one to the encounter before creating a new entry PatSurgEncounter = 0 Dim Count As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath) Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(PatSurgAutoNo) as existingrecords from [Pat19PatSurg] where [PatSurgMRNo]=? and [PatSurgDate]=?", _ Connection) cmd.Parameters.AddWithValue("@PatSurgMRNo", MR) cmd.Parameters.AddWithValue("@PatSurgDate", PatSurgDate) Connection.Open() Count = cmd.ExecuteScalar End Using If Count >= 0 Then 'set new SurgEncounterNumber PatSurgEncounter = Count + 1 Return End If
-
Hello everyone, I am running VB.Net 2005 looking at an Access 2003 database. I have a task that I am trying to get my head around and I need some help with. I have to read through a table and get the highest number from a certain field. I am kind of doing this already but I am doing a Count, I count the number of records returned from a filter/SQL statement and then using that amount, I add 1 to it and that is my Encounter number. But now I need change this process because it wasn't working out so great. Can someone look at this code and explain how I can change it to accomplish my task? Or point me to an article that will show me how to do this? What you will see is that I am filtering out based on values in 2 fields, then counting the records returned. Instead I need run this filter/sql statement, but then look at a 3rd field in the table called PatSurgEnNo. and of the records returned with the filter/sql statement of the first 2 fields (MR number and VisitDate), I need to find the highest number in the PatSurgEnNo fields, so I can add 1 to it and that will become my new/current Encounter number. Thanks in advance for any help that you can give! Code I have now: Dim PatSurgDate As String MR = Me.txtMRNo.Text PatSurgDate = Me.cbobxVisitDate.Text.ToString() 'Set Encounter to 0, count how many encounters for this date there are ' then add one to the encounter before creating a new entry PatSurgEncounter = 0 Dim Count As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath) Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(PatSurgAutoNo) as existingrecords from [Pat19PatSurg] where [PatSurgMRNo]=? and [PatSurgDate]=?", _ Connection) cmd.Parameters.AddWithValue("@PatSurgMRNo", MR) cmd.Parameters.AddWithValue("@PatSurgDate", PatSurgDate) Connection.Open() Count = cmd.ExecuteScalar End Using If Count >= 0 Then 'set new SurgEncounterNumber PatSurgEncounter = Count + 1 Return End If
You'd do better to use SQL for both of these, use COUNT and MAX ( from memory ) instead of doing it in memory and reading all the values. select count(*) from Pat19PatSurg where [PatSurgMRNo]=? and [PatSurgDate]=? select max(PatSurgEnNo) from Pat19PatSurg where [PatSurgMRNo]=? and [PatSurgDate]=?
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
Hello everyone, I am running VB.Net 2005 looking at an Access 2003 database. I have a task that I am trying to get my head around and I need some help with. I have to read through a table and get the highest number from a certain field. I am kind of doing this already but I am doing a Count, I count the number of records returned from a filter/SQL statement and then using that amount, I add 1 to it and that is my Encounter number. But now I need change this process because it wasn't working out so great. Can someone look at this code and explain how I can change it to accomplish my task? Or point me to an article that will show me how to do this? What you will see is that I am filtering out based on values in 2 fields, then counting the records returned. Instead I need run this filter/sql statement, but then look at a 3rd field in the table called PatSurgEnNo. and of the records returned with the filter/sql statement of the first 2 fields (MR number and VisitDate), I need to find the highest number in the PatSurgEnNo fields, so I can add 1 to it and that will become my new/current Encounter number. Thanks in advance for any help that you can give! Code I have now: Dim PatSurgDate As String MR = Me.txtMRNo.Text PatSurgDate = Me.cbobxVisitDate.Text.ToString() 'Set Encounter to 0, count how many encounters for this date there are ' then add one to the encounter before creating a new entry PatSurgEncounter = 0 Dim Count As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath) Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(PatSurgAutoNo) as existingrecords from [Pat19PatSurg] where [PatSurgMRNo]=? and [PatSurgDate]=?", _ Connection) cmd.Parameters.AddWithValue("@PatSurgMRNo", MR) cmd.Parameters.AddWithValue("@PatSurgDate", PatSurgDate) Connection.Open() Count = cmd.ExecuteScalar End Using If Count >= 0 Then 'set new SurgEncounterNumber PatSurgEncounter = Count + 1 Return End If
To get the largest value in a field, you use
Max..."Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Hello everyone, I am running VB.Net 2005 looking at an Access 2003 database. I have a task that I am trying to get my head around and I need some help with. I have to read through a table and get the highest number from a certain field. I am kind of doing this already but I am doing a Count, I count the number of records returned from a filter/SQL statement and then using that amount, I add 1 to it and that is my Encounter number. But now I need change this process because it wasn't working out so great. Can someone look at this code and explain how I can change it to accomplish my task? Or point me to an article that will show me how to do this? What you will see is that I am filtering out based on values in 2 fields, then counting the records returned. Instead I need run this filter/sql statement, but then look at a 3rd field in the table called PatSurgEnNo. and of the records returned with the filter/sql statement of the first 2 fields (MR number and VisitDate), I need to find the highest number in the PatSurgEnNo fields, so I can add 1 to it and that will become my new/current Encounter number. Thanks in advance for any help that you can give! Code I have now: Dim PatSurgDate As String MR = Me.txtMRNo.Text PatSurgDate = Me.cbobxVisitDate.Text.ToString() 'Set Encounter to 0, count how many encounters for this date there are ' then add one to the encounter before creating a new entry PatSurgEncounter = 0 Dim Count As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath) Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(PatSurgAutoNo) as existingrecords from [Pat19PatSurg] where [PatSurgMRNo]=? and [PatSurgDate]=?", _ Connection) cmd.Parameters.AddWithValue("@PatSurgMRNo", MR) cmd.Parameters.AddWithValue("@PatSurgDate", PatSurgDate) Connection.Open() Count = cmd.ExecuteScalar End Using If Count >= 0 Then 'set new SurgEncounterNumber PatSurgEncounter = Count + 1 Return End If
Hi, SELECT MAX(PatSurgEnNo) as "MaxNo" FROM TableName; ... but you still need to worry about the multi-user changes. hope this helps :)
NajiCo http://www.InsideVB.NET[^] It's nice 2b important, but it's more important 2b nice...