Query - Can a Union query solve this problem

[Deee]

Please I need help! How can i write a query the gives me this result below

Trans no Fund1    Per  Fund2   Per
2           1   10%    2      20%

From my base table that looks like this

Trans no   Funds  Per
2             1   10%
2             2   20%

Many thanks
Dapo

[Anas Mosaad]

I don't believe you need a union. You need a join to implement this one -assuming that funds can be only 1 or 2. A query like this one would solve it for you:

select t1.Trans_no, t1.Fund, t1.Per, t2.Fund, t2.Per
from (select Trans_no, Fund, Per from Trans where Fund = 1) t1 left outer join
    (select Trans_no, Fund, Per from Trans where Fund = 2) t2 on t1.Trans_no = t2.Trans_no
 

I didn't run the above query but it should give you some guidance on how to implement it.

[zmbd]

Well Dee,
From the extremely small amount of data, I'm going to guess that your database isn't normalized:
A Tutorial for Access
Database Normalization and Table Structures.

I haven't taken a close look at Mosaad's SQL; however, it's missing a closing ";" and I don't see how it would provide the format you desire.

I'm on my way out the door (morings are so busy) to take kids to school; however, I hope to take a closer look in a few hours; however, it would appear to me that crosstab-query or a pivot-table would be somethings to take a look at for the solution.

[NeoPa]

Access's Jet SQL doesn't fuss over semi-colons (;) Z. That said, it wouldn't (as far as I'm aware) recognise the "OUTER" part of "LEFT OUTER JOIN".

Fundamentally though, although this looks to fit a Cross-Tab query perfectly, the concept that Anas has outlined would also work perfectly well.