Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    'Stringing Data Together' (2000 SR3)

    Hi,
    I have a set of data that has Post Title, Family, Level ; Like this:

    Cook.............Technical........A
    Cleaner........Technical........A
    Secretary......Admin..............B
    etc etc

    What I want is a crosstab query with Family as column headings, Level as row headings. That's obviously easy but I'd like the value in the crosstab to be a string containing all the relevant post titles.

    In the example above that would mean that the Value for Technical A in the crosstab would be "Cook, Cleaner.......

    These strings will contain less than 100 post titles.

    Thanks,
    Jim MacLeod
    Shetland Isles

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

    Re: 'Stringing Data Together' (2000 SR3)

    The attachment to <post#=301,393>post 301,393</post#> contains the code for a function named Concat that you can use to concatenate values into one long string. Copy the function into a standard module. You can then use it for the Value column of the crosstab query (set the Total option to Expression). The expression would look like this:
    <code>
    Concat("NameOfTableOrQuery","Post Title","Family=" & Chr(34) & [Family] & Chr(34) & " AND Level=" Chr(34) & [Level] & Chr(34))
    </code>
    Substitute the correct names for the table/query and fields.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: 'Stringing Data Together' (2000 SR3)

    Edited by HansV to provide links to posts (and to correct one post number) - see <!help=19>Help 19<!/help>

    Hi,
    I think I'm getting there!
    I entered the code as per your old <post:=301,393>post 301,393</post:> , when I tried to run it I got the error as per <post:=309,902>post 309,902</post:> which I fixed by following your instructions in <post:=309,912>post 309,912</post:>.

    I then got an error on the same line (...Set rst = dbs.OpenRecordset(strSQL)....) same line as <post:=310,656>post 310,656</post:> .

    I changed the code as per your <post:=324,705>post 324,705</post:>

    I'm still getting an error at the line Set rst = dbs.OpenRecordset(strSQL) :
    Run-time error '3016':
    Too few parameters. Expected 1

    The value of strSQL is:
    SELECT [Post Title] FROM [Q:ForBigPoster] WHERE (Family="Business Support" AND Grade="A") ORDER BY [Post Title];

    I've had a go at debugging but I'm afraid I'm stumped.

    Jim

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

    Re: 'Stringing Data Together' (2000 SR3)

    It would be nice if you could take the trouble to provide links to posts (as I have done for you), it is much easier for others reading the thread. It is very easy: just select the post number in the header of the post, including the square brackets, e.g. <!t>[Post: 301,393]. Press Ctrl+C to copy to the clipboard, then press Ctrl+V in your post/reply to paste it. When you submit your post, the Lounge software will convert the text to a clickable link.

    The error message that you get means that either one of the names in the SQL is not recognized. Another possibility is that Q:ForBigPoster is a parameter query. DAO can't handle that (as I clearly indicated in the code attachment).

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: 'Stringing Data Together' (2000 SR3)

    Sorry, I thought that you could do the links because you are the "Administrator".

    I'll do some more digging into what could be wrong.

    Jim

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: 'Stringing Data Together' (2000 SR3)

    Yes, you were spot on in post <post:=704,649>post 704,649</post:> , one of my names in the SQL was incorrect.

    Thanks, this will save me loads of time.

    Hopefully the above link will work too!

    Thanks again,
    Jim

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

    Re: 'Stringing Data Together' (2000 SR3)

    Yep, the link works! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

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