Putting table side by side

  • Thread starter Thread starter PF
  • Start date Start date
P

PF

Hi,

tbl A tbl B
prod,data prod,data
x,a x,c
x,b x,d
x,c

if i link tbl a and tbl b on the prod field
i have as a result

x,a,c
x,a,d
x,b,c
x,b,d
x,c,c
x,c,d

what should i do if i simply want
x,a,c
x,b,d
x,c,empty

I just want to put those table side by side, what is the correct way to do
that
Regards,
Pierre
 
You could probably do this in a report more easily. To do it in a
query, rank both tables and join on the ranking. However, you'll need
a FULL OUTER JOIN, which is also a complication.

Start with 2 queries for ranking:

Saved Query QA:
SELECT prod, data,
(SELECT COUNT(*) FROM tblA A1
WHERE A1.prod < A.Prod
OR (A1.prod = A.prod AND A1.data < A.Data) AS Rank
FROM tblA A

Saved query: QB
SELECT prod, data,
(SELECT COUNT(*) FROM tblB B1
WHERE B1.prod < B.Prod
OR (B1.prod = B.prod AND B1.data < B.Data) AS Rank
FROM tblB B

From these tables you can LEFT JOIN on the Rank columns. In order for
this to work, the columns prod and data in both tables must form
unique values together. Otherwise you would have duplicate Rank
values and get a partial cross-product on the duplicated values.

Also, this would work only if tblA is longer than or the same length
as tblB. Reversing the JOIN would handle the other case. We can make
the union of these two to get the effect of the FULL OUTER JOIN, which
Access Jet does not directly support. Let's save the details of that
for later. Can you get this much to work?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
You could do something like this:
tA
L
****
A
C
D
K
S
X

tB
L
***
W
X
Y
Z
tB

_qA
SELECT "A" AS Grp, tA.L, Sum(1) AS N
FROM tA, tA AS tA_1
WHERE (((tA_1.L)<=[tA].[L]))
GROUP BY "A", tA.L
ORDER BY "A", tA.L;

_qB
SELECT "B" AS Grp, tB.L, Sum(1) AS N
FROM tB, tB AS tB_1
WHERE (((tB_1.L)<[tB].[L]))
GROUP BY "B", tB.L
ORDER BY "B", tB.L;

_Q
SELECT * FROM _qA UNION SELECT * FROM _qB;

Then
TRANSFORM First([_Q].L) AS FirstOfL
SELECT [_Q].N
FROM _Q
GROUP BY [_Q].N
ORDER BY [_Q].N, [_Q].Grp
PIVOT [_Q].Grp;

Gives you,

N A B
*********************
1 A X
2 C Y
3 D Z
4 K
5 S
6 X

Should be fast enough for normal sized sets (don't start doing all the post codes on it).

Best Regards

Peter.
 
You could do something like this:
tA
L
****
A
C
D
K
S
X

tB
L
***
W
X
Y
Z
tB

_qA
SELECT "A" AS Grp, tA.L, Sum(1) AS N
FROM tA, tA AS tA_1
WHERE (((tA_1.L)<=[tA].[L]))
GROUP BY "A", tA.L
ORDER BY "A", tA.L;

_qB
SELECT "B" AS Grp, tB.L, Sum(1) AS N
FROM tB, tB AS tB_1
WHERE (((tB_1.L)<[tB].[L]))
GROUP BY "B", tB.L
ORDER BY "B", tB.L;

_Q
SELECT * FROM _qA UNION SELECT * FROM _qB;

Then
TRANSFORM First([_Q].L) AS FirstOfL
SELECT [_Q].N
FROM _Q
GROUP BY [_Q].N
ORDER BY [_Q].N, [_Q].Grp
PIVOT [_Q].Grp;

Gives you,

N A B
*********************
1 A X
2 C Y
3 D Z
4 K
5 S
6 X

Should be fast enough for normal sized sets (i.e. don't start doing all the post codes on it!).

Best Regards

Peter.
 
Thanks for the answers

if i use

SELECT a.prod, a.data, (select count(*) from tblA A1
where a1.prod<A.prod
or(a1.prod=a.prod and A1.data <a.data)) AS rank
FROM tblA AS a;

it is working, i can rank the data.
is A1 a temporary table or a shortcut name for select count(*) from tblA?
What does A1 stand for?

of course table a and b are not of the same length and sometimes a product
have more
entry in table a sometime less.

So i was expêcting the ranking to restart at 0 after each product
Your code is giving

prod data rank
x 1 0
x 2 1
x 3 2
y 1 3
y 2 4
z 1 5
z 2 6
z 3 7
z 4 8
and i would like to have
x 1 0
x 2 1
x 3 2
y 1 0
y 2 1
z 1 0
z 2 1
z 3 2
z 4 3
Is it possible?

What about your fll outer join?

best regards
pierre
 
Back
Top