Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show detail rec's left to right beside group data (2kpro)

    I have a report based on a select query. The report is grouped by client_id, then order_id, and the details are the items ordered uniquely identified as control_id. Each item belongs to a category dining table, cofee table etc. The report must show category. Because each table can belong to multiple categories (dining tables and square dining tables) ... my query returns multiple records for each single item. Right now I have a roup by the item and am letting the various categories list down as the detail records. I would like have one line item per contol_id and a field that picks up the categories and shows them from left to right as a part of the control_id record ... this may be based on a new query that is a crosstab or something... or maybe its done in the report.

    now I get this

    client 1
    -----order 5
    ----------item77, model 88
    ---------------dining table
    ---------------square dining table
    ---------------table

    I would like to have

    client 1
    -----order 5
    ----------item77--model 88 dining table--square dining table--table

    I could easily settle for including only the first three categories in the list (rarely does an item belong to more than 3)

    Thank you .... Jason

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Show detail rec's left to right beside group data (2kpro)

    You need to concatenate records from multiple category records into a single field to display them in the report.

    The thread beginning with <post#=514,047>post 514,047</post#> has a concat function that does this job.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show detail rec's left to right beside group data (2kpro)

    Thanks for the help. I think you are correct. However I added the module. I checked the DAO 3.51 in tools references in the VB editor. Inside the query builder I click build and go to functions and the function is there. I include it in my query and when I run the query it very quickly responds telling me there is an unknown function Concat

    Any thoughts. ( I have added a total of maybe 3 functions...in my life so I am sure it is an issue how I added the function....

    Thank you very much

    Thanks

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

    Re: Show detail rec's left to right beside group data (2kpro)

    In the first place, in Access 2000 and higher, you should set a reference to the Microsoft DAO 3.6 Object Library, not to 3.5*.

    If the expression builder "sees" the Concat function, you probably put it in the correct place. Can you tell us what the result of the expression builder in your query is? Or perhaps attach a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Show detail rec's left to right beside group data (2kpro)

    I just tried it myself and it worked. I am not sure what you have done wrong

    Have a look in the attachment.
    Regards
    John



  6. #6
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show detail rec's left to right beside group data (2kpro)

    thanks Hans.

    The error says

    undefined function 'Concat' in expresion.

    Thanks again
    Jason
    PS (sending a stripped down version is rather impractical with this DB... )

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

    Re: Show detail rec's left to right beside group data (2kpro)

    See if you can find out from John Hutchinson's example (thanks, John!) what is wrong with the way you use Concat.

  8. #8
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show detail rec's left to right beside group data (2kpro)

    thanks to both.

    I used John's sample in his DB and it worked. I imported his tables and query into my DB and got the same errors as before. I deleted my Concat module. and imported his. His query worked worked in my DB with his module. (I noticed his module was named module1 in the module window. I had named mine Concat there... maybe I created myself a conflict doing that) Eventual after additional pain and suffering my query also seems to work.

    Notes.
    In the query I had to NOT ADD the table that is being concatenated (I seemed to create a huge record set until I figured that out)
    Also the "table" being concatenated can be a query but there is nothing that says whether it is a query or table so I assume if you had a table and query by the same name (or maybe access wont permit that?) you would get a conflict.
    At one point I got into an eternal loop in the de###### ---strRSt was highlighted and I would hit end and just kept looping ... It said I lacked some additional perameter. I had to control alt delete to make it stop. (nothing unusual for me)

    These notes are only meant to help provide feedback in case it is useful. Not at all meant to complain..

    Thank you both very much John and Hans. This is great. I can use ths other places too!

    Very truly yours.

    Jason

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Show detail rec's left to right beside group data (2kpro)

    The module needs to have a different name from the function, so "I had named mine Concat " was definetely one of the problems.
    Regards
    John



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

    Re: Show detail rec's left to right beside group data (2kpro)

    As John notes, naming the module Concat was the main problem - if you give a module the same name as a procedure or function confuses Visual Basic.

    It is not possible to have a table and a query of the same name in one database - Access will not allow you to create this situation.

    Glad you were able to make it work, and thanks again to John for providing the example.

Posting Permissions

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