ACCESS FEED: Re: [MS_AccessPros] Re: Merge information into one record

Bil,

I have a laptop with 10 on it and I was able to copy your revised code into the
module. I made a copy of the database of course and then tried to open it in 07
but I still get the unrecognized format error. I tried to save the database
down, but I get an error on that also that. I thought 07 and 10 had the same
accdb format?

I do see how you did it. that is awesome.

Jim Wagner
________________________________

________________________________
From: Bill Mosca <wrmosca@comcast.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thu, June 2, 2011 1:27:12 PM
Subject: [MS_AccessPros] Re: Merge information into one record

Jim

I edited it in 2010. That might have screwed it up. Here is my revised code.

Just make a copy of your table (structure only) and name it TableTest.

Regards,
Bill

Public Function TestCode()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim strSQL As String
Dim aryTotals As Variant
Dim x As Integer
Dim strAcctNum As String

Set db = CurrentDb
strSQL = "SELECT * " _
& "FROM tblDisplaySubTotalAndTotalFinal"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Set rsNew = db.OpenRecordset("SELECT * " _
& "FROM TableTest")
With rs
Do While Not .EOF
' Get AcctNum
If IsNull(.Fields(0)) = False Then
strAcctNum = .Fields(0)
Else
'This is a totals row.
rsNew.AddNew
rsNew.Fields(0) = strAcctNum
rsNew.Fields(2) = .Fields(2)
rsNew.Fields(3) = .Fields(3)
rsNew.Fields(4) = .Fields(4)
rsNew.Fields(5) = .Fields(5)
rsNew.Fields(6) = .Fields(6)
rsNew.Fields(7) = .Fields(7)
rsNew.Update
End If
.MoveNext
Loop

End With

MsgBox "Data Loaded into Table Test", vbInformation

Set rsNew = Nothing
Set rs = Nothing
Set db = Nothing

End Function

--- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@...>
wrote:
>
> Bill,
> I extracted the database and tried to open it and I get an error that states
> that it is an unrecognizable format. I tried several times and the same error.

>
> I am using Windows 7 with Office 2007
>
> Jim Wagner
> ________________________________
>
>
>
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Thu, June 2, 2011 12:29:01 PM
> Subject: RE: [MS_AccessPros] Re: Merge information into one record
>
>
> Jim
>
> I changed my code after I saw that your table actually had separate fields
> instead of one field with commas.
>
> Iâ€™ve uploaded a file to your folder. All I did was make a copy of the
>original
>
> table and named it TableTest. I already ran the code so you will need to clear

> out TableTest and run the code again to see it work.
>
> It should give you an idea how it all works. I just used 2 recordsets. The
>first
>
> on gets the data. The second loads it into the new table.
>
> The trick was to use a loop of the first recordset. If the first field was not

