Enbedded Select

  • Thread starter CostReport101 via AccessMonster.com
  • Start date
C

CostReport101 via AccessMonster.com

How can I get below query to work? Objective: 1st colmn = Total Amt, 2nd
colmn = amt of specific sub code, 3rd colmn = amt for yet another set of
codes. And records without amts should stay empty.

SELECT [tbl1].[ACCT #], [tbl1].[SUB CODE], [tbl1].DESCRIPTION,
[tbl1].AMOUNT AS TOTAL,
(select [tbl1].[AMOUNT] FROM [tbl1] WHERE [sub code] between 60000 and
60595 and [sub code] <>
60100
and [sub code] <> 64000) AS Salaries,
(select [tbl1].[AMOUNT] FROM [tbl1] WHERE [sub code] = 60100 or [sub
code] = 64000) AS PysComp
FROM [tbl1];

Or should I use a different method all together?

Thanks!
 
T

Tom Ellison

Dear Cost:

I assume you want the [sub code]s for the listed account, and that there may
be more than one of them.

I believe what you should use would be a self-join and some filtering. Like
this:

SELECT T.[ACCT #], T.[SUB CODE],
T.[DESCRIPTION], T.AMOUNT AS TOTAL,
T1.AMOUNT AS Salaries, T2.AMOUNT AS PysComp
FROM (tbl1 T
INNER JOIN tbl1 T1 ON T1.[ACCT #] = T.[ACCT #])
INNER JOIN tbl1 T2 ON T2.[ACCT #] = T.[ACCT #]
WHERE T1.[sub code] BETWEEN 60000 AND 60595
AND T1.[sub code] <> 60100
AND T2.[sub code] BETWEEN 60100 AND 64000

There are no filters placed on the first instance of the table. I expect
you intended one, but I don't know what it is. That is, the ACCT #, SUB
CODE, DESCRIPTION, and TOTAL columns probably should come up just once, for
some specific [sub code] value, not for every [sub code] value. Of course,
I don't know what [sub code] this is. With this assumption, and using [sub
code] = 12345 for some "master sub code" value, add this to the above:

AND T.[sub code] = 12345

If there are 3 rows for an ACCT # that have sub codes between 60000 and
60595, and 5 more between 60100 and 64000, then the query would produce 3 *
5 = 15 rows for that ACCT # (after the above filter is added. Without the
above filter you would get 15 * 16 = 240 rows or more if you have sub codes
other than 12345, 60000-60595, and 60100-64000). I don't know if you wanted
that, or just some totals of the 2 ranges of sub codes.

Without additional guidance from you, that's the best I can see to do for
now.

Tom Ellison
 
Top