Welcome to SPN

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

Sign Up Now!

creating a flat file (txt or excel) based on a query with one to many records

Discussion in 'Information Technology' started by tjay, Jul 28, 2006.

  1. tjay

    tjay
    Expand Collapse
    Guest

    I am attempting to create a flat file that I need to send to a service
    processor.
    Problem
    The file I am creating comes from a query with a one to many join. This
    results in several records being reproduced for each entry in the many
    table.
    I need to append the unique information from the duplicate rows onto the end
    of the initial record. In effect creating a record that can be exported in a
    flat file as a single row.

    I have scoured the access help and online resources and cannot find a
    solution.

    If someone can point me to a resource or example I can take it from there.

    Thanks much for your help. First time poster.
     
  2. Loading...

    Similar Threads Forum Date
    Canada Ottawa weighs creating ambassador for religious freedom Breaking News Oct 4, 2011
    India SGPC Accused of Creating a Row Breaking News Jan 20, 2011
    Pacific International Gathering of Young Sikhs Focuses on Creating Good Citizens Breaking News Jan 8, 2011
    Are We Creating New Devtas? Sikh Sikhi Sikhism Aug 21, 2009
    Sikh News Sharif warns against creating instability in Punjab (Express India) Breaking News Sep 19, 2008

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 21 May 2006 14:59:52 -0700, "tjay" <timjones@cyprex.biz>
    wrote:

    >I need to append the unique information from the duplicate rows onto the end
    >of the initial record. In effect creating a record that can be exported in a
    >flat file as a single row.


    You'll need some VBA code. For an example (using one field from the
    "many" table, but you can adapt it) see

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

    John W. Vinson[MVP]
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    RE: creating a flat file (txt or excel) based on a query with one to m

    Hello tjay,

    First, welcome to this group as a first time poster. Here is a generic
    example for the sample Northwind.mdb database. Try it out first in Northwind,
    and then see if you can get it to work in your database.

    Create a new module. Add Option Explicit as the second line of code to your
    module, if it is not already there. Here is the reason why you want this, and
    instructions on how to configure your copy of Access to always include this
    in all new modules:

    Always Use Option Explicit
    http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

    Click on Tools > References. You need to verify that you have a reference
    set to the "Microsoft DAO 3.6 Object Library" (or version 3.51 if you are
    using Access 97). If you do not see this reference selected, then scroll down
    the list until you find it, and place a check to select it. Then click on OK
    to back out of the references dialog.

    Select the following code and copy it (Ctrl C). Paste it into your new
    module, just below the line that reads Option Explicit:

    Function ConcatenateRecords(lngCategoryID As Long) As String
    On Error GoTo ProcError

    ' Input: Primary key of record in Categories table
    'Returns: String containing all the related product names.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strOut As String
    Dim lngLen As Long
    Const conSEP = "; "

    Set db = CurrentDb
    strSQL = "SELECT ProductName " _
    & "FROM Products " _
    & "WHERE CategoryID =" & lngCategoryID

    Set rs = db.OpenRecordset(strSQL)

    With rs
    Do While Not (.BOF Or .EOF) = True
    strOut = strOut & ![ProductName] & conSEP
    .MoveNext
    Loop
    End With

    lngLen = Len(strOut) - Len(conSEP)

    If lngLen > 0 Then
    ConcatenateRecords = Left$(strOut, lngLen)
    Else
    ConcatenateRecords = ""
    End If

    ExitProc:
    On Error Resume Next
    rs.Close: Set rs = Nothing
    db.Close: Set db = Nothing
    Exit Function
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
    "Error in ConcatenateRecords function..."
    Resume ExitProc
    End Function

    Click on Debug > Compile ProjectName. Hopefully, your code will compile
    without any errors. VBA code is compiled when an attempt to repeat this
    operation reveals that the option is grayed out (ie. unavailable).

    Save your new module as "basConcatentateRecords" (without the quotes).

    Create a new query. Dismiss the Add Tables dialog without adding any tables.
    In query design view, click on View > SQL View. You should see the word
    SELECT highlighted. Copy the following SQL statement (Ctrl C) and paste it
    into the SQL view (Ctrl V), replacing the SELECT keyword:

    SELECT Categories.CategoryName,
    ConcatenateRecords([CategoryID]) AS ProductName
    FROM Categories
    ORDER BY Categories.CategoryName;

    You can then switch back to the more familiar design view, if you wish, by
    clicking on View > Design View. Run the query.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "tjay" wrote:

    > I am attempting to create a flat file that I need to send to a service
    > processor.
    > Problem
    > The file I am creating comes from a query with a one to many join. This
    > results in several records being reproduced for each entry in the many
    > table.
    > I need to append the unique information from the duplicate rows onto the end
    > of the initial record. In effect creating a record that can be exported in a
    > flat file as a single row.
    >
    > I have scoured the access help and online resources and cannot find a
    > solution.
    >
    > If someone can point me to a resource or example I can take it from there.
    >
    > Thanks much for your help. First time poster.
     
  5. tjay

    tjay
    Expand Collapse
    Guest

    Thanks guys I will give it a go


    "tjay" <timjones@cyprex.biz> wrote in message
    news:SsKdnb4toOBAf-3ZnZ2dneKdnZydnZ2d@comcast.com...
    >I am attempting to create a flat file that I need to send to a service
    >processor.
    > Problem
    > The file I am creating comes from a query with a one to many join. This
    > results in several records being reproduced for each entry in the many
    > table.
    > I need to append the unique information from the duplicate rows onto the
    > end of the initial record. In effect creating a record that can be
    > exported in a flat file as a single row.
    >
    > I have scoured the access help and online resources and cannot find a
    > solution.
    >
    > If someone can point me to a resource or example I can take it from there.
    >
    > Thanks much for your help. First time poster.
    >
     
  6. tjay

    tjay
    Expand Collapse
    Guest

    Ok
    Both examples worked and it gave me a great deal of insight but that still
    wont get me there. I most likely did not explain it well.
    In the attached spreadsheet Query 2 tab has the output of my test query
    shaded in yellow. the cells to the right are where I accomplished what I
    needed to do with a series of functions.

    The desired result rows are lined top and bottom.

    You can also see a desired output on the other tab

    Am I wishing for something that dont exist?


    "tjay" <timjones@cyprex.biz> wrote in message
    news:SsKdnb4toOBAf-3ZnZ2dneKdnZydnZ2d@comcast.com...
    >I am attempting to create a flat file that I need to send to a service
    > processor.
    > Problem
    > The file I am creating comes from a query with a one to many join. This
    > results in several records being reproduced for each entry in the many
    > table.
    > I need to append the unique information from the duplicate rows onto the
    > end
    > of the initial record. In effect creating a record that can be exported in
    > a
    > flat file as a single row.
    >
    > I have scoured the access help and online resources and cannot find a
    > solution.
    >
    > If someone can point me to a resource or example I can take it from there.
    >
    > Thanks much for your help. First time poster.
    >
    >
     
  7. tjay

    tjay
    Expand Collapse
    Guest

    hmmmm
    I posted another post with a spreadsheet attachment I guess that is not
    allowed

    Both examples worked fine, I am working in Access 2003
    While they have some features I need I really need the output to create new
    fields here is an example out put I need where additional parts for work
    orders are from the sub query

    testpartsheader_wo testdata parts_wo part desc cost part2 desc2 cost2
    part3 desc3 cost3 part4 desc4 cost4 part5 desc5 cost5
    1 testdata1 1 testpart1 1 some info 11
    2 testdata2 2 testpart2 2 some info 22 testpart2 1 some info 21
    3 testdata3 3 testpart3 3 some info 33 testpart3 2 some info 32
    testpart3 1 some info 31
    4 testdata4 4 testpart4 4 some info 44 testpart4 3 some info 43
    testpart4 2 some info 42 tespart4 1 some info 41
    5 testdata5 5 testpart5 5 some info 55 testpart5 4 some info 54
    testpart5 3 some info 53 testpart5 2 some info 52 testpart5 1 some info 51




    "tjay" <timjones@cyprex.biz> wrote in message
    news:SsKdnb4toOBAf-3ZnZ2dneKdnZydnZ2d@comcast.com...
    >I am attempting to create a flat file that I need to send to a service
    >processor.
    > Problem
    > The file I am creating comes from a query with a one to many join. This
    > results in several records being reproduced for each entry in the many
    > table.
    > I need to append the unique information from the duplicate rows onto the
    > end of the initial record. In effect creating a record that can be
    > exported in a flat file as a single row.
    >
    > I have scoured the access help and online resources and cannot find a
    > solution.
    >
    > If someone can point me to a resource or example I can take it from there.
    >
    > Thanks much for your help. First time poster.
    >
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 21 May 2006 18:11:20 -0700, "tjay" <timjones@cyprex.biz>
    wrote:

    >I posted another post with a spreadsheet attachment I guess that is not
    >allowed


    It's certainly not encouraged. I did in fact get the spreadsheet - but
    posting binary attachments is considered inappropriate in these text
    newsgroups, both for bandwidth and security.

    >Both examples worked fine, I am working in Access 2003
    >While they have some features I need I really need the output to create new
    >fields here is an example out put I need where additional parts for work
    >orders are from the sub query


    So you need to take a properly normalized table, and generate a
    denormalized repeating-fields spreadsheet?

    There's no easy way to do so. I'd suggest that your best bet would be
    to use VBA code to open the spreadsheet and loop through the records,
    adding new cells in the appropriate places. I'm not well enough
    skilled in Excel to do this off the top of my head - perhaps a post in
    an Excel newsgroup would be better.

    I do need to wonder: given that you have *the same information* in
    your Access database, what purpose is served by spreading it out
    horizontally on the page? Tradition?

    John W. Vinson[MVP]
     

Share This Page