On Tue, 21 Sep 2004 02:54:57 -0700, "Squirrel" <wiseowl@covad.net>
wrote:
[color=blue]
>Max,
>
>This code puts up routine Access informational messages with each insert and
>append - I left it like this for testing purposes. Once it's working OK
>then add this code before and after this segment of code in the InsertData
>routine:
>
>Before the following code segment insert
>
>DoCmd.SetWarnings False
>
>If fldName = "Sub-Reason1" Then ' add new record
> DoCmd.RunSQL strSQL1
>Else ' update existing record
> DoCmd.RunSQL strSQL2
>End If
>
>And after the above code insert
>DoCmd.SetWarnings True
>
>Once again, Goodnight. :-)
>
>
>
>"Squirrel" <wiseowl@covad.net> wrote in message
>news:ccfd2$414ff8f1$44a50369$18289@msgid.meganews servers.com...[color=green]
>> Hi Max,
>>
>> Well that was fun! :-)
>>
>> Three routines, tested them as public routines in a standard module, you[/color]
>may[color=green]
>> want to call the code from a command button
>> in a form eventually. So just copy/paste these routines into a standard
>> module.
>>
>> tblPrint_FLAT must exist exactly as described in your posting and be[/color]
>empty.[color=green]
>> There should be data in tblPrint, assume no embedded single quotes in your
>> data. (Keeping this simple to start.)
>>
>> Set a reference to Microsoft DAO 3.6 Object Library, position it before[/color]
>any[color=green]
>> ADO libs.
>>
>> So in the Immediate Window use: ?DoPrintFlat to run the code.
>> (DoPrintFlat() calls the other two routines).
>>
>> Public Function GetNextSubReason(rs As Recordset, init As Boolean) As[/color]
>String[color=green]
>> Static Name, Address1, Address2, City, State, Zip, Reason
>> Static cnt As Integer
>>
>> If init Then
>> cnt = 1
>> Name = rs!Name
>> Address1 = rs!Address1
>> Address2 = rs!Address2
>> City = rs!City
>> State = rs!State
>> Zip = rs!Zip
>> Reason = rs!Reason
>> ElseIf Name <> rs!Name Or _
>> Address1 <> rs!Address1 Or _
>> City <> rs!City Or _
>> State <> rs!State Or _
>> Zip <> rs!Zip Or _
>> Reason <> rs!Reason Then
>> cnt = 1
>> Name = rs!Name
>> Address1 = rs!Address1
>> Address2 = rs!Address2
>> City = rs!City
>> State = rs!State
>> Zip = rs!Zip
>> Reason = rs!Reason
>> Else
>> cnt = cnt + 1
>> End If
>>
>> GetNextSubReason = "Sub-Reason" & cnt
>> End Function
>>
>>
>> Public Sub InsertData(rs As Recordset, fldName As String)
>> On Error GoTo InsertData_err
>>
>> Dim strSQL1 As String
>> Dim strSQL2 As String
>>
>> strSQL1 = "INSERT INTO tblprint_FLAT ( Name, Address1, Address2, City, " &[/color]
>_[color=green]
>> "State, Zip, Reason, [Sub-Reason1]) " & _
>> "values('" & rs!Name & "','" & rs!Address1 & "','" & rs!Address2 & _
>> "','" & rs!City & "','" & rs!State & "','" & rs!Zip & "','" & _
>> rs!Reason & "','" & rs![Sub-Reason] & "')"
>>
>> strSQL2 = "UPDATE tblprint_FLAT set [" & fldName & "] = '" & _
>> rs![Sub-Reason] & "' where Name = '" & rs!Name & "' and Address1 = '"[/color]
>&[color=green]
>> _
>> rs!Address1 & "' and City = '" & rs!City & "' and State = '" & _
>> rs!State & "' and Zip = '" & rs!Zip & "' and Reason = '" & rs!Reason &
>> "';"
>>
>> If fldName = "Sub-Reason1" Then ' add new record
>> DoCmd.RunSQL strSQL1
>> Else ' update existing[/color]
>record[color=green]
>> DoCmd.RunSQL strSQL2
>> End If
>>
>> InsertData_Exit:
>> Exit Sub
>>
>> InsertData_err:
>> MsgBox Err.Number & vbCrLf & Err.Description
>> Resume InsertData_Exit
>>
>> End Sub
>>
>>
>> Public Function DoPrintFlat() As Boolean
>> On Error GoTo DoPrintFlat_err
>>
>> Dim rst As Recordset
>> Dim strSQL As String
>> Dim firstRec As Boolean
>> Dim fldName As String
>>
>> DoPrintFlat = False
>>
>> strSQL = "SELECT Name, Address1, Address2, City, State, " & _
>> "Zip, Reason, [Sub-Reason] " & _
>> "FROM tblPrint ORDER BY Name, Address1, City, State, Zip;"[/color][/color]
I think you want to add Reason to your ordering.
[color=blue][color=green]
>> Set rst = CurrentDb.OpenRecordset(strSQL)
>> rst.MoveFirst
>> If Not rst.EOF Then
>> firstRec = True
>> While Not rst.EOF
>> fldName = GetNextSubReason(rst, firstRec)
>> InsertData rst, fldName
>> firstRec = False
>> rst.MoveNext
>> Wend
>> End If
>>
>> rst.Close
>> DoPrintFlat = True
>>
>> DoPrintFlat_exit:
>> Exit Function
>>
>> DoPrintFlat_err:
>> MsgBox Err.Number & vbCrLf & Err.Description
>> Resume DoPrintFlat_exit
>>
>> End Function
>>
>> Goodnight. -Linda
>>
>>
>> "Max" <absolete@aol.com> wrote in message
>> news:b42267b8.0409200957.cc55214@posting.google.co m...[color=darkred]
>> > Hello everyone, and thank you in advance for all of your help!
>> >
>> > I have a tblPrint table in Access 2002. Its structure is as follows:
>> >
>> > tblPrint
>> > ------------------
>> > Name, Text
>> > Address1, Text
>> > Address2, Text
>> > City, Text
>> > State, Text
>> > Zip, Text
>> > Reason, Memo
>> > Sub-Reason, Memo
>> >
>> > Several Records in this table may have Identical Recepient information
>> > as well as 'Reason' data. The only difference, would be in Sub-Reason
>> > field. For Example:
>> >
>> > Rec1:
>> > Name: Joe Shmoe
>> > Address1: 123 Anywhere Street
>> > Address2: Suite 123
>> > City: Anytown
>> > State: Anystate
>> > Zip: 12345-6789
>> > Reason: You must pay the following fine:
>> > Sub-Reason: Speeding - $130
>> >
>> > Rec2:
>> > Name: Joe Shmoe
>> > Address1: 123 Anywhere Street
>> > Address2: Suite 123
>> > City: Anytown
>> > State: Anystate
>> > Zip: 12345-6789
>> > Reason: You must pay the following fine:
>> > Sub-Reason: Tailgating - $70
>> >
>> > Without going into the discussions of poor database design, and how it
>> > should all be relational and not even 'memo' to begin with - I know
>> > that the maximum number of entries with same info but different
>> > Sub-Reasons is 8. I would like to write a query/function, which will
>> > 'unwrap' this table and make it look like so...
>> >
>> > tblPrint_FLAT
>> > ------------------
>> > Name, Text
>> > Address1, Text
>> > Address2, Text
>> > City, Text
>> > State, Text
>> > Zip, Text
>> > Reason, Memo
>> > Sub-Reason1, Memo
>> > Sub-Reason2, Memo
>> > Sub-Reason3, Memo
>> > Sub-Reason4, Memo
>> > Sub-Reason5, Memo
>> > Sub-Reason6, Memo
>> > Sub-Reason7, Memo
>> > Sub-Reason8, Memo
>> >
>> > This will obviously reduce the number of records, and will allow me to
>> > provide such a 'FLAT' and 'UNWOUND' table to a program for mail-merge.
>> >
>> > If anyone can suggest what to do, provide pointers on where I should
>> > look, or even how this is properly called, I would greatly appreciate
>> > it!!!
>> >
>> > Thanks!
>> > Max[/color]
>>
>>[/color]
>
>[/color]
