Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compile multiple records (Access 2000)

    Before I pose my question, I would like to thank all who have helped me. I haven't always been able to get back to the helpers and thank them personally but I have been helped every time I have asked for it. Now my question. I have a parent table and a child table that holds multiple entries. The multiple entries are of course linked to a single record in the parent table by the auto ID number in the parent and an ID number(long integer) in the child table. The data is entered by way of asubform(data source child table) on a data input form(data source parent table). Now I need to combine all of the values(records) listed for a particular id in the child table into a single field on the parent table. For example a Company (Parent table Invoice # <autonumber>) bought pens, paper ink (3 entries in the Childtable linked by <ID>). Now I want the combine the <text pen&paper&ink> into a single field on the parent table(or created query). Any ideas. Please remember that I am code challenged and need many 'Comments' to understand any proposed code. Thanks much in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile multiple records (Access 2000)

    See the thread starting with <post#=263987>post 263987</post#>, in particular the replies by Tom Bushaw and myself. Both use DAO (Data Access Objects), so you must select Tools | References... in the Visual Basic Editor, and set a reference to the Microsoft DAO 3.6 Object Library.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile multiple records (Access 2000)

    Thank You. I will try it.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile multiple records (Access 2000)

    I am sorry I couldn't make heads or tales of the posted functions. I am a novice at Vb. Could I impose on you to look at the attached dB? It contains two tables (1) "tblProcedure" with multiple records referencing an Operation ID and (2)"tblConcatenatedProcedure" which would be the destination table of the concactenated procedures from the tblProcedure. And a query "qryConcatenated" with the criteria set to a particular Operation ID. The end result would be a single ORID and any procedures matching that ORID in the tblProcedure strung together as one value.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile multiple records (Access 2000)

    Can you try to attach your database again? There is no attachment now.

    Remember the following "rules":
    <UL><LI>You must create a zip file from your database, for .mdb is not an allowed attachment type.
    <LI>The zip file must be less than 100 kb.
    <LI>If you preview your reply, the text box containing the file name of the attachment will be cleared.[/list]Thanks.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile multiple records (Access 2000)

    Sorry, here is the attachment.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile multiple records (Access 2000)

    Thank you that's exactly what I needed. Very streamlined. Now I have to study it so I can use the technique in future projects.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile multiple records (Access 2000)

    Bill,

    I have attached the modified database. I have done the following things:
    <UL><LI>Added a module modConcatenate that contains the Concat function.
    <LI>Set a reference to the DAO 3.6 object library (Tools /References... in the Visual Basic Editor); the module needs this to work.
    <LI>Modified qryProcedure to use the function. It will now display the concatenated procedures.
    <LI>Created an append query qryFillTable to populate the other table. It has been executed already, so if you open the table, you will see the results.[/list]Note: executing the append query can be slow (depending on the speed and memory of your PC).
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •