Using CSV or tab-delimited format to create report

H

huzefahashim

I am trying to create a format for a report where it uses data from a
query. The data is displayed as a list of records. This list only
includes one field (which is a 5-digit number). How is it possible to
display this data in either tab-delimited format or comma seperated?
Since the list is long, it leaves blank space and uses up a lot of
paper. Instead of being seperated by 'enter' if it were seperated by
commas, it would save a lot of space.

Thanks,
Zef.
 
A

Allen Browne

Sounds like you want a multi-column report.

1. Open the report in design view.

2. Set the Width of the detail section as narrow as you wish.
We will use 1" for this example.

3. Choose Page Setup on the File menu.

4. On the Columns tab, set these properties:
Number of Columns: 6
Column Spacing: 0
Column Size Same as Detail
Column Layout Across, then Down.
 
J

John Vinson

I am trying to create a format for a report where it uses data from a
query. The data is displayed as a list of records. This list only
includes one field (which is a 5-digit number). How is it possible to
display this data in either tab-delimited format or comma seperated?
Since the list is long, it leaves blank space and uses up a lot of
paper. Instead of being seperated by 'enter' if it were seperated by
commas, it would save a lot of space.

Thanks,
Zef.
Click to expand...

You can use a bit of VBA code to generate a text string consisting of
this list - separated by commas, blanks, or whatever you like. See

http://www.mvps.org/access/modules/mdl0004.htm

for sample code.

John W. Vinson[MVP]
 
H

huzefahashim

Thanks for your help. Although I failed to mention, there are a few
headers and footers in the report as well. So I cannot reduce the
horizontal size of the report. When you mentioned 'Set the Width of the
detail section as narrow as you wish.', there is no way to do as such
without setting the width of the entire report.

Also, the list is a sub-list of another data field. So, there are 3-4
such sub-lists in each report. I just want the sub-lists to be
tab-delimited or comma seperated.

Zef.
 
A

Allen Browne

You can leave the report width as you want, and place the header text into
the Page Header section. Then manually specify the column widths in the page
setup dialog.

Alternatively, you can write a function to concatenate the related records
into a single string. For an example, see:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm
 
H

huzefahashim

As I mentioned earlier, it's a sub-list.
An example would be:

Quantity List sub-List
123 A
A1
A2
A3

456 B
B1
B2
B3

The code will concatenate A1 to B3. I want to keep A and B seperate but
only tab-delimited A1, A2, A3 etc.

Your opinion, I cannot remove the quantity or the main list into the
page header. That will have to remain in the list header.


Thanks,
Zef
 
L

Larry Linson

Access has, IMNSHO, the very best relational reporting engine of any
"desktop" database, for reporting what _is there_. What you are asking is
that it report something that _isn't there_ in the form you want, so that
will require that we add functionality to create what you want from what you
actually have.

In Access terms, I wonder what would you call a "sub-list"? If it is a
related table, perhaps the following would work.

For a main Table named tblLists, containing a Number (Long Integer) Field
named Qty (as yours is) and a Text Field named List. A related Table is
named tblListItems with a TextField named List (which corresponds to the
List Field of tblLists) and a Text Field named ListItem (the items which are
concatenated to build your "sub-List").

A function, ConcatL:

Public Function ConcatL(pstrLName As String) As String
' Reads appropriate List from tblListItems, concatenates the List Item in a
' String and returns that String
Dim db As DAO.Database
Dim tb As DAO.Recordset
Dim strSQL As String
Dim strConcatList As String
Set db = CurrentDb()
strSQL = "SELECT * FROM tblListItems WHERE
  • = """ & pstrLName &
    """"
    Set tb = db.OpenRecordset(strSQL)
    If Not (tb.BOF And tb.EOF) Then
    tb.MoveFirst
    Do While Not tb.EOF
    If Len(strConcatList & "") < 1 Then
    strConcatList = tb("ListItem")
    Else
    strConcatList = strConcatList & ", " & tb("ListItem")
    End If
    tb.MoveNext
    Loop
    End If
    tb.Close
    Set tb = Nothing
    Set db = Nothing
    ConcatL = strConcatList
    Exit_Proc:
    Exit Function

    A Query built in the Query Builder with only tblLists as a data source,
    Fields Qty and Lists brought down into the grid, and a calculated Column
    sub-List: ConcatL(
    • ), whose SQL is:

      SELECT tblLists.Qty, tblLists.List, ConcatL(
      • ) AS [sub-List]
        FROM tblLists;

        Returns what you desire as Output. If you do not find it is trivial to
        replace the table and column names with your own and create the report -- I
        expect it will be trivial -- please post back with the specific problems you
        are having.

        Larry Linson
        Microsoft Access MVP
 
