Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2002
    Posts
    37

    Unanswered: Sql Report Help Please!!!

    I have ONE table.
    I have fields in the table for software name, and in the same software table it shows who has this software (non realtional I know), i.e. user1, user2, user3 which yes/no boolean values.
    The app may have more than one user associated with on app, so user 1 and user 2 may have the same app.
    I need a report to group by the users and their software:

    User1
    Access97
    Access2000
    MSWord
    User2
    Access97
    Lotus Millenium
    User3
    Netscape

    This is non-relational and evil!!!!
    This is what I have to work with though

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this:
    Code:
    select 'User1', softwarename
      from softwaretable
     where user1 <> 0
    union all
    select 'User2', softwarename
      from softwaretable
     where user2 <> 0
    union all
    select 'User3', softwarename
      from softwaretable
     where user3 <> 0
    order 
        by 1,2
    this will return

    User1 Access97
    User1 Access2000
    User1 MSWord
    User2 Access97
    User2 Lotus Millenium
    User3 Netscape

    rudy
    http://r937.com/

  3. #3
    Join Date
    Nov 2002
    Posts
    150

    Re: Sql Report Help Please!!!

    I'm a little confused on your table structure. Is it a column for user and then several yes/no colunms for software name? I thnik I can help if you show the structure.

  4. #4
    Join Date
    Nov 2002
    Posts
    37

    Re: Sql Report Help Please!!!

    OK my table is something like this

    ProductID --> Primary key
    ProductName --> name of software
    User1 --> yes/no
    User2--> yes/no
    User3--> yes/no

    Here is an example of the records in the table:
    Access 97, yes, yes, no
    Word2000, no, yes, yes.
    Excel2000, yes, no,yes

    The desired use is to to have a list by user of what software they have.
    The desired output for the querie would be grouped by user ideally.
    User 1
    Access97
    Excel 2000
    User 2
    Access97
    Word2000
    User 3
    Word 2000
    Excel 2000

  5. #5
    Join Date
    Nov 2002
    Posts
    150

    Re: Sql Report Help Please!!!

    So if you get a new user you have to add a column to the table? Whew, you better change that quick.

    Anyways, you might be able to create a crosstab query to give you the correct results otherwise it's going to be alot of typing.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what i posted is grouped by user and will work on the table you describe

    rudy

  7. #7
    Join Date
    Jan 2003
    Posts
    24
    Originally posted by r937
    what i posted is grouped by user and will work on the table you describe

    rudy
    But then how could the report print out the following:

    Access97 User1
    Access97 User2
    Access97 User3
    Word2000 User1
    Word2000 User2

  8. #8
    Join Date
    Jan 2003
    Posts
    24
    I ran the above code, and everything was grouped into two colums (user & product name). How can the report be generated to print that out?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lunch36, that would be the result with a different ORDER BY sequence in the query

  10. #10
    Join Date
    Jan 2003
    Posts
    24
    How would you do that then?

Posting Permissions

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