jwcolby
jwcolby at colbyconsulting.com
Thu Nov 15 13:27:02 CST 2007
A.D.
I will get around to this, just not sure when. Running this through Access
/ VBA is probably a non starter since not only are there a lot of fields
(>600), but also a lot of records (>50 million). Thus if this is ever to
actually work it will need to be done in VB.Net. My experience so far is
that VB.Net speeds things up by a factor of 10 or more.
Even then I would suggest something like pulling the field names that will
be actually used out and placing them (or maybe their numeric field position
in the record object) in a collection. That way the code that iterates the
fields becomes a simple "for each in colValidFld" construct instead of a
constant comparison to see if they are in the string of non-valid fields.
If you place the valid field names in the collection, then the For Each loop
would "just have" the field names to build up the SQL statement.
Additionally I think you can use the field names to index into the field
collection to extract the values:
rst.fields(strFldName).value
Believe me, with 600 fields and 50 million records, it needs all the
efficiency it can get.
And BTW thanks for the code.
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Thursday, November 15, 2007 1:11 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] merging records
Gustav,
In its present version, the suggested procedure does not open any
recordset for target table. After all the values meant to be inserted in
various fields are lined up, the pertinent record gets inserted in
destination table in one go via append query.
As pointed out by you, pure recordset approach can also be adopted in
lieu of append query method. John states that the number of fields involved
is very large. That would require corresponding large number of assignment
calls from one recordset to the other.
It might be interesting if an actual trial could be conducted over a
large data set, using append query visa-vis recordset approach. Perhaps John
could do it and let us know.
Best wishes,
A.D.Tejpal
------------
----- Original Message -----
From: Gustav Brock
To: accessd at databaseadvisors.com
Sent: Thursday, November 15, 2007 13:43
Subject: Re: [AccessD] merging records
Hi A.D.
But - as you have the DAO recordset present - why not use AddNew/Update to
replace the slow "Insert Into .." SQL execution?
/gustav
>>> adtp at airtelbroadband.in 14-11-2007 20:49 >>>
John,
Apparently, each column carries only one significant value (over a
group of records) for each combination of person & address. Your objective
is to display only one compacted row per combination of person & address,
showing only the significant values for survey results in various columns
across the record.
As a programmatic solution, the following course of action is
suggested:
1 - Let the source table be named T_Data. Its first four fields are ID
(PK), FirstName, LastName and Address, followed by large number of other
fields (like Smokes etc) meant to hold survey response.
2 - Create an empty table named T_Result. Its structure should be
identical to that of T_Data.
3 - Create a dummy table T_Dummy having one field. Populate it with
one record.
Having taken the above steps, if you run sample subroutine
P_PopulateResultTable as given below, table T_Result will get populated with
the compacted survey results in desired format. You might like to try it out
and confirm whether it is in line with what you have been aiming at.
Note - It has been tested on Access 2003 desktop (Access 2000 file
format). Reference required - DAO 3.6
Best wishes,
A.D.Tejpal
------------
Sample subroutine - for merging survey results
T_data is source table. Results are appended to T_Result.
'=====================================
Sub P_PopulateResultTable()
' This subroutine merges the survey
' results for each person in source table
' T_Data and appends the outcome into
' destination table T_Result. Structure of T_Result
' is identical to that of T_Data
' T_Dummy is a single field single record table.
Dim Qst As String, Txt As String
Dim Fnm As String, Qst2 As String
Dim Fv As Variant
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim fd As Field
Dim tdf As TableDef
Dim db As DAO.Database
Const SourceTable As String = "T_Data"
Const DestnTable As String = "T_Result"
Const DummyTable As String = "T_Dummy"
' Comma separated string of all field names
' that do not directly carry survey response
Const ExemptFields As String = _
"ID,FirstName,LastName,Address"
Set db = DBEngine(0)(0)
' Clear destination table
db.Execute "DELETE * FROM " & _
DestnTable & ";", dbFailOnError
Qst = "SELECT FirstName, LastName, " & _
"Address FROM " & SourceTable & _
" GROUP BY FirstName, " & _
"LastName, Address;"
Set rst1 = db.OpenRecordset(Qst)
Set tdf = db.TableDefs(SourceTable)
Do Until rst1.EOF
Qst = "INSERT INTO " & DestnTable & _
" SELECT '" & _
rst1.Fields("FirstName") & "' AS " & _
"FirstName, '" & rst1.Fields("LastName") & _
"' AS LastName, '" & rst1.Fields("Address") & _
"' AS Address,"
For Each fd In tdf.Fields
Fnm = fd.Name
If InStr(ExemptFields, Fnm) > 0 Then
Else
Qst2 = "SELECT " & Fnm & _
" FROM " & SourceTable & _
" WHERE FirstName = '" & _
rst1.Fields("FirstName") & _
"' AND LastName = '" & _
rst1.Fields("LastName") & _
"' AND Address = '" & _
rst1.Fields("Address") & _
"' AND Len(" & Fnm & ") > 0;"
Set rst2 = db.OpenRecordset(Qst2)
If rst2.RecordCount > 0 Then
Qst = Qst & " '" & rst2.Fields(0) & _
"' AS " & Fnm & ","
Else
Qst = Qst & " Null AS " & Fnm & ","
End If
End If
Next
' Remove trailing comma
Qst = Left(Qst, Len(Qst) - 1)
Qst = Qst & " FROM " & DummyTable & ";"
' Append to destination table
db.Execute Qst, dbFailOnError
rst1.MoveNext
Loop
rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
Set fd = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub
'=====================================
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com