H

huzefahashim

To clarify; the "sub-list" is not a related table. I am only using one
table for the entire report. Also, "Quantity" is generated by a formula
at run-time. So only "List" and "sub-List" are picked from a query. The
query is generated from the table since only a certain list needs to be
used, which match the criteria.

The "sub-list" is a list of records which have the same data in the
"List" field. As in my previous example, the ones which have 'A' in the
"List" field, are grouped together. Then A1, A2, A3 are listed since
all of them have 'A' in the "List" field.

I hope this helps to understand.

Thanks again for your help.
Zef.

Larry said:
Access has, IMNSHO, the very best relational reporting engine of any
"desktop" database, for reporting what _is there_. What you are asking is
that it report something that _isn't there_ in the form you want, so that
will require that we add functionality to create what you want from what you
actually have.

In Access terms, I wonder what would you call a "sub-list"? If it is a
related table, perhaps the following would work.

For a main Table named tblLists, containing a Number (Long Integer) Field
named Qty (as yours is) and a Text Field named List. A related Table is
named tblListItems with a TextField named List (which corresponds to the
List Field of tblLists) and a Text Field named ListItem (the items which are
concatenated to build your "sub-List").

A function, ConcatL:

Public Function ConcatL(pstrLName As String) As String
' Reads appropriate List from tblListItems, concatenates the List Item in a
' String and returns that String
Dim db As DAO.Database
Dim tb As DAO.Recordset
Dim strSQL As String
Dim strConcatList As String
Set db = CurrentDb()
strSQL = "SELECT * FROM tblListItems WHERE
  • = """ & pstrLName &
    """"
    Set tb = db.OpenRecordset(strSQL)
    If Not (tb.BOF And tb.EOF) Then
    tb.MoveFirst
    Do While Not tb.EOF
    If Len(strConcatList & "") < 1 Then
    strConcatList = tb("ListItem")
    Else
    strConcatList = strConcatList & ", " & tb("ListItem")
    End If
    tb.MoveNext
    Loop
    End If
    tb.Close
    Set tb = Nothing
    Set db = Nothing
    ConcatL = strConcatList
    Exit_Proc:
    Exit Function

    A Query built in the Query Builder with only tblLists as a data source,
    Fields Qty and Lists brought down into the grid, and a calculated Column
    sub-List: ConcatL(
    • ), whose SQL is:

      SELECT tblLists.Qty, tblLists.List, ConcatL(
      • ) AS [sub-List]
        FROM tblLists;

        Returns what you desire as Output. If you do not find it is trivial to
        replace the table and column names with your own and create the report -- I
        expect it will be trivial -- please post back with the specific problems you
        are having.

        Larry Linson
        Microsoft Access MVP



        As I mentioned earlier, it's a sub-list.
        An example would be:

        Quantity List sub-List
        123 A
        A1
        A2
        A3

        456 B
        B1
        B2
        B3

        The code will concatenate A1 to B3. I want to keep A and B seperate but
        only tab-delimited A1, A2, A3 etc.

        Your opinion, I cannot remove the quantity or the main list into the
        page header. That will have to remain in the list header.


        Thanks,
        Zef
        Click to expand...
Click to expand...
 
L

Larry Linson

Before I waste any more time on erroneous assumptions, please clarify:

1. What is the table name?

2. What are names of all the Fields in the Table, and the type of each?

3. From what information is Quantity calculated?

Larry Linson
Microsoft Access MVP
 
H

huzefahashim

The table name is SSGC 2006. Although, I fail to figure out how that
would make a difference in a report. As for the names of all the
Fields, there are numerous fields! But only two are used for the
report. The report is generated from a query, which has only one
condition.
The names of the fields which are used in the report are Emp Num and
Unit. Emp Num is an integer field while Unit is a Text field.
Many employees belong to the same unit. So as previously, the main list
is Unit, while the 'sub-list' is Emp Num. For example, Unit A could
have 20 employees whose numbers are generated in the report. These need
to be categorized together. And there can be many units in one report.
The Quantity is calculated from Emp Num. The employee numbers in a
particular group (multiplied by 3) is the Quantity. And then there is a
total quantity at the end of the report.
Any more questions?
Thanks for the help.
Zef.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question on exporting table data. 2
Importing Delimited Files 3
csv files 1
CSV Tab Delimited 4
TransferText using wrong format 0
Extracting Emails f 1
Comma Delimited 2
Query to search for data all in caps 5

Top