SQL - complex unions 1

[kingz2000]

Hi everyone,

I have a table looking like the one below:

X Y Z
-------------------------------
A ALPHA 1
A BETA 4
B DELTA 2
C ALPHA 1
C BETA 2
C DELTA 3
--------------------------------

Now I would like the easiest means of creating a table with all possible variations of column Y for each Column X.Hence, looking like below:


X Y Z
-------------------------------
A ALPHA 1
A BETA 4
A DELTA 0
B ALPHA 0
B BETA 0
B DELTA 2
C ALPHA 1
C BETA 2
C DELTA 3
--------------------------------

Notice the zeros in Column Z where there wasn't a value, and all Column X Fields have 3 datasets.

I thought it would be possible to create a simple table with the 3 possible Column Ys(Alpha, Beta and Delta) and then to use UNION somehow.

I am working in MS ACCESS2003 by the way.

Thanks in advance for any help

 

[PHV]

A starting point (typed, untested):

SELECT C.X,C.Y,Nz(D.Z,0) AS Z
FROM (
SELECT A.X,B.Y
FROM (SELECT DISTINCT X FROM yourTable) A
, (SELECT DISTINCT Y FROM yourTable) B
) C LEFT JOIN yourTable D ON C.X=D.X AND C.Y=D.Y
ORDER BY 1,2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I would first create a cartesian query (qcarXY):

SELECT C1.X, C2.Y
FROM tblOneBelow C1, tblOneBelow AS C2
GROUP BY C1.X, C2.Y;

Then combine with your existing table in another query:

SELECT qcarXY.X, qcarXY.Y, Val(nz([Z],0)) AS NewZ
FROM qcarXY LEFT JOIN tblOneBelow ON (qcarXY.Y = tblOneBelow.Y) AND (qcarXY.X = tblOneBelow.X);

Duane
Hook'D on Access
MS Access MVP

 

[kingz2000]

WOW.. PHVs method works..thanks a lot. 2 more questions:

Firstly, How do I get into higher SQL like this..Is there a good book on such things?

Secondly, unfortunately I don't understand the SQL as of yet and I was wondering how to change it if I actually have another column in 'mytable'.

 

[dhookom]

There are tons of resources regarding SQL syntax on the web. If you are looking for a book, check the recommendations at SQL / Database Design

Duane
Hook'D on Access
MS Access MVP

 

[kingz2000]

Ok, I managed to use the cartesian query from PHV above. Unfortunately using this method the figures are changed to strings, which disables me to produce the result in 2 Decimal places(for example) as needed for the report. How do I get round this problem??
DO I need to make a quewry from this query, or can I ammend the above query to function without changing the format.

Thanks in advance

 

[PHV]

Use the Val function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886