Enbedded Select

[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!

 

[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

 

[CostReport101 via AccessMonster.com]

Hi Tom,

sorry I wasn't clear enough. Let me try again. I have one table that has
codes, sub codes and amounts, for sake of making it easier. What I would
like to see nice and neat is 3 different amount columns.

The result should show something like this:
code sub code amount 1(total) amount 2(salaries) amount 3
(pyscomp)
123 80000 60,000
123 60595 30,000 30,000
345 60600 28,000
28,000
678 70000 20,000
978 60400 60,000 60,000

etc.

so, depending on the sub code I only want to see those rows populated that
have a specific sub code and my first total column should not change.

I looks like it shouldn't be so complicated but when I run the query that I
described earlier, I get "at most one record can be shown".

Thanks!
Christa

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

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

[quoted text clipped - 13 lines]

Thanks!

 

[Tom Ellison]

Dear Christa:

Thanks for adding your name. Christa is much nicer than Cost.

You're going to be introduced now to correlated subqueries. This is perhaps
not so simple as you were expecting, probably mostly if it is quite new to
you.

SELECT T.[ACCT #] AS code, T.[SUB CODE] AS [sub code],
SUM(AMOUNT) AS [amount 1],
(SELECT SUM(T1.AMOUNT)
FROM tbl1
WHERE T1.[ACCT #] = T.[ACCT #]
AND T1.[sub code] = T.[sub code]
AND T1.[sub code] BETWEEN 60000 AND 60595)
AND T1.[sub code] <> 60100
AS [amount 2],

(SELECT SUM(T1.AMOUNT)
FROM tbl1
WHERE T1.[ACCT #] = T.[ACCT #]
AND T1.[sub code] = T.[sub code]
AND T1.[sub code] BETWEEN 60100 AND 64000)
AS [amount 3]
FROM (tbl1 T
GROUP BY [ACCT #], [SUB CODE]
ORDER BY [ACCT #], [SUB CODE]

This probably doesn't look so simple if it's all new to you. I expect
you'll find this stuff gets easier with a bit of study and practice.

I have the feeling I've done a lot of interpretation to figure out what you
want. You'd better test this out thoroughly before you use it. Better yet,
if you can learn to understand it you'll be better equipped to see if I did
what you really want.

Now, you said, "my first total column should not change." Yet in your
example, it seems it does change. Not sure what's up with that. Again,
it's a problem interpreting your message. In my code, it will change, like
your example. So, because of my problems interpreting your post, be
careful.

Hope this helped!

Tom Ellison

Hi Tom,

sorry I wasn't clear enough. Let me try again. I have one table that has
codes, sub codes and amounts, for sake of making it easier. What I would
like to see nice and neat is 3 different amount columns.

The result should show something like this:
code sub code amount 1(total) amount 2(salaries) amount
3
(pyscomp)
123 80000 60,000
123 60595 30,000 30,000
345 60600 28,000
28,000
678 70000 20,000
978 60400 60,000 60,000

etc.

so, depending on the sub code I only want to see those rows populated that
have a specific sub code and my first total column should not change.

I looks like it shouldn't be so complicated but when I run the query that
I
described earlier, I get "at most one record can be shown".

Thanks!
Christa

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

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

[quoted text clipped - 13 lines]

Thanks!

 

[CostReport101 via AccessMonster.com]

Hi Tom,

I guess it would be easier if access would be voice activated and I can just
tell it what to do.

In regards to the first column which is the total column, this should not
change. The consecutive columns change. I always want the total column,
then one column that shows "salaries" if a salary sub code is there, if not,
there shouldn't be anything showing. The same with the next column, if a
pyscomp sub code is there, show me the amount, if not, don't show me anything.
But always show me the total in column one. In my pretty little brain this
doesn't "sound" so complicated.

I'll try your query and adjust as needed. Maybe I have to dig out my VB
programming experience. It's somewhere back there........

Thanks!
Christa

Dear Christa:

Thanks for adding your name. Christa is much nicer than Cost.

You're going to be introduced now to correlated subqueries. This is perhaps
not so simple as you were expecting, probably mostly if it is quite new to
you.

SELECT T.[ACCT #] AS code, T.[SUB CODE] AS [sub code],
SUM(AMOUNT) AS [amount 1],
(SELECT SUM(T1.AMOUNT)
FROM tbl1
WHERE T1.[ACCT #] = T.[ACCT #]
AND T1.[sub code] = T.[sub code]
AND T1.[sub code] BETWEEN 60000 AND 60595)
AND T1.[sub code] <> 60100
AS [amount 2],

(SELECT SUM(T1.AMOUNT)
FROM tbl1
WHERE T1.[ACCT #] = T.[ACCT #]
AND T1.[sub code] = T.[sub code]
AND T1.[sub code] BETWEEN 60100 AND 64000)
AS [amount 3]
FROM (tbl1 T
GROUP BY [ACCT #], [SUB CODE]
ORDER BY [ACCT #], [SUB CODE]

This probably doesn't look so simple if it's all new to you. I expect
you'll find this stuff gets easier with a bit of study and practice.

I have the feeling I've done a lot of interpretation to figure out what you
want. You'd better test this out thoroughly before you use it. Better yet,
if you can learn to understand it you'll be better equipped to see if I did
what you really want.

Now, you said, "my first total column should not change." Yet in your
example, it seems it does change. Not sure what's up with that. Again,
it's a problem interpreting your message. In my code, it will change, like
your example. So, because of my problems interpreting your post, be
careful.

Hope this helped!

Tom Ellison

Hi Tom,

[quoted text clipped - 78 lines]