Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Is Access good enough for this?

Discussion in 'Information Technology' started by Uzy, Nov 12, 2005.

  1. Uzy

    Uzy
    Expand Collapse
    Guest

    Hi

    I have recently start to use Access and trying to figure out how to
    solve the following problem, moreover, if it is even possible to do it
    in Access.

    Any ideas would be greatly appreciated.

    Many thanks
    Uzy

    I have a table called MASTER. Each row can be dimensioned by two dimensions,
    DIM1 or DIM2. I need to read this information and devise the permutations
    based
    on the actual data in DIM1 and DIM2. So the four records in MASTER would be
    turned to nine
    records as seen in the RESULTS table.

    MASTER
    ID1 DIM1 DIM2
    harry
    peter x x
    taz x
    uzy x

    DIM1
    key label
    1 phone
    2 memory

    DIM2
    pkey plabel
    p1 nokia
    p2 sony

    The result table I want is as follows:
    RESULT

    ID1 key label pkey plabel
    harry
    peter 1 phone p1 nokia
    peter 1 phone p2 sony
    peter 2 memory p1 nokia
    peter 2 memory p2 sony
    taz p1 nokia
    taz p2 sony
    uzy 1 phone
    uzy 2 memory
     
  2. Loading...


  3. giorgio rancati

    giorgio rancati
    Expand Collapse
    Guest

    Hi Uzy,

    try this query
    ----
    SELECT M.Id1,DIM1.Key,Dim1.Label,DIM2.PKEY,DIM2.PLabel
    From (MASTER AS M LEFT JOIN DIM1 ON (M.DIM1='X') =NOT IsNull(DIM1.KEY))
    LEFT JOIN DIM2 ON (M.DIM2='X') =NOT IsNull(DIM2.PKEY)
    ----

    bye
    --
    Giorgio Rancati
    [Office Access MVP]

    "Uzy" <Uzy@discussions.microsoft.com> ha scritto nel messaggio
    news:1DAB639C-8E60-4F48-8F2E-7D78DD4EB18C@microsoft.com...
    > Hi
    >
    > I have recently start to use Access and trying to figure out how to
    > solve the following problem, moreover, if it is even possible to do it
    > in Access.
    >
    > Any ideas would be greatly appreciated.
    >
    > Many thanks
    > Uzy
    >
    > I have a table called MASTER. Each row can be dimensioned by two

    dimensions,
    > DIM1 or DIM2. I need to read this information and devise the permutations
    > based
    > on the actual data in DIM1 and DIM2. So the four records in MASTER would

    be
    > turned to nine
    > records as seen in the RESULTS table.
    >
    > MASTER
    > ID1 DIM1 DIM2
    > harry
    > peter x x
    > taz x
    > uzy x
    >
    > DIM1
    > key label
    > 1 phone
    > 2 memory
    >
    > DIM2
    > pkey plabel
    > p1 nokia
    > p2 sony
    >
    > The result table I want is as follows:
    > RESULT
    >
    > ID1 key label pkey plabel
    > harry
    > peter 1 phone p1 nokia
    > peter 1 phone p2 sony
    > peter 2 memory p1 nokia
    > peter 2 memory p2 sony
    > taz p1 nokia
    > taz p2 sony
    > uzy 1 phone
    > uzy 2 memory
     
  4. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Your Master table is denormalized: you've got repeating groups in it.

    If you had

    ID1 DIM
    peter DIM1
    peter DIM2
    taz DIM2
    uzy DIM1

    it would be a bit easier.

    Wanting your output denormalized like that is what'll make it difficult in
    any relational DBMS.

    In essence, when you join two tables and don't provide a common key, you'll
    get a cartesian join.

    If you're willing to settle for the following, it'll be relatively easy:

    ID key label
    harry
    peter 1 phone
    peter 2 memory
    peter p1 nokia
    peter p2 sony
    taz p1 nokia
    taz p2 sony
    uzy 1 phone
    uzy 2 memory


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Uzy" <Uzy@discussions.microsoft.com> wrote in message
    news:1DAB639C-8E60-4F48-8F2E-7D78DD4EB18C@microsoft.com...
    > Hi
    >
    > I have recently start to use Access and trying to figure out how to
    > solve the following problem, moreover, if it is even possible to do it
    > in Access.
    >
    > Any ideas would be greatly appreciated.
    >
    > Many thanks
    > Uzy
    >
    > I have a table called MASTER. Each row can be dimensioned by two
    > dimensions,
    > DIM1 or DIM2. I need to read this information and devise the permutations
    > based
    > on the actual data in DIM1 and DIM2. So the four records in MASTER would
    > be
    > turned to nine
    > records as seen in the RESULTS table.
    >
    > MASTER
    > ID1 DIM1 DIM2
    > harry
    > peter x x
    > taz x
    > uzy x
    >
    > DIM1
    > key label
    > 1 phone
    > 2 memory
    >
    > DIM2
    > pkey plabel
    > p1 nokia
    > p2 sony
    >
    > The result table I want is as follows:
    > RESULT
    >
    > ID1 key label pkey plabel
    > harry
    > peter 1 phone p1 nokia
    > peter 1 phone p2 sony
    > peter 2 memory p1 nokia
    > peter 2 memory p2 sony
    > taz p1 nokia
    > taz p2 sony
    > uzy 1 phone
    > uzy 2 memory
     
  5. Uzy

    Uzy
    Expand Collapse
    Guest

    Giorgio

    That works a trest. I am obliged to you.

    Many Thanks

    "giorgio rancati" wrote:

    > Hi Uzy,
    >
    > try this query
    > ----
    > SELECT M.Id1,DIM1.Key,Dim1.Label,DIM2.PKEY,DIM2.PLabel
    > From (MASTER AS M LEFT JOIN DIM1 ON (M.DIM1='X') =NOT IsNull(DIM1.KEY))
    > LEFT JOIN DIM2 ON (M.DIM2='X') =NOT IsNull(DIM2.PKEY)
    > ----
    >
    > bye
    > --
    > Giorgio Rancati
    > [Office Access MVP]
    >
    > "Uzy" <Uzy@discussions.microsoft.com> ha scritto nel messaggio
    > news:1DAB639C-8E60-4F48-8F2E-7D78DD4EB18C@microsoft.com...
    > > Hi
    > >
    > > I have recently start to use Access and trying to figure out how to
    > > solve the following problem, moreover, if it is even possible to do it
    > > in Access.
    > >
    > > Any ideas would be greatly appreciated.
    > >
    > > Many thanks
    > > Uzy
    > >
    > > I have a table called MASTER. Each row can be dimensioned by two

    > dimensions,
    > > DIM1 or DIM2. I need to read this information and devise the permutations
    > > based
    > > on the actual data in DIM1 and DIM2. So the four records in MASTER would

    > be
    > > turned to nine
    > > records as seen in the RESULTS table.
    > >
    > > MASTER
    > > ID1 DIM1 DIM2
    > > harry
    > > peter x x
    > > taz x
    > > uzy x
    > >
    > > DIM1
    > > key label
    > > 1 phone
    > > 2 memory
    > >
    > > DIM2
    > > pkey plabel
    > > p1 nokia
    > > p2 sony
    > >
    > > The result table I want is as follows:
    > > RESULT
    > >
    > > ID1 key label pkey plabel
    > > harry
    > > peter 1 phone p1 nokia
    > > peter 1 phone p2 sony
    > > peter 2 memory p1 nokia
    > > peter 2 memory p2 sony
    > > taz p1 nokia
    > > taz p2 sony
    > > uzy 1 phone
    > > uzy 2 memory

    >
    >
    >
     
  6. Uzy

    Uzy
    Expand Collapse
    Guest

    Hi Giorgio

    Your solution is pretty good and to be perfectly honest I am still trying to
    get my head around it. Question I now have is that could this solution now be
    extended to include more DIM's in the MASTER? In reality I have around 13
    more.. so I have DIM1, DIM2,.....,DIM15. I have tried the obvious thing by
    following the pattern but not got anywhere. I would appreciate if you could
    shed some more light on this.

    Many Thanks

    "giorgio rancati" wrote:

    > Hi Uzy,
    >
    > try this query
    > ----
    > SELECT M.Id1,DIM1.Key,Dim1.Label,DIM2.PKEY,DIM2.PLabel
    > From (MASTER AS M LEFT JOIN DIM1 ON (M.DIM1='X') =NOT IsNull(DIM1.KEY))
    > LEFT JOIN DIM2 ON (M.DIM2='X') =NOT IsNull(DIM2.PKEY)
    > ----
    >
    > bye
    > --
    > Giorgio Rancati
    > [Office Access MVP]
    >
    > "Uzy" <Uzy@discussions.microsoft.com> ha scritto nel messaggio
    > news:1DAB639C-8E60-4F48-8F2E-7D78DD4EB18C@microsoft.com...
    > > Hi
    > >
    > > I have recently start to use Access and trying to figure out how to
    > > solve the following problem, moreover, if it is even possible to do it
    > > in Access.
    > >
    > > Any ideas would be greatly appreciated.
    > >
    > > Many thanks
    > > Uzy
    > >
    > > I have a table called MASTER. Each row can be dimensioned by two

    > dimensions,
    > > DIM1 or DIM2. I need to read this information and devise the permutations
    > > based
    > > on the actual data in DIM1 and DIM2. So the four records in MASTER would

    > be
    > > turned to nine
    > > records as seen in the RESULTS table.
    > >
    > > MASTER
    > > ID1 DIM1 DIM2
    > > harry
    > > peter x x
    > > taz x
    > > uzy x
    > >
    > > DIM1
    > > key label
    > > 1 phone
    > > 2 memory
    > >
    > > DIM2
    > > pkey plabel
    > > p1 nokia
    > > p2 sony
    > >
    > > The result table I want is as follows:
    > > RESULT
    > >
    > > ID1 key label pkey plabel
    > > harry
    > > peter 1 phone p1 nokia
    > > peter 1 phone p2 sony
    > > peter 2 memory p1 nokia
    > > peter 2 memory p2 sony
    > > taz p1 nokia
    > > taz p2 sony
    > > uzy 1 phone
    > > uzy 2 memory

    >
    >
    >
     
  7. giorgio rancati

    giorgio rancati
    Expand Collapse
    Guest

    Hi Uzy,

    You can add a left join for every table of DIMx.
    example
    ----
    SELECT M.Id1,DIM1.Key,Dim1.Label,
    DIM2.PKEY,DIM2.PLabel,
    DIM3.PKEY,DIM3.PLabel,
    DIM4.PKEY,DIM4.PLabel,
    DIM5.PKEY,DIM5.PLabel,
    DIM6.PKEY,DIM6.PLabel,
    DIM7.PKEY,DIM7.PLabel,
    DIM8.PKEY,DIM8.PLabel,
    DIM9.PKEY,DIM9.PLabel,
    DIM10.PKEY,DIM10.PLabel,
    DIM11.PKEY,DIM11.PLabel,
    DIM12.PKEY,DIM12.PLabel,
    DIM13.PKEY,DIM13.PLabel,
    DIM14.PKEY,DIM14.PLabel,
    DIM15.PKEY,DIM15.PLabel
    From ((((((((((((((MASTER AS M
    LEFT JOIN DIM1 ON (M.DIM1='X') =NOT IsNull(DIM1.KEY))
    LEFT JOIN DIM2 ON (M.DIM2='X') =NOT IsNull(DIM2.PKEY))
    LEFT JOIN DIM3 ON (M.DIM3='X') =NOT IsNull(DIM3.PKEY))
    LEFT JOIN DIM4 ON (M.DIM4='X') =NOT IsNull(DIM4.PKEY))
    LEFT JOIN DIM5 ON (M.DIM5='X') =NOT IsNull(DIM5.PKEY))
    LEFT JOIN DIM6 ON (M.DIM6='X') =NOT IsNull(DIM6.PKEY))
    LEFT JOIN DIM7 ON (M.DIM7='X') =NOT IsNull(DIM7.PKEY))
    LEFT JOIN DIM8 ON (M.DIM8='X') =NOT IsNull(DIM8.PKEY))
    LEFT JOIN DIM9 ON (M.DIM9='X') =NOT IsNull(DIM9.PKEY))
    LEFT JOIN DIM10 ON (M.DIM10='X') =NOT IsNull(DIM10.PKEY))
    LEFT JOIN DIM11 ON (M.DIM11='X') =NOT IsNull(DIM11.PKEY))
    LEFT JOIN DIM12 ON (M.DIM12='X') =NOT IsNull(DIM12.PKEY))
    LEFT JOIN DIM13 ON (M.DIM13='X') =NOT IsNull(DIM13.PKEY))
    LEFT JOIN DIM14 ON (M.DIM14='X') =NOT IsNull(DIM14.PKEY))
    LEFT JOIN DIM15 ON (M.DIM15='X') =NOT IsNull(DIM15.PKEY)
    ----

    For simplicity I maintained the DIM2 fields name
    Check the fields name for every DIMx table

    By
    --
    Giorgio Rancati
    [Office Access MVP]

    "Uzy" <Uzy@discussions.microsoft.com> ha scritto nel messaggio
    news:4322E854-AB82-4AB9-BF28-0D03DA53C264@microsoft.com...
    > Hi Giorgio
    >
    > Your solution is pretty good and to be perfectly honest I am still trying

    to
    > get my head around it. Question I now have is that could this solution now

    be
    > extended to include more DIM's in the MASTER? In reality I have around 13
    > more.. so I have DIM1, DIM2,.....,DIM15. I have tried the obvious thing by
    > following the pattern but not got anywhere. I would appreciate if you

    could
    > shed some more light on this.
    >
    > Many Thanks
    >
     
  8. giorgio rancati

    giorgio rancati
    Expand Collapse
    Guest

    Hi Uzy,

    you can also have a unique DIM table
    example

    MASTER
    ID1 DIM1 DIM2 DIM3 ... DIM15
    harry
    peter x x
    taz x
    uzy x

    DIM
    dimID key label
    1 1 phone
    1 2 memory
    2 1 nokia
    2 2 sony
    ..
    ..
    15

    the query
    ----
    SELECT M.Id1,
    DIM1.Key,Dim1.Label,
    DIM2.KEY,DIM2.Label,
    DIM3.KEY,DIM3.Label,
    DIM4.KEY,DIM4.Label,
    DIM5.KEY,DIM5.Label,
    DIM6.KEY,DIM6.Label,
    DIM7.KEY,DIM7.Label,
    DIM8.KEY,DIM8.Label,
    DIM9.KEY,DIM9.Label,
    DIM10.KEY,DIM10.Label,
    DIM11.KEY,DIM11.Label,
    DIM12.KEY,DIM12.Label,
    DIM13.KEY,DIM13.Label,
    DIM14.KEY,DIM14.Label,
    DIM15.KEY,DIM15.Label
    From ((((((((((((((MASTER AS M
    LEFT JOIN DIM AS DIM1 ON (M.DIM1='X') =(DIM1.dimID=1))
    LEFT JOIN DIM AS DIM2 ON (M.DIM2='X') = (DIM2.dimID=2))
    LEFT JOIN DIM AS DIM3 ON (M.DIM3='X') = (DIM3.dimID=3))
    LEFT JOIN DIM AS DIM4 ON (M.DIM4='X') = (DIM4.dimID=4))
    LEFT JOIN DIM AS DIM5 ON (M.DIM5='X') = (DIM5.dimID=5))
    LEFT JOIN DIM AS DIM6 ON (M.DIM6='X') = (DIM6.dimID=6))
    LEFT JOIN DIM AS DIM7 ON (M.DIM7='X') = (DIM7.dimID=7))
    LEFT JOIN DIM AS DIM8 ON (M.DIM8='X') = (DIM8.dimID=8))
    LEFT JOIN DIM AS DIM9 ON (M.DIM9='X') = (DIM9.dimID=9))
    LEFT JOIN DIM AS DIM10 ON (M.DIM10='X') = (DIM10.dimID=10))
    LEFT JOIN DIM AS DIM11 ON (M.DIM11='X') = (DIM11.dimID=11))
    LEFT JOIN DIM AS DIM12 ON (M.DIM12='X') = (DIM12.dimID=12))
    LEFT JOIN DIM AS DIM13 ON (M.DIM13='X') = (DIM13.dimID=13))
    LEFT JOIN DIM AS DIM14 ON (M.DIM14='X') = (DIM14.dimID=14))
    LEFT JOIN DIM AS DIM15 ON (M.DIM15='X') = (DIM15.dimID=15)
    ----

    bye
    --
    Giorgio Rancati
    [Office Access MVP]
     

Share This Page