Jim
I got your sample and will look at it.
Bill
--- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@...> wrote:
>
> Bill
>
> I created a database with the table and I am uploading it to the files section
>
> Jim Wagner
> ________________________________
>
>
>
>
>
> ________________________________
> From: Jim Wagner <luvmymelody@...>
> To: MS_Access_Professionals@yahoogroups.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@...>
> To: MS_Access_Professionals@yahoogroups.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, 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
> > 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
> > 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, "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]
>
Tidak ada komentar:
Posting Komentar