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

Kamis, 02 Juni 2011

RE: [MS_AccessPros] Re: Merge information into one record

Jim,
You stated:
========================
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
========================
This confused the heck out of most of us since you didn't indicate the commas separated value from multiple fields. Questions like this should always include the siginificant table and field names.

Have you considered normalizing your table so the breakdowns each create a record in a related table rather than multiple fields? Or, is it too late for that?

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: luvmymelody@yahoo.com
Date: Wed, 1 Jun 2011 15:20:45 -0700
Subject: Re: [MS_AccessPros] Re: Merge information into one record

Bill,
Here is my table design with sample data. I wish we could get it in another
format. But this is what I get.

Breakdown_TotalCharges_CostCenter = JA 1001
Breakdown_Page_Number = Subtotal
Breakdown_Accs_Chrgs = $768.59
Breakdown_Usage_Chrgs = $3.31
Breakdown_Equip_Chrgs = $.00
Breakdown_Surchrgs = $45.42
Breakdown_Taxes_Fees = $.00
Breakdown_Total_Chrgs = $817.32

Jim Wagner
________________________________

________________________________
From: Bill Mosca <wrmosca@comcast.net>
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]
>