help in query

[Carlos]

Hello can someone help me?

i have this sample table


| Name | Pos | start | end |
| A | 0 | 1 | 2 |
| A | 1 | 3 | 4 |
| B | 0 | 1 | 2 |
| B | 1 | 3 | 4 |

i try to get this view

| Name | start0 | end0 | start1 | end1 |
| A | 1 | 2 | 3 | 4 |
| B | 1 | 2 | 3 | 4 |

if this possible? thank's

 

[Michel Walsh]

SELECT a.Name,
a.start AS Start0,
a.end AS End0,
b.start AS Start1,
b.end AS End1

FROM (SELECT * FROM myTable WHERE pos=0) AS a
INNER JOIN
(SELECT * FROM myTable WHERE pos=1) As b
ON a.name=b.name



That would list only 'names' that have a record with pos=0 and a record
with pos=1

Hoping it may help,
Vanderghast, Access MVP

 

[Carlos]

Thanks a lot Michel, you help me a lot

SELECT a.Name,
a.start AS Start0,
a.end AS End0,
b.start AS Start1,
b.end AS End1

FROM (SELECT * FROM myTable WHERE pos=0) AS a
INNER JOIN
(SELECT * FROM myTable WHERE pos=1) As b
ON a.name=b.name



That would list only 'names' that have a record with pos=0 and a record
with pos=1

Hoping it may help,
Vanderghast, Access MVP