Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    1

    Unanswered: Need help on an inner join....

    Hi all,

    Got two tables, one called 'Working' with columns called, Users, Application1, Application2, Application3 etc. Second table called CitrixApps with a single column, citrixapps.

    What I need to do is return several recordsets:

    1) All rows with 1 application in citrixapps;

    2) As above, but with 2 applications in citrixapps;

    3) As above, but with 3 applications in citrixapps etc.


    THEN - I'll need them grouped by location.

    Tables.columns:

    Working.user
    Working.location
    working.applications 1 (note the plural 's'!!)
    working.application 2
    working.application 3
    working.application 4
    working.application 5


    citrixapps.citrixapps

    Bear in mind there are plenty of nulls in the table.

    Sample data:

    Sarah Lewis lewiss01 020 8888 8888 /7957 Civil Center - Stockwell 1st LCS0205 COMPAQ TM 6S2AKN9Z331E RSS RON Ebusiness NULL Adobe 7 is a requirement for RON NULL NULL NULL NULL NULL NULL NULL N/K With EDS HP Laserjet 1100 (MS) / HP business inkjet 1200 Available in Central Software Library NULL No - Generally in meetings am / appointments with public pm NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    I think i should emphasise something, "Applications 1" is NOT a column of users who have one application in common with citrixapps - if a users row has 5 applications listed, they will each be in Application 1, etc Application 2 etc as above.

    Cheers,

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    And this is why you should use a normalized database structure.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Have to agree with Teddy

    i'd suggest

    Users (PK ID, UserName, ...)
    Apps (PK ID, AppName, ...)

    linked via

    Working (CK Users.ID, CK Apps.ID)
    CitrixApps(CK Users.ID, CK Apps.ID)
    or
    Working (CK Users.ID, CK Apps.ID, IsCitrix)

    Key to above

    Table (Field1, Field2, Field3, Field4)
    PK = Primary Key, CK = Combined Key
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    soz misread you requirements CitrixApps is not a subset of working

    Users (PK ID, UserName, ...)
    CitrixApps(PK ID, AppName, ...)

    linked via

    Working (CK Users.ID, CK CitrixApps.ID)


    Key to above

    Table (Field1, Field2, Field3, Field4)
    PK = Primary Key, CK = Combined Key
    Last edited by m.timoney; 01-17-07 at 12:13.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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