Importing Vertically Formatted Records

  • Thread starter Thread starter Zardoz
  • Start date Start date
Z

Zardoz

I dont know why anyone in their right mind would format records this
way... but here is what I've got. It looks similar to an *.iif file, I
figure; kind of laid out like so:

accountid1 transactioncodexyz transactionamount
transactioncodexyz transactionamount
transactioncodexyz transactionamount
accountid2 transactioncodexyz transactionamount
transactioncodexyz transactionamount


and so on...

and this is much broken down from what I had before...

What I need to do is associate each of the following transaction codes
with the preceding account ID.

Unfortunately the access wizard never conceived of this situation,
frankly, he never conceives much of anything...

is there a way I can construct some kind of loop code to scan the
Account ID fields and remember the first ID it encounters as a string
and paste it into the following blank fields until it hits the next ID,
where it then remembers that string and pastes it until the next and so
on?

I imagine this is possible, only Im completey VB illiterate right now,
though Im desperately trying to learn...

Id appreciate your help, cuz Im stuck!!
 
Is this an Access table, or an external file?
If it is external, what format is it in?

This can be done. Here is an example that assumes it is an Access table:

Dim rst As Recordset
Dim strAcctID

Set rst = Currentdb.OpenRecordset("TableName")
If rst.RecordCount > 0 Then
rst.MoveFirst
rst.MoveLast
Else
MsgBox "No Records To Process"
Exit Sub
End If

With rst
strAcctId = !accountid1
Do While Not .EOF
If Len(Trim(Nz(!accountid1,""))) = 0 Then
.Edit
!accountid1 = strAcctId
.Update
Else
strAcctId = !accountid1
End If
.MoveNext
Loop
.Close
End With
Set rst = Nothing
 
This looks pretty good! Ill try it out today; will get me past a
critical hump in what Im doing. I really appreciate it.

The original file is a .csv Im getting from a credit reporting bureau.
My guess is that it is output from some form of accounting program
because it resembles an iif file as far as I can tell.

Its ridiculous though. I've looked at the internals of quickbooks and
it looks pretty much like a relational database to me. Why they chose
to relay external data like this I dont understand.
 
I tried it, but I dont think I implemented it correctly. Am I supposed
to replace !accountid with the fieldname containing the account id's?]

I pasted a sample csv straight from the table. The items of concern are
"SUBCODE" which contains the account ID, "PRODUCT" which contains the
transaction code and "TAX" which is... tax.

Im trying to associate SUBCODE with each subsequent PRODUCT in order
to extract the TAX so that I can sum the tax for the total tax charged
per billing period for each account.

Im pretty sure what you gave me before does this, but I am a dufus with
VB. Ill keep trying with that sample, maybe Ill get it right. But if
you have a fast answer, I will buy you pizza and beer!!!

Your help is much appreciated.

Phares

