need help for this query

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

I have a query result looks like this:

Sect ID
1418 7002
8018 7002
3355 7045
etc. etc.

based on above, I want to create a query with this result:
Sect ID Also
1418 7002 8018
8018 7002 1418
3355 7045
etc. etc.

I don't mind to create another intermidiate query to generate above result.

Any help is appreciated.
 
they all text field with 4 charactor long.
and same ID can only appear either once or twice. I want to show if ID
appear twice, what's the other 'sect'
 
I have a query result looks like this:

Sect ID
1418 7002
8018 7002
3355 7045
etc. etc.

based on above, I want to create a query with this result:
Sect ID Also
1418 7002 8018
8018 7002 1418
3355 7045
etc. etc.

I don't mind to create another intermidiate query to generate above result.
Click to expand...

A Self Join will work here:

Select A.Sect, A.ID, B.Sect AS Also
FROM yourtable AS A
INNER JOIN yourtable AS B
ON A.ID = B.ID
WHERE B.Sect > A.Sect;

I use > rather than <> (not equal) because you say that there will
only be two records (are you *certain* of that!?) and the greater-than
will find each pair only once.

John W. Vinson[MVP]
 
We are almost there.
Yes, I'm very certain records can only applear max. twice. So, <> is working
fine. However, how about those records appear only once? For your code, ID
7045 would not show. I want that record to show as well just 'ALSO' column
is blank for this record.
 
Yes, I'm very certain records can only applear max. twice. So, <> is working
fine. However, how about those records appear only once? For your code, ID
7045 would not show. I want that record to show as well just 'ALSO' column
is blank for this record.
Click to expand...

Ah! Misunderstood. The not equals should cause a problem in that
you'll find each pair twice: e.g.

Sect ID
1418 7002
8018 7002

will give both:

ID Sect Also
7002 1418 8018
7002 8018 1418

If that's what you want, fine.

If you want to see the solos as well, use a Left Outer Join:

Select A.Sect, A.ID, B.Sect AS Also
FROM yourtable AS A
LEFT JOIN yourtable AS B
ON A.ID = B.ID
WHERE B.Sect > A.Sect
OR B.Sect IS NULL;

Use <> if you want to see each pair twice, with Sect and Also swapping
roles.

John W. Vinson[MVP]
 
I used <> and leftjoin and added or b.sect is null (exactly as your code)
ID Sect Also
7002 1418 8018
7002 8018 1418
is good but single (ID 7045) still not show.
 
Back
Top