> null we know that it has the acct number. I capture that into a variable and
> then move to the next record. That is the one with the amounts. I take the
> amounts and the variable to make a new record.
>
> Regards,
>
> Bill
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
> Sent: Wednesday, June 01, 2011 3:29 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: Merge information into one record
>
> Bill
>
> I created a database with the table and I am uploading it to the files section
>
> Jim Wagner
> ________________________________
>
> ________________________________
> From: Jim Wagner <luvmymelody@... <mailto:luvmymelody%40yahoo.com> >
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> Sent: Wed, June 1, 2011 3:16:39 PM
> Subject: Re: [MS_AccessPros] Re: Merge information into one record
>
> Bill
>
> It crashes on the line
>
> aryTotals = Split(.Fields(1), ",")
>
> Jim Wagner
> ________________________________
>
> ________________________________
> From: Bill Mosca <wrmosca@... <mailto:wrmosca%40comcast.net> >
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> Sent: Wed, June 1, 2011 1:31:23 PM
> Subject: [MS_AccessPros] Re: Merge information into one record
>
> Jim
>
> Table4 has 2 fields: the AcctNum (text) and AcctTotal (Decimal 18,2)
>
> Public Function TestCode()
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Dim rsNew As DAO.Recordset
> Dim strSQL As String
> Dim aryTotals As Variant
> Dim x As Integer
> Dim strAcctNum As String
>
> Set db = CurrentDb
> strSQL = "SELECT * " _
> & "FROM Table3"
> Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
> Set rsNew = db.OpenRecordset("SELECT * " _
> & "FROM Table4")
> With rs
> Do While Not .EOF
> ' Get AcctNum
> If IsNull(.Fields(0)) = False Then
> strAcctNum = .Fields(0)
> Else
> 'This is a totals row.
> aryTotals = Split(.Fields(1), ",")
> 'Skip the first element. It's the word "Subtotal".
> For x = 1 To UBound(aryTotals)
> With rsNew
> .AddNew
> .Fields(0) = strAcctNum
> .Fields(1) = aryTotals(x)
> .Update
> End With
> Next
> End If
> .MoveNext
> Loop
>
> End With
>
> Set rsNew = Nothing
> Set rs = Nothing
> Set db = Nothing
>
> End Function
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , Jim Wagner
> <luvmymelody@>
>
> wrote:
> >
> > Bill,
> >
> > I forgot to add a column.
> > Before the 768.59 column is a column with the word Subtotal in it. Is there a
>
> > way to get all of this data on one row?
> >
> > example
> > JA21001
> > Subtotal, 768.59,3.31, .00, 45.42, .00, 817.32
> >
> >
> >
> >
> > Jim Wagner
> > ________________________________
> >
> >
> >
> >
> >
> > ________________________________
> > From: Jim Wagner <luvmymelody@>
> > To: MS_Access_Professionals@yahoogroups.com
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> >
> > Sent: Wed, June 1, 2011 12:25:53 PM
> > Subject: Re: [MS_AccessPros] Re: Merge information into one record
> >
> >
> > Unfortunately the table is a download from an xml file from a website. We are
>
> > trying to create a report with just totals. The original has all the details

> >and
> >
> >
> > I was able to get it down to the totals for each account.
> >
> > Jim Wagner
> > ________________________________
> >
> > ________________________________
> > From: Bill Mosca <wrmosca@>
> > To: MS_Access_Professionals@yahoogroups.com
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> >
> > Sent: Wed, June 1, 2011 12:23:25 PM
> > Subject: [MS_AccessPros] Re: Merge information into one record
> >
> > Jim
> >
> > Are the records really like that? a record number in one row and then the
>next
>
>
>
> > row is a string of totals? That's not really a table.
> >
> > Regards,
> > Bill Mosca, Founder - MS_Access_Professionals
> > http://www.thatlldoit.com
> > Microsoft Office Access MVP
> > https://mvp.support.microsoft.com/profile/Bill.Mosca
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> ><mailto:MS_Access_Professionals%40yahoogroups.com> , "luvmymelody"
> ><luvmymelody@>
> >
> > wrote:
> > >
> > > Hello,
> > >
> > > I have a table that comes from a xml download. I have manipulated the table
>
> >to
> >
> >
> > >almost the way we need it. The data has the following records
> > >
> > > JA21001
> > > 768.59,3.31, .00, 45.42, .00, 817.32
> > > JC11001
> > > 159.35, .04,.00,10.50,.00,169.89
> > > JC11002
> > > 106.99,.12,.00,16.32,.00,12.43
> > >
> > > The J numbers are an account and the next line is the amounts charged (I
> >added
> >
> >
> > >a comma to separate the columns. Also for example the 768.59 field is not
> > >directly under the J number. It is the next column over on the next line.
> > >
> > >
> > > I am trying to get the totals for every account. Such as
> > > JA21001 768.59, 3.31, .00, 45.42, .00, 817.32
> > > JC11001 159.35, .04, .00,10.50,.00,169.89
> > > JC11002 106.99, .12, .00,16.32,.00,12.43
> > >
> > > Thank you
> > >
> > > Jim Wagner
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]