"ID","INVOICE.","INV DATE","PRMB","SUBCODE","PRODUCT","REPORT
TYPE","VOLUME","AMOUNT","TAX","CATEGORY","SUBTOTAL",""INVOICE TOTAL""
1,,,"TRSK","1962039","PPC0001","CPU-TO-CPU","00000086","+000098.90","+000008.03",,,
2,,,,,"PPC0002","CPU-TO-CPU NO
RECORD","00000004","+000004.60","+000000.37",,,
3,,,,,"PPC1001","CPU-CPU FIRST
DUAL","00000006","+000006.90","+000000.57",,,
4,,,,,"PPC2001","CPU-CPU SECOND
DUAL","00000005","+000005.75","+000000.47",,,
5,,,,,"PPC2002","CPU-CPU SECOND DUAL NO
RECORD","00000001","+000001.15","+000000.09",,,
6,,,,,"0000RV1","EXP/FAIR ISAAC AUTO
II","00000089","+000013.35","+000001.10",,,
7,,,,,"136000M","FACTA COST
RECOVERY","00000001","+000009.99","+000000.79",,,
8,,,"TRSK","1962165","PPC0001","CPU-TO-CPU","00000013","+000014.95","+000001.24",,,
9,,,,,"PPC0002","CPU-TO-CPU NO
RECORD","00000001","+000001.15","+000000.09",,,
10,,,,,"0000RV1","EXP/FAIR ISAAC AUTO
II","00000013","+000001.95","+000000.17",,,
11,,,,,"136000M","FACTA COST
RECOVERY","00000001","+000001.35","+000000.11",,,
12,,,"TRSK","1964259","PPC0001","CPU-TO-CPU","00000155","+000178.25","+000007.14",,,
13,,,,,"PPC0002","CPU-TO-CPU NO
RECORD","00000012","+000013.80","+000000.56",,,
14,,,,,"0000RV1","EXP/FAIR ISAAC AUTO
II","00000120","+000018.00","+000000.72",,,
15,,,,,"0000YS1","PROFILE
SUMMARY","00000139","+000011.12","+000000.44",,,
16,,,,,"136000M","FACTA COST
RECOVERY","00000001","+000016.59","+000000.66",,,
17,,,"TRSK","1966430","PPC0001","CPU-TO-CPU","00000001","+000001.15","+000000.09",,,
18,,,,,"0000FS1","FRAUD SHIELD -
HIT","00000001","+000000.25","+000000.02",,,
19,,,,,"0000RV1","EXP/FAIR ISAAC AUTO
II","00000001","+000000.15","+000000.02",,,
20,,,"TRSK","1966497","PPT0001","CREDIT PROFILE
TTY","00000283","+000325.45","+000000.09",,,
21,,,,,"0000RV1","EXP/FAIR ISAAC AUTO
II","00000360","+000054.00","+000000.01",,,
22,,,,,"136000M","FACTA COST
RECOVERY","00000001","+000041.12","+000000.01",,,
23,,,"TRSK","2960378","PPT0001","CREDIT PROFILE
TTY","00000246","+000282.90","+000014.16",,,
 
So let me see if I have this right. The SUBCODE column in the first row (ID
= 1) is 1962039. Rows 2 - 7 SUBCODE is blank. You want rows 2 - 7 to be
1962039 like the first row above them that has a value. Them in row 8,
SUBCODE is 1962165, which should be copied down to rows 9 - 11, where SUBCODE
has a value, and on and on. Is this what you are trying to do?

If so, the origina code I sent will do the trick by changing !accountid with
!SUBCODE

I'll have Pepperoni and Sausage and a Butt Face Ale from the Big Horn
Brewery in Portland, Oregon :)
 
Hmm, in leu of **** faced?? Sounds like a fine brew! Portland rocks,
btw. Just tell me where to send it and Ill have your favorite
establishment bring it by. Well I tried the code and it goes and does
something but I see no result. I referenced it through a command
button. It looks like this:

Private Sub Command10_Click()

Dim rst As Recordset
Dim strAcctID


Set rst = CurrentDb.OpenRecordset("QBTAXES")
If rst.RecordCount > 0 Then
rst.MoveFirst
rst.MoveLast
Else
MsgBox "No Records To Process"
Exit Sub
End If


With rst
strAcctID = !SUBCODE
Do While Not .EOF
If Len(Trim(Nz(!SUBCODE, ""))) = 0 Then
.Edit
!SUBCODE = strAcctID
.Update
Else
strAcctID = !SUBCODE
End If
.MoveNext
Loop
.Close
End With
Set rst = Nothing

End Sub
 
Ah, ok. It had something to do with having the recordset options set
with a variable. I pasted in the full string
CurrentDb.OpenRecordset("QBTAXES") in lieu of the rst and presto!
Genius! Seriously, let me know where to send it and Ill have beer and
pizza for you by this weekend!
 
Thanks for your kind offer, but it is not necessary. Someday you will be
able to assist me or someone else on this site. That is reward enough.

I would guess by your name you are from the planet Tralfamador
 
Briefly looking at your post I had an idea for you. Can you paste the file
into Excel? If formatted the way you say, one in excell you may be able to
copy the spreadsheet and then paste it into a new sheet, but choos "paste
special" and select "Transpose Fields" which will tuen the thing around.

May not be what you are looking for.
 
Back
Top