query help needed--part 2 1

[DotNetGnat]

The below is my table in the access db:

ProjectID|item|Station1|Station2|Length|Width1|Width2|AvgWidth|Area    |Comments
_________________________________________________________________________
   1     | 111|57.25   |57.50   |25.00 |20.00 |25.00 |22.50   |562.50  | Mowing 
   1     | 111|58.00   |58.63   |63.00 |17.00 |22.00 |19.50   |1228.50 | Mowing 
   1     | 111|189.90  |190.50  |60.00 |182.00|197.00|189.50  |11370.0| Mowing  
   1     | 111|190.50  |190.97  |47.00 |197.00|187.00|192.00  |9024.00 | Mowing 
   1     | 111|57.50   |58.00   |50.00 |25.00 |17.00 |21.00   |1050.0  | Mowing 
 
   1     | 222|57.25   |57.50   |25.00 |20.00 |25.00 |22.50   |562.50  |Clearing
   1     | 222|58.00   |58.63   |63.00 |17.00 |22.00 |19.50   |1228.50 |Clearing
   1     | 222|189.90  |190.50  |60.00 |182.00|197.00|189.50  |11370.00|Clearing
   1     | 222|190.50  |190.97  |47.00 |197.00|187.00|192.00  |9024.00 |Clearing
   1     | 222|57.50   |58.00   |50.00 |25.00 |17.00 |21.00   |1050.0  |Clearing

I want the following output:

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

   1     | 222|57.25  |25.00  |20.00  |22.50   |562.50  |Clearing
   1     | 222|57.50  |50.00  |25.00  |21.00   |1050.0  |
   1     | 222|58.00  |63.00  |17.00  |19.50   |1228.5  |
   1     | 222|58.63  |NULL   |22.00  |NULL    |NULL    |
   1     | 222|189.90 |60.00  |182.00 |189.50  |1050.0  |
   1     | 222|190.50 |47.00  |197.00 |192.00  |1050.0  | 
   1     | 222|190.97 |NULL   |187.00 |NULL    |NULL    |

PHV answered to the similar situation in the following thread thread701-1170068
i tried to adapt to this situation for each item for a project but was unsuccessful

Thanks

-DNG

 

[PHV]

You may try something like this:
SELECT ProjectID, item, Station1 AS Station, Length, Width1 AS Width, AvgWidth, Area, Comments
FROM yourTable A WHERE Station1 In (SELECT Min(Station1) FROM yourTable WHERE ProjectID=A.ProjectID AND item=A.item)
UNION ALL
SELECT ProjectID, item, Station1, Length, Width1, AvgWidth, Area, Null
FROM yourTable A WHERE Station1 Not In (SELECT Min(Station1) FROM yourTable WHERE ProjectID=A.ProjectID AND item=A.item)
UNION ALL
SELECT ProjectID, item, Station2, Null, Width2, Null, Null, Null
FROM yourTable AS A WHERE Station2 Not In (SELECT Station1 FROM yourTable WHERE ProjectID=A.ProjectID AND item=A.item)
ORDER BY 1,2,3

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

 

[DotNetGnat]

PHV,

The code is working fine...but it is popping up enter boxes asking to enter ProjectID and Item

-DNG

 

[PHV]

Which version of access ?
Works fine on mine (ac2003).
double check the field names and the aliases.

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

 

[DotNetGnat]

sorry...my bad...everything is working fine...

thanks

-DNG