Array?For Each? Transpose.....

[dtaylor]

Hello All,
Is this possible?......

I have a table that is grouped by customer number and contract number. For each customer there can be from 0 - 10 contracts -so because of the contracts there are multiple customer number entries.

What I am trying to do is create a table that has a single customer line and each of the grouped contracts as a field on the same line - contract field 1, 2 , 3 ect???

An array? For Each? Is this too much to ask? Any suggestions, links, tidbits to get on the right direction....

Thanks

Dan

 

[dtaylor]

This message was edited by dtaylor on 2003-02-03 18:04

 

[dtaylor]

This message was edited by dtaylor on 2003-02-03 18:04

 

[dtaylor]

This message was edited by dtaylor on 2003-02-03 18:06

 

[dtaylor]

This message was edited by dtaylor on 2003-02-03 18:05

 

[dtaylor]

This message was edited by dtaylor on 2003-02-03 18:06

 

[dtaylor]

This message was edited by dtaylor on 2003-02-03 19:05

 

[dtaylor]

Hi Russell,
Not a problem , I appreciate the assistance. No I haven't solved it, been trying for about 6 monts

Customer and Contract fields will both be text format.

Thanks

Dan


Edit - MAN - when i hit 'submit' my pc went south - when i logged back on i saw all these posts! Sorry guys - not intentional...
This message was edited by dtaylor on 2003-02-03 19:06

 

[Russell Hauf]

You're out of control! :eek
I'm working on this now and will have something for you today.

Oh, What version of Access are you using?

Thank you,

Russell

 

[dtaylor]

Hi Russell,
Yea just a little out of control !

I am using 2000 but code most of my projects in DAO.

Either works for me, ADODB just means hitting the reference

Hey thanks for all your help!

Dan

 

[Russell Hauf]

Ok, I'm going to post an ADO solution, since that's what you should be getting used to anyway!

One thing - before running this, set the primary key of the new table that you made (with the unique Customers) to your Customer field. Just change the 4 constants at the top of this code to your table & field names. My tables were tblCustomers, tblCustomersNew, and my fields were Customer and Contract in my "old" table, and Customer and Contract1-Contract10 in my "new" table.<pre>Option Compare Database
Option Explicit

Private Const cstrTblOld As String = "tblCustomers"
Private Const cstrTblNew As String = "tblCustomersNew"
Private Const cstrFldCustomer As String = "Customer"
Private Const cstrFldContract As String = "Contract"

Sub TestRstSortOrder()
Dim rstOld As ADODB.Recordset
Dim rstNew As ADODB.Recordset
Dim lngI As Long

Set rstOld = New ADODB.Recordset
Set rstNew = New ADODB.Recordset

rstNew.CursorLocation = adUseServer


rstOld.Open cstrTblOld, CurrentProject.Connection, adOpenForwardOnly
rstNew.Open cstrTblNew, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect


If rstNew.Supports(adSeek) And rstNew.Supports(adIndex) Then
rstNew.Index = "PrimaryKey"
GoTo SeekYes
Else
GoTo NonSeek
End If

SeekYes:

Do While Not rstOld.EOF
rstNew.Seek rstOld.Fields(cstrFldCustomer)

For lngI = 1 To 10
If IsNull(rstNew.Fields(cstrFldContract & lngI)) Then
rstNew.Fields(cstrFldContract & lngI) = rstOld.Fields(cstrFldContract)
Exit For
End If
Next lngI

rstOld.MoveNext
Loop

GoTo ExitHere

NonSeek:

Do While Not rstOld.EOF
rstNew.MoveFirst
Do While Not rstNew.EOF
If rstNew.Fields(cstrFldCustomer) = rstOld.Fields(cstrFldCustomer) Then
For lngI = 1 To 10
If IsNull(rstNew.Fields(cstrFldContract & lngI)) Then
rstNew.Fields(cstrFldContract & lngI) = rstOld.Fields(cstrFldContract)
Exit Do
End If
Next lngI
End If
rstNew.MoveNext
Loop
rstOld.MoveNext
Loop

ExitHere:
On Error Resume Next
rstOld.Close
rstNew.Update
rstNew.Close
Set rstOld = Nothing
Set rstNew = Nothing

HandleErr:
Select Case Err.Number
Case Else
MsgBox Err.Description, vbCritical, "Error " & Err.Number
Resume ExitHere
End Select


End Sub</pre>

Just paste the code into a new module, and you should be ready to roll.

HTH,

Russell
This message was edited by Russell Hauf on 2003-02-04 14:09