Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Jul 2003
    Posts
    41

    Unanswered: database problem

    I have another problem with my database. I have two tables. One list the programs of the users and another list the modules. Can I send you a sample of the two tables and get you to take a look at them? What I am wanting to do is fix a form or query that will allow me to count the number of modules an individual user has access to. However, our security is set up whereas if a user has a module they can still have access to or be excluded from a program in that module and can have access to individual programs without having access to a module.
    Patricia Robey

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And your problem is???? Why not just post the problem code and/or a sample of your data here ...

  3. #3
    Join Date
    Jul 2003
    Posts
    41
    First_NameLast_Name Bpcs_ID Location ProgramName
    Greg Albertson AlbertsonG Franklin SFC100
    Greg Albertson AlbertsonG Franklin SFC400
    Greg Albertson AlbertsonG Franklin SFC600


    FirstName LastName BpcsId Location Module
    Greg Albertson Albertsong Franklin Sfc


    Here are samples from two tables. THis user has a program name called SFC100 etc..., however in the bottom example the user has a module name of SFC. I am trying to count only SFC for a user if it appears in the Module name and not the program name in this example.
    Patricia Robey

  4. #4
    Join Date
    Jul 2003
    Posts
    41

    Talking previous post

    Hello

    I have not yet received help on the previous post. Will someone be responding to my post?

    Thanks
    Pat
    Patricia Robey

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Pat,

    You haven't explained your problem yet (or at least so that I can understand it) ... Are you trying to relate the module to the program name? Are you trying to find users who can run programs within a module but are not assigned to a module? What are you looking for?

  6. #6
    Join Date
    Jul 2003
    Posts
    41
    Does the example not explain what I am trying to do?
    Patricia Robey

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this:
    Code:
    select M.FirstName
         , M.LastName
         , M.BpcsId
         , M.Location
         , M.Module
         , count(P.ProgramName) as ProgramNames
      from Modules M
    left outer
      join Programs P
        on M.FirstName = P.FirstName
       and M.LastName  = P.LastName
       and M.BpcsId    = P.BpcsId
       and M.Location  = P.Location
       and M.Module    = Left(P.ProgramName,len(M.Module))
    group
        by M.FirstName
         , M.LastName
         , M.BpcsId
         , M.Location
         , M.Module
    rudy
    http://r937.com/

  8. #8
    Join Date
    Jul 2003
    Posts
    41
    Where do I put this code?
    Patricia Robey

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    go to the Queries tab and press New

    Design View should be highlighted, press OK

    this should bring up the Show Table wizard, just close it

    from the menu bar, View > SQL View

    now you should see window with SELECT; in it

    delete that and paste my query

    now you can run it, save it, etc.

    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2003
    Posts
    41
    I have gotten the code to work, and thanks a lot. However it is showing me all the programs and what I need to see is for example if a user has a module name of SFC and also a program name of SFC900, I don't want to be able to count that twice. In other words if SFC is in the module table exclude any reference to SFC for the users when I count from the program table. I hope I haven't confused you too much.

    Thanks
    Patricia Robey

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, that last part was way too confusing

    how about some sample rows?

    it's helps to visualize the problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2003
    Posts
    41
    I am pasteing this from the previous post. The user below has 3 programs that begin with SFC. However he also has an entry in the module table of SFC. I need to run a query that will show all people with the program SFC100 and the module sfc and vice versa. A query that shows SFC in the module and not SFC100 in the program table.

    First_NameLast_Name Bpcs_ID Location ProgramName
    Greg Albertson AlbertsonG Franklin SFC100
    Greg Albertson AlbertsonG Franklin SFC400
    Greg Albertson AlbertsonG Franklin SFC600


    FirstName LastName BpcsId Location Module
    Greg Albertson Albertsong Franklin Sfc
    Patricia Robey

  13. #13
    Join Date
    Jul 2003
    Posts
    41
    First_Name Last_NameBpcsId Location Module ProgramNames
    Adria Mcpherson McphersonANorwood Sfc 31
    Allison Brown BrownAL Norwood Sfc 32
    Angela Woodlee WoodleeA Franklin Sfc 42


    This is the results I get whenever I run the query.
    Patricia Robey

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "I need to run a query that will show all people with the program SFC100 and the module sfc and vice versa"

    that's a straight inner join

    but i don't think that's what you want, right?

    "A query that shows SFC in the module and not SFC100 in the program table."

    change the left outer join i gave you slightly --
    Code:
    select M.FirstName
         , M.LastName
         , M.BpcsId
         , M.Location
         , M.Module
      from Modules M
    left outer
      join Programs P
        on M.FirstName = P.FirstName
       and M.LastName  = P.LastName
       and M.BpcsId    = P.BpcsId
       and M.Location  = P.Location
       and M.Module    = Left(P.ProgramName,len(M.Module))
     where M.Module = 'SFC'
       and P.ProgramName <> 'SFC001'
    group
        by M.FirstName
         , M.LastName
         , M.BpcsId
         , M.Location
         , M.Module
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2003
    Posts
    41
    Also SFC in module and SFC100 in program table.
    Patricia Robey

Posting Permissions

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