Special Query

[Faraz A. Qureshi]

I have a table with following 3 fields:
Account_1, Account_2, Amount

Any query that would result into following layout?

Account (Containing Unique Names From BOTH Account_1, Account_2)
Amount_1 (Containing Amounts of Accounts Listed In Account_1)
Amount_2 (Containing Amounts of Accounts Listed In Account_2)

For instance, consider the following sample:

Account_1, Account_2, Amount
====== ======= =====
AccountA AccountB 1000
AccountC AccountA 2000
AccountB AccountC 3000

To be generating a result as follows:

Account, Amount_1, Amount_2
====== ======= =======
AccountA 1000 2000
AccountB 3000 1000
AccountC 2000 3000

Looking forward for your expertise!

 

[Douglas J. Steele]

SELECT Account, Sum(Amount_1), Sum(Amount_2)
FROM
(SELECT Account_1 As Account, Amount AS Amount_1, 0 AS Amount_2
FROM Table1
UNION
SELECT Account_2 AS Account, 0, Amount
FROM Table1) AS Subq
GROUP BY Account

 

[Faraz A. Qureshi]

XClent!
Thanx buddy!
--
Best Regards,

Faraz

SELECT Account, Sum(Amount_1), Sum(Amount_2)
FROM
(SELECT Account_1 As Account, Amount AS Amount_1, 0 AS Amount_2
FROM Table1
UNION
SELECT Account_2 AS Account, 0, Amount
FROM Table1) AS Subq
GROUP BY Account

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)




.

 

[John Spencer]

Question. Shouldn't you be using UNION ALL and not Union in the subquery?

If you had two records in with the same Account and amount in either section
of the subquery, you would eliminate the duplicates before attempting to sum them.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

Good point, John.

Hopefully Faraz sees this.

 

[Steve]

Your table is not correct! If you don't correct it now, you will probably
run into problems again down the road.
TblAccount
AccountID
Account
Amount

Steve
(e-mail address removed)

 

[Bob Quintal]

Your table is not correct! If you don't correct it now, you will
probably run into problems again down the road.
TblAccount
AccountID
Account
Amount

Steve
(e-mail address removed)

There was a TV show that had the hook of BZZZZZZT -- Wrong Answer!!!

That applies to you Steve. It applies to all your answers.

Why you persist in breaking the rules by advertising your "services"
when you continually post "Wrong Answer"s must be due to some
psychiatric issues.

The OP described a Junction Table used for storing from-to data for
transfers between accounts. The query he requested is a valid way of
summarizing such transactions.

Go away.
Don't go away angry,
just go away.

 

[Faraz A. Qureshi]

HE DID!
Thanx a lot guys!