query help 1

[DotNetGnat]

Guys, this is my original table:

Station1|Station2|Length|Width1|Width2|AvgWidth|Area 
_________________________________________________________________
57.25   |57.50   |25.00 |20.00 |25.00 |22.50   |562.50
58.00   |58.63   |63.00 |17.00 |22.00 |19.50   |1228.50
189.90  |190.50  |60.00 |182.00|197.00|189.50  |11370.00
190.50  |190.97  |47.00 |197.00|187.00|192.00  |9024.00 
57.50   |58.00   |50.00 |25.00 |17.00 |21.00   |1050.0

i want the output as:

Station|Length |Width  |AvgWidth|Area
____________________________________________
57.25  |25.00  |20.00  |22.50   |562.50
57.50  |50.00  |25.00  |21.00   |1050.0
58.00  |63.00  |17.00  |19.50   |1228.5
58.63  |NULL   |22.00  |NULL    |NULL
189.90 |60.00  |182.00 |189.50  |1050.0
190.50 |47.00  |197.00 |192.00  |1050.0
190.97 |NULL   |187.00 |NULL    |NULL

1. The series should be ordered
2. if there is break in the series, i want to get a NULL for
the length, AvgWidth and Area

Thanks

-DNG

 

[PHV]

Something like this ?
SELECT Station1 As Station, Length, Width1 As Width, AvgWidth, Area FROM yourTable
UNION ALL
SELECT Station2, Null, Width2, Null, Null FROM yourTable
WHERE Station2 Not In (SELECT Station1 FROM yourTable)
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DotNetGnat]

Thanks PHV. Worked like charm.

-DNG

 

[DotNetGnat]

PHV...another question on this...

i have a field called comments in the original table...and this comment is same for the single continuous series...

Station1|Station2|Comments
_______________________
57.25 |57.50 |blah
58.00 |58.63 |blah
189.90 |190.50 |foo
190.50 |190.97 |foo
57.50 |58.00 |blah

how can i show only once on the first instance in my result...i mean

Station|Comments
____________________________________________
57.25 |blah
57.50 |
58.00 |
58.63 |
189.90 |foo
190.50 |
190.97 |

thanks

-DNG

 

[PHV]

Typed, not tested:
SELECT Min(Station1) AS Station, Comments FROM yourTable
GROUP BY Comments
UNION ALL
SELECT Station1, Null FROM yourTable
WHERE Station1 Not In (SELECT Min(Station1) FROM yourTable GROUP BY Comments)
UNION ALL
SELECT Station2, Null FROM yourTable
WHERE Station2 Not In (SELECT Station1 FROM yourTable)
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DotNetGnat]

hmm...let me try to combine both now...i wanted all the fields besides the comment field appearing only once...

will get back to you...

-DNG

 

[DotNetGnat]

PHV,

i tried adding columns one by one...something like this:

SELECT Min(Sta_Begin) AS Station,IIF(Rt_Lt="NA.","",Rt_Lt) as RightLeft, Line,calcLength,
Comments FROM  ItemMeas_AcreByStation
GROUP BY IIF(Rt_Lt="NA.","",Rt_Lt), Line,calcLength,Comments

UNION ALL

SELECT Sta_Begin as Station,  IIF(Rt_Lt="NA.","",Rt_Lt) as RightLeft, Line,calcLength,
Null FROM  ItemMeas_AcreByStation
WHERE Sta_Begin NOT In (SELECT Min(Sta_Begin) FROM  ItemMeas_AcreByStation GROUP BY IIF(Rt_Lt="NA.","",Rt_Lt), Line,calcLength,Comments)

UNION ALL SELECT Sta_End as Station, IIF(Rt_Lt="NA.","",Rt_Lt) as RightLeft,  Line,calcLength,
Null FROM  ItemMeas_AcreByStation
WHERE Sta_End Not In (SELECT Sta_Begin FROM  ItemMeas_AcreByStation)

ORDER BY 1;

once i added the calcLength column my comments column was screwed up...comments show up in all the rows expect the last row of the series instead of showing only once for the min station of the series...like its reversed...

where did i go wrong...

thanks

-DNG

 

[PHV]

Perhaps this ?

SELECT Sta_Begin AS Station, IIf(Rt_Lt = 'NA.', '', Rt_Lt) AS RightLeft, Line, calcLength, Comments
FROM ItemMeas_AcreByStation
WHERE Sta_Begin In (SELECT Min(Sta_Begin) FROM ItemMeas_AcreByStation GROUP BY Comments)
UNION ALL
SELECT Sta_Begin, IIf(Rt_Lt = 'NA.', '', Rt_Lt), Line, calcLength, Null
FROM ItemMeas_AcreByStation
WHERE Sta_Begin Not In (SELECT Min(Sta_Begin) FROM ItemMeas_AcreByStation GROUP BY Comments)
UNION ALL
SELECT Sta_End, IIf(Rt_Lt = 'NA.', '', Rt_Lt), Line, calcLength, Null
FROM ItemMeas_AcreByStation
WHERE Sta_End Not In (SELECT Sta_Begin FROM ItemMeas_AcreByStation)
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DotNetGnat]

Yes PHV,

That did the trick...

You are Amazing...Kudos to the whiz

thanks

-DNG