 |
03-10-2004, 05:26 AM
|
|
Contributor
|
|
Join Date: Jun 2003
Location: Oslo, Norway
Posts: 602
|
|
Problem with query
|
Hello!
I have the folowing tables:
TblTransactions[Date, Time, SenderAccount, ReceiverAccount]
TblAccounts[Account, Owner]
How can I make a query like this: [Date, Time, SenderAccount, OwnerSenderAccount, ReceiverAccount, OwnerReceiverAccount]?
I want to do a lookup from both SenderAccount and ReceiverAccount, and find the owners...
noccy
|
|
03-10-2004, 06:15 AM
|
|
Steppe Walker
Retired Moderator
* Expert *
|
|
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
|
|
|
Hi,
How are you going to relate these two tables to each other if you don't have field (s) to make JOIN on?
Shurik.
|
__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell
"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'
|
03-10-2004, 06:18 AM
|
|
Junior Contributor
|
|
Join Date: Feb 2004
Location: Southampton, UK
Posts: 335
|
|
you want something like this
Code:
select t.Date, t.Time, t.SenderAccount, A1.Owner as OwnerSenderAccount, t.ReceiverAccount, A2.Owner as OwnerReceiverAccount
from TblTransactions t
inner join TblAccounts A1 on t.SenderAccount = A1.Account
inner join TblAccounts A2 on t.ReceiverAccount = A1.Account
|
|
03-10-2004, 06:19 AM
|
|
Contributor
|
|
Join Date: Jun 2003
Location: Oslo, Norway
Posts: 602
|
|
|
I was thinking about using dlookup in the query, but i am not sure....
The problem is that the transactions table contains two account numbers, and in the query i want to get both accountnumbers, and the account owners (from the accounts table)
noccy
|
|
03-10-2004, 06:40 AM
|
|
Junior Contributor
|
|
Join Date: Feb 2004
Location: Southampton, UK
Posts: 335
|
|
|
My solution will work because you are setting up two instances of the accounts table one for each of sender and receiver.
|
|
03-10-2004, 06:41 AM
|
|
Contributor
|
|
Join Date: Jun 2003
Location: Oslo, Norway
Posts: 602
|
|
|
Ok, the inner join seems to work fine, but it only shows med the records where the account number is present in the accounts table.
Is there a way to show all records from tblTransactions?
|
|
03-10-2004, 07:00 AM
|
 |
Centurion
|
|
Join Date: Feb 2004
Location: Alabama
Posts: 194
|
|
Quote:
|
Originally Posted by noccy
Ok, the inner join seems to work fine, but it only shows med the records where the account number is present in the accounts table.
Is there a way to show all records from tblTransactions?
|
Change the inner join to left outer joins and that should do it for ya...
Enjoy! |
__________________
There are no Stupid Questions, only Stupid People
|
03-10-2004, 10:08 AM
|
|
Junior Contributor
|
|
Join Date: Feb 2004
Location: Southampton, UK
Posts: 335
|
|
|
use an outer join rather than an inner join
|
|
03-10-2004, 11:31 AM
|
|
Contributor
|
|
Join Date: Jun 2003
Location: Oslo, Norway
Posts: 602
|
|
|
Here is what I ended up with:
SELECT t.Date, t.Time, t.SenderAccount, t2.Owner AS SenderAccountOwner, t.ReceiverAccount, tblAccounts.Owner AS ReceiverAccountOwner
FROM tblAccounts RIGHT JOIN (qryTransactions AS t LEFT JOIN tblAccounts AS t2 ON t.SenderAccount = t2.Owner) ON tblTAccounts.AccountNumber = t.ReceiverAccount;
Any views on that solution?
noccy
|
|
03-11-2004, 02:38 AM
|
|
Junior Contributor
|
|
Join Date: Feb 2004
Location: Southampton, UK
Posts: 335
|
|
|
My preference would be not to use a query in the sql but to use two left outer joins to tblAccounts. What is in the query definition (qryTransactions) ?
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
