Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using CSV or tab-delimited format to create report

Discussion in 'Information Technology' started by huzefahashim@gmail.com, Jul 28, 2006.

  1. huzefahashim@gmail.com

    huzefahashim@gmail.com
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <huzefahashim@gmail.com> wrote in message
    news:1151917256.127427.3910@h44g2000cwa.googlegroups.com...
    >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.
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 3 Jul 2006 02:00:56 -0700, huzefahashim@gmail.com wrote:

    >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.


    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]
     
  5. huzefahashim@gmail.com

    huzefahashim@gmail.com
    Expand Collapse
    Guest

    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.


    Allen Browne wrote:
    > 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.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > <huzefahashim@gmail.com> wrote in message
    > news:1151917256.127427.3910@h44g2000cwa.googlegroups.com...
    > >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.
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <huzefahashim@gmail.com> wrote in message
    news:1151997744.088189.257980@h44g2000cwa.googlegroups.com...
    > 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.
    >
    >
    > Allen Browne wrote:
    >> 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.
    >>
    >> <huzefahashim@gmail.com> wrote in message
    >> news:1151917256.127427.3910@h44g2000cwa.googlegroups.com...
    >> >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.
     
  7. huzefahashim@gmail.com

    huzefahashim@gmail.com
    Expand Collapse
    Guest

    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

    Allen Browne wrote:
    > 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
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > <huzefahashim@gmail.com> wrote in message
    > news:1151997744.088189.257980@h44g2000cwa.googlegroups.com...
    > > 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.
    > >
    > >
    > > Allen Browne wrote:
    > >> 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.
    > >>
    > >> <huzefahashim@gmail.com> wrote in message
    > >> news:1151917256.127427.3910@h44g2000cwa.googlegroups.com...
    > >> >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.
     
  8. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    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



          <huzefahashim@gmail.com> wrote in message
          news:1152001803.698988.184830@j8g2000cwa.googlegroups.com...
          > 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
          >
          > Allen Browne wrote:
          >> 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
          >>
          >> --
          >> Allen Browne - Microsoft MVP. Perth, Western Australia.
          >> Tips for Access users - http://allenbrowne.com/tips.html
          >> Reply to group, rather than allenbrowne at mvps dot org.
          >>
          >> <huzefahashim@gmail.com> wrote in message
          >> news:1151997744.088189.257980@h44g2000cwa.googlegroups.com...
          >> > 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.
          >> >
          >> >
          >> > Allen Browne wrote:
          >> >> 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.
          >> >>
          >> >> <huzefahashim@gmail.com> wrote in message
          >> >> news:1151917256.127427.3910@h44g2000cwa.googlegroups.com...
          >> >> >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.

          >
     
  9. huzefahashim@gmail.com

    huzefahashim@gmail.com
    Expand Collapse
    Guest

    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 Linson wrote:
    > 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
          >
          >
          >
          > <huzefahashim@gmail.com> wrote in message
          > news:1152001803.698988.184830@j8g2000cwa.googlegroups.com...
          > > 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
          > >
          > > Allen Browne wrote:
          > >> 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
          > >>
          > >> --
          > >> Allen Browne - Microsoft MVP. Perth, Western Australia.
          > >> Tips for Access users - http://allenbrowne.com/tips.html
          > >> Reply to group, rather than allenbrowne at mvps dot org.
          > >>
          > >> <huzefahashim@gmail.com> wrote in message
          > >> news:1151997744.088189.257980@h44g2000cwa.googlegroups.com...
          > >> > 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.
          > >> >
          > >> >
          > >> > Allen Browne wrote:
          > >> >> 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.
          > >> >>
          > >> >> <huzefahashim@gmail.com> wrote in message
          > >> >> news:1151917256.127427.3910@h44g2000cwa.googlegroups.com...
          > >> >> >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.

          > >
     
  10. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    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
     
  11. huzefahashim@gmail.com

    huzefahashim@gmail.com
    Expand Collapse
    Guest

    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.

    Larry Linson wrote:
    > 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
     

Share This Page