COMBINE FIELD FROM 2 RECORDS INTO ONE 1

[jadams0173]

Hello all,

I've got a query that returns resluts like this.

MO PASS PRIMLINE
12345 1 B1
12345 2 C2

What I would like to get back is this:
MO PRIMLINE
12345 B1/C2

With the only condition being that the PRIMLINE be configuered as such 1/2 so the line where pass = 1 is always first.

Can this be done in one query? If not any suggestions?

 

[randy700]

This might help...

faq701-4233

Randy

 

[PHV]

SELECT A.MO, A.PRIMLINE & '/' & B.PRIMLINE AS [PRIMLINE1/2]
FROM yourTable AS A INNER JOIN yourTable AS B ON A.MO = B.MO
WHERE A.PASS = 1 AND B.PASS = 2

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

 

[jadams0173]

PHV that was what I was looking for. I'm going to try to take this and adapt it to my append query! Thanks again!

 

[jadams0173]

I've ran into a slight problem. Not all of the data has a pass 1 and 2. Some have only a Pass 1 or a Pass 0.

I tried to modify the where clause of the query PHV (WHERE A.PASS = 1 AND B.PASS = 1) provided but now I get 2 records if the records has a Pass = 1 and pass =2.

Data:

MO PASS PRIMLINE
12345 1 B1
12345 2 C2
23456 0 n-a
78945 1 B6
85426 1 B9

What I would like to get back is this:
MO PRIMLINE
12345 B1/C2
23456 N-A/N-A
78945 B6/N-A (OR BLANK)
85426 B9/N-A (OR BLANK)

 

[jadams0173]

I'm still trying to figure this out and am very much stuck if anyone has any ideas.

 

[PHV]

SELECT A.MO, A.PRIMLINE & '/' & Nz(B.PRIMLINE,'N/A') AS [PRIMLINE1/2]
FROM yourTable AS A LEFT JOIN (
SELECT MO, PRIMLINE FROM yourTable WHERE PASS = 2
) AS B ON A.MO = B.MO
WHERE A.PASS < 2

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

 

[jadams0173]

PHV,

Again, spot on!

I'm trying to understand the query, espically B. "B" will only have the records where there is a pass=2....OH. The light just came on!! "B" only has MO's with a pass = 2 hence the nz in [primeline1/2]. The where (a.pass < 2) then that takes into account all of the passes that = 1 or 0. So even if B has no records then I will get all the 0's and 1's for pass 1 and N/A for pass 2.

Ironic thing is I have pretty much that same logic written on paper. Just wasn't smart enough to code it!!

I can't thank you enough. I wish I could give you another star!