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@comcast.net>
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@yahoo.com <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@comcast.net <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]
Tidak ada komentar:
Posting Komentar