Stagger Records on Output with VBA

[sstump]

I have an Access Database that I need to stagger the two tables on output. Both tables have the same number of records, but different data and I need to show line 1 of table A, then on a new line I need to show line 1 from table B, then on a new line line 2 from table A and then on a new line line 2 from table B.

Example:
Table A
A 1 TEST-A FILE-A
A 2 TEST-A FILE-A

Table B
B 1 TEST-B FILE-B
B 2 TEST-B FILE-B

I need to use Transfertext to get it to look like this
A 1 TEST-A FILE-A
B 1 TEST-B FILE-B
A 2 TEST-A FILE-A
B 2 TEST-B FILE-B

Any help would be greatly appreciated!
Stump

 

[JoeAtWork]

If the data actually looks like your sample, you just need to UNION your two tables and sort by whatever field that second column is.

 

[lameid]

If they really have the same columns as you have shown...

Make a query (query1 for post purposes...)

Select *
From [Table A]

Union All

Select *
From [Table B]

Then make another query...

Select *
From Query1
Order By Field2, Field1

Note Field2 has the order sequence and Field1 has A or B in it.

The key thing here is using a Union query.

 

[sstump]

Well they do not have the same columns...they are different sets of data and column headers. Also I need the Table B to be on a new line.

Also this is with 6000 lines on each table.

Thanks,
Stump

 

[HarleyQuinn]

Where are you outputting this to? You need to see them on a new line of what?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[sstump]

I'm outputting it to a *.txt file to be run as a script.

 

[HarleyQuinn]

OK, easiest way I can see. Two recordsets and loop through them appending a record a time from each into the text file.

It's won't necessarily be fast though but i'd be imaging that the probable size of your dataset would exclude storing them as a string whilst looping and then doing just one write at the end...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[lameid]

You could also go with the Union if you are willing to make your select statements in the query have the same number of columns. Basically you need a query for each table that outputs the same columns. Those 2 select statements can be stacked with Union All like my example.

Assuming you can join the two tables appropriately you could also stack the data in a report and export the report to text. This is probably easier than recordsets and the file but perhaps less ideal. The Recordsets and writing to a file definitely gives you the most flexibility.

 

[sstump]

Adding fields to the report with less columns wouldn't work cause the specs to output each table are different(line length, filler spaces, etc...).

At this point I'm looking at running both tables into one line and outputing it to text, then editing the file with Notepad++ to insert a New Line (via Ctrl M) in Search/Replace at the point of the break between the tables. I tested it out on a smaller batch and it worked.

I'll try it out as a Report and see if that works (each line from Table A does coorelate to a line on Table B) so I might be able to stack em.

If anyones got any better ideas then this please let me know...this won't be a regular script I'll have to run but I'd like to have a permanent process in place if it ever comes up again.

 

[PWise]

Perhaps if we can see som real sample data we can help

 

[HarleyQuinn]

you could always use my suggestion...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[sstump]

HarleyQuinn...How would I go about doing that. What would the code look like in this case?

 

[PWise]

What i was thinking was along the lines of lameid's suggestion with a twist

Select dt.Export from (
Select Fielda +space(x)+fieldb.... as Export, 'Tablea' as tablename, fieldx as sortfiled
From [Table A]

Union All

Select Fielda +space(x)+fieldb.... as Export, 'Tablea' as tablename, fieldx as sortfiled
From [Table B]
 )DT
order by fieldx ,tablenmae

this query gives you both tables formated and sorted the way you want

 

[ck1999]

I think harleyquinn was speaking of something similar to

Sub help()
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim myfile

Set db = CurrentDb
Set rs = db.OpenRecordset("tblsample", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tblsample2", dbOpenDynaset)
rs.MoveFirst
rs2.MoveFirst
myfile = "c:\" & "VBAoutput.txt"
ftext = FreeFile
Open myfile For Output As #ftext

Do While Not rs.EOF
   ' MsgBox rs.Fields("alpha")
    Print #ftext, rs!alpha & "    " & rs!num & "   " & rs!file & "   " & rs!test
    Print #ftext, rs2!alpha & "    " & rs2!num & "   " & rs2!file & "   " & rs2!test
    rs.MoveNext
    rs2.MoveNext
Loop

Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing
Close ftext

End Sub

Notice that I made up tables and fields you will have to change
And I made up spacing not sure on your alignment ideas
if you want headers add a "write" line above the loop.

Also I based this on both tables having equal recordsets if this is not true then you would have to change the loop
This code overwrites my file! not adds to it!

ck1999