Joining tables

[noccy]

Hello!

I have a table like this:

Account1, Account1Owner, Account2, Account2Owner,Account3, Account3Owner. All the account owners are blank

In another table i Have

Account, AccountOwner

How can I join theese two tables in one query so that the accounts get the right owners?

I see that i need some left and right joins, but the complexity is out of my league.....

tnx

noccy

 

[Kraj]

Could you provide more details? Right now it appears as if you are storing the same information in both tables.

 

[Mile-O]

Your proplem has arisen, moreso, due to the fact that your table design is not normalised.

 

[noccy]

Hi!

To specify the problem:

I get data from two sources.

The first set is a list of transactions with date, time, TransactionType, SenderAccount, ReceiverAccount, FinalAccount etc....

All accounts are unique Account numbers

The second set is a list of all the accounts, and their owners: AccountNumber, AccountOwner

Basicly i want to merge the two datasets in a query like :

date, time, TransactionType, SenderAccount, SenderAccountOwner ReceiverAccount, ReceiverAccountOwner, FinalAccount, FinalAccountOwner

I have done this when the first dataset had 2 accountnumbers in it:

SELECT T.Date , T.Time, T.SenderAccount, T1.AccountOwner, T.ReceiverAccount, tblAccountOwners.AccountOwner FROM tblAccountOwners RIGHT JOIN (tblTransactions AS T LEFT JOIN tblAccountOwners AS T1 ON T.SenderAccount=T1.AccountNumber) ON tblAccountOwners.AccountNumber=T.ReceiverAccount

This works fine, but how can i deal with it when i have 3 accounts in the first dataset...?

noccy