"Unwind" a Database & create a Flat Table

[Max]

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

[Squirrel]

Hi Max,

Well that was fun! :-)

Three routines, tested them as public routines in a standard module, you may
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 empty.
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 any
ADO libs.

So in the Immediate Window use: ?DoPrintFlat to run the code.
(DoPrintFlat() calls the other two routines).

Public Function GetNextSubReaso n(rs As Recordset, init As Boolean) As String
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

GetNextSubReaso n = "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, " & _
"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 = '" &
_
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 record
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;"

Set rst = CurrentDb.OpenR ecordset(strSQL )
rst.MoveFirst
If Not rst.EOF Then
firstRec = True
While Not rst.EOF
fldName = GetNextSubReaso n(rst, firstRec)
InsertData rst, fldName
firstRec = False
rst.MoveNext
Wend
End If

rst.Close
DoPrintFlat = True

DoPrintFlat_exi t:
Exit Function

DoPrintFlat_err :
MsgBox Err.Number & vbCrLf & Err.Description
Resume DoPrintFlat_exi t

End Function

Goodnight. -Linda
"Max" <ab******@aol.c om> wrote in message
news:b4******** *************** **@posting.goog le.com...

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

[Squirrel]

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.SetWarnin gs 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.SetWarnin gs True

Once again, Goodnight. :-)

"Squirrel" <wi*****@covad. net> wrote in message
news:cc******** *************** ****@msgid.mega newsservers.com ...

Hi Max,

Well that was fun! :-)

Three routines, tested them as public routines in a standard module, you may 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 empty. 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 any ADO libs.

So in the Immediate Window use: ?DoPrintFlat to run the code.
(DoPrintFlat() calls the other two routines).

Public Function GetNextSubReaso n(rs As Recordset, init As Boolean) As String 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

GetNextSubReaso n = "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, " & _ "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 = '" & _
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 record 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;"

Set rst = CurrentDb.OpenR ecordset(strSQL )
rst.MoveFirst
If Not rst.EOF Then
firstRec = True
While Not rst.EOF
fldName = GetNextSubReaso n(rst, firstRec)
InsertData rst, fldName
firstRec = False
rst.MoveNext
Wend
End If

rst.Close
DoPrintFlat = True

DoPrintFlat_exi t:
Exit Function

DoPrintFlat_err :
MsgBox Err.Number & vbCrLf & Err.Description
Resume DoPrintFlat_exi t

End Function

Goodnight. -Linda
"Max" <ab******@aol.c om> wrote in message
news:b4******** *************** **@posting.goog le.com...

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

[Mike Preston]

On Tue, 21 Sep 2004 02:54:57 -0700, "Squirrel" <wi*****@covad. net>
wrote:

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.SetWarni ngs 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.SetWarni ngs True

Once again, Goodnight. :-)

"Squirrel" <wi*****@covad. net> wrote in message
news:cc******* *************** *****@msgid.meg anewsservers.co m...

Hi Max,

Well that was fun! :-)

Three routines, tested them as public routines in a standard module, you

may

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

empty.

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

any

ADO libs.

So in the Immediate Window use: ?DoPrintFlat to run the code.
(DoPrintFlat() calls the other two routines).

Public Function GetNextSubReaso n(rs As Recordset, init As Boolean) As

String

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

GetNextSubReaso n = "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, " &

_

"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 = '"

&

_
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

record

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;"
I think you want to add Reason to your ordering.
Set rst = CurrentDb.OpenR ecordset(strSQL )
rst.MoveFirst
If Not rst.EOF Then
firstRec = True
While Not rst.EOF
fldName = GetNextSubReaso n(rst, firstRec)
InsertData rst, fldName
firstRec = False
rst.MoveNext
Wend
End If

rst.Close
DoPrintFlat = True

DoPrintFlat_exi t:
Exit Function

DoPrintFlat_err :
MsgBox Err.Number & vbCrLf & Err.Description
Resume DoPrintFlat_exi t

End Function

Goodnight. -Linda
"Max" <ab******@aol.c om> wrote in message
news:b4******** *************** **@posting.goog le.com...

> 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

[Squirrel]

Mike,

strSQL = "SELECT Name, Address1, Address2, City, State, " & _
"Zip, Reason, [Sub-Reason] " & _
"FROM tblPrint ORDER BY Name, Address1, City, State, Zip;"

I think you want to add Reason to your ordering.

Yes, definitely. thx for catching that.

Linda