SPECIAL QUERY

[Faraz A. Qureshi]

HI ALL!

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!

 

[John Spencer]

If you don't have a table of the unique accounts then you will need a query to
get that as the first step.

SELECT Account_1 as Account
FROM SomeTable
UNION
SELECT Account_2
FROM SomeTable

Now you can write this query, assuming that the table and field names follow
the naming conventions of consisting of only letters, numbers, and the
underscore character.

SELECT Account, A1.Amount, A2.Amount
FROM (qUnionAccounts LEFT JOIN
(SELECT Account_1, Amount
FROM SomeTable) as A1
ON qUnionAccounts = A1.Account_1)
LEFT JOIN
(SELECT Account_2, Amount
FROM SomeTable) as A2
ON qUnionAccounts = A2.Account_2

Another option would be to use a union all query and then run a crosstab
against that

SELECT Account_1 as Account, Amount, "Amount1" as TheAmount
FROM SomeTable
UNION ALL
SELECT Account_2 as Account, Amount, "Amount2" as TheAmount
FROM SomeTable

Now use that in a crosstab query
TRANSFORM Sum(Amount)
SELECT Account
FROM qUnionQuery
GROUP BY Account
PIVOT TheAmount

If you need further instructions on how to build these queries post back.

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