Is Access good enough for this?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Giorgio

That works a trest. I am obliged to you.

Many Thanks

giorgio rancati said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Totally unsure - Tech Challenge 2
Some good Select or While 11
IComparer, Compare, CompareTo for multiple comparisons for Sort inArray and List 3
Access To Text For Webpage 1
This is a real doozy... 5
Looking for a query 2
This is a good one! 1
Object Variable or With Block Variable Not Set 3

Back
Top