|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 22nd, 2006, 07:42 PM
|
|||
|
|||
VBA running sum needed by group
well, i have officially gone crazy over this.
Overall, i needed to obtian a Running Sum for each group in my query results or a count starting at 1 and increasing by 1 dependant on the amount results each employee has. After searching the previous posts ("how to create a table in ms access db using vb code?" dated Oct 26, 2004) which is very close to what i need but i am having trouble converting his code to work for my needs. Keep in mind that I am very new to VBA, therefore, this is a mix and match attempt at getting where i need to be. In other words, I could be in the wrong direction so please feel free to scratch whatever. Any assistance is GREATLY APPRECIATED. Code:
Public Function ProdLineUp()
Dim rsEmployee As Recordset, rsUpdate As Recordset
Dim qdf As QueryDef
Dim X As Integer
Dim strSQL As String
'pulls all employees that require a running tally by Production
Set rsEmployee = CurrentDb.OpenRecordset("qry_Downtime_Limited_Count_Employee")
rsEmployee.MoveLast
rsEmployee.MoveFirst
'contains all desired fields EXCLUDING running tally
Set qdf = CurrentDb.QueryDefs("qry_Update")
For X = 1 To rsEmployee.RecordCount
qdf.Parameters(0) = rsEmployee.Fields("Last_Name")
Set rsUpdate = qdf.OpenRecordset
'this is where i need to located the running tally...possibly using dsum function with default=1 and if recordsetcount>1 then recordset count +1 ?????
'then i need to update all info from "qry_Update" plus the newly created running tally into tbl_Downtime_Production_Count
DoCmd.RunSQL "INSERT INTO tbl_Downtime_Production_Count SELECT Last_Name as Last_Name, Production_Name as Production_Name, PurposedStartDate as PurposedStartDate, PurposedEndDate as PurposedEndDate, " ' & X & '" as FASProdLineUp) " _
& "FROM qry_Update;"
rsUpdate.Close
rsEmployee.MoveNext
Next X
Set rsUpdate = Nothing
Set rsEmployee = Nothing
Set qdf = Nothing
MsgBox "Update Complete"
End Function
tuk Last edited by tuktuk : May 23rd, 2006 at 08:25 PM. Reason: Found previous threads that pointed in diff direction
|
|
#2
May 23rd, 2006, 08:43 PM
|
|||
|
|||
|
told you i was going crazy....the correct reference that matches what i am trying to do is "SQL 'insert into' in vba query not updating table "
tuk
|
|
#5
May 26th, 2006, 12:37 PM
|
|||
|
|||
|
I can also create a update query to a table, say [tbl_increment] with a Primary Key set to incrementing for EmployeeOne. Then create another update query to pull all of [tbl_increment] including the primary key field/'assignment count' (the desired info) to a 2nd table, called [tbl_increment_combined]. the delete [tbl_increment] and repeat the procedure for EmployeeTwo?
this would entail having a vba code to delete the old table, say for employee one and create a new for employee two and repeat the procedure until end of employee but the coding for that is completely beyond me. can anyone figure this one out? i am game for what ever is easiest. Tuk
|
|
#6
May 26th, 2006, 03:18 PM
|
|||
|
|||
|
here is my attempt at creating the table and deletign the table...keep in mind i need to add the recordset count and loop in CreateTable(). I am currently unable to create the table...it is erroring on "cmd.execute"; syntax error
Private Sub CreateTable() Const strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\my documents\mdb1.mdb;Persist Security Info=False" Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command cnn.ConnectionString = strConnection cnn.Open cmd.ActiveConnection = cnn cmd.CommandText = "create table tbl_Increment (a autonumber, b varchar(20))" cmd.Execute cnn.Close End Sub Private Sub DeleteTable() DoCmd.SetWarnings False 'this just deletes that data in the tbl_Increment. 'i need VB code that removes the entire tble so that the next time CreateTable() is ran the autonumber field will start at "1". DoCmd.OpenQuery "DELETEQUERY", acViewNormal, acEdit DoCmd.SetWarnings True End Sub
|
|
#7
May 26th, 2006, 07:13 PM
|
|||
|
|||
|
well, i have figured out how to create the table.
Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command cnn.ConnectionString = strConnection cnn.Open cmd.ActiveConnection = cnn cmd.CommandText = "create table tnn1 (a number, b varchar(10))" cmd.Execute cnn.Close jsut like the string said....my bad. will auto create a table with a AutoNumber field? my test indicate no. also i tried using for following to delete the table but no go. can you see how to fix it? Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command cnn.ConnectionString = strConnection cnn.Open cmd.ActiveConnection = cnn cmd.CommandText = "delete table tnn1 (a number, b varchar(10))" cmd.Execute cnn.Close
|
|
#8
May 27th, 2006, 02:47 AM
|
|||
|
|||
|
pmlakshmis
Quote:
'query to create sum of particular field '$$$$ rs.open " select sum(no) from table_name group by name ",cnn,1,3 '$$$$$ msgbox rs.recordcount ' this would give u the record count '$$$$$
|
|
#9
May 30th, 2006, 08:22 PM
|
|||
|
|||
|
i would like to have an autonumber associated with each line item rather than a summation.
therefore, i have a new table being created using code from a previous string: Const strConnection As String = 'database location Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command cnn.ConnectionString = strConnection cnn.Open cmd.ActiveConnection = cnn cmd.CommandText = "create table tnn1 (a number, b text)" cmd.Execute cnn.Close End Sub how can i change the "data type" for the newly created table to be a Primary Key or an field with a Autonumber setting?
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > VBA running sum needed by group |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
