Nested select statement

[flaviooooo]

Hey,

I'm trying the following:

I have a query containing all cylinders of a code, like this:

A 1 123
A 2 124
A 3 256
B 1 125
B 2 222
C 1 126
C 2 127
C 3 128

I have another query with detailed info per code:

A CodeA Customer1
B CodeB Customer2
C CodeC Customer3

Now I would like to add 3 columns to the second query, CYL1, CYL2, CYL3

So the result should be for Code A:

A CodeA Customer1 123 124 256

I tried the following:

Select Code, Name, Customer, (select cyl from query1 where CODE like '" &

 & "' and sequence = 1), (select cyl from query1 where CODE like '" & [CODE] & "' and sequence = 2), (select cyl from query1 where CODE like '" & [CODE] & "' and sequence = 3))

But my CYL-columns remain empty...

Any idea?

 

[HarleyQuinn]

Return code as well in your three sub-queries (also alias the returned CYL), join to the three based on code in your outer query (in the FROM statement) and return your three aliased values in the SELECT.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[dhookom]

You might be able to use code like the concatenate function found at faq701-4233.

Duane
Hook'D on Access
MS Access MVP