Can someone explain me something about my SQL query...

[daicamad]

I have this:
SELECT UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName,
Sum(SoldItems.Commision) AS TotalCommission, Sum(TIP.TipAmt) AS
TotalTip,(SELECT Sum(SoldItems.Commision) AS MyCommission FROM
mytable)
FROM
(SELECT * FROM ((SoldItems INNER JOIN UserInfo ON
SoldItems.EmpID=UserInfo.EmpID) INNER JOIN [Transaction] ON
SoldItems.TransID=Transaction.TransID) INNER JOIN TIP ON
(Transaction.TransID=TIP.TransID) AND (UserInfo.EmpID=TIP.EmpID)
WHERE ((DateValue(Transaction.Time)) Between #8/2/2007# And
#8/30/2007#) ) AS mytable
GROUP BY UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName;

and Access "cannot see" 'my table' in the query (SELECT
Sum(SoldItems.Commision) AS MyCommission FROM mytable). I already
reference it in the the subquery below it!!
Thanks

(the query may look a little odd as I just try out the query)

[Salad]

daicamad wrote:

I have this:
SELECT UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName,
Sum(SoldItems.Commision) AS TotalCommission, Sum(TIP.TipAmt) AS
TotalTip,(SELECT Sum(SoldItems.Commision) AS MyCommission FROM
mytable)
FROM
(SELECT * FROM ((SoldItems INNER JOIN UserInfo ON
SoldItems.EmpID=UserInfo.EmpID) INNER JOIN [Transaction] ON
SoldItems.TransID=Transaction.TransID) INNER JOIN TIP ON
(Transaction.TransID=TIP.TransID) AND (UserInfo.EmpID=TIP.EmpID)
WHERE ((DateValue(Transaction.Time)) Between #8/2/2007# And
#8/30/2007#) ) AS mytable
GROUP BY UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName;

and Access "cannot see" 'my table' in the query (SELECT
Sum(SoldItems.Commision) AS MyCommission FROM mytable). I already
reference it in the the subquery below it!!
Thanks

(the query may look a little odd as I just try out the query)

You seem to be selecting fields from UserInfo but if there's any table
available for referencing I'd think it would be MyTable as you are
Froming from MyTable and referencing UserInfo.

[Ben]

On Aug 17, 8:28 am, daicamad <[e-mail]:

I have this:
SELECT UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName,
Sum(SoldItems.Commision) AS TotalCommission, Sum(TIP.TipAmt) AS
TotalTip,(SELECT Sum(SoldItems.Commision) AS MyCommission FROM
mytable)
FROM
(SELECT * FROM ((SoldItems INNER JOIN UserInfo ON
SoldItems.EmpID=UserInfo.EmpID) INNER JOIN [Transaction] ON
SoldItems.TransID=Transaction.TransID) INNER JOIN TIP ON
(Transaction.TransID=TIP.TransID) AND (UserInfo.EmpID=TIP.EmpID)
WHERE ((DateValue(Transaction.Time)) Between #8/2/2007# And
#8/30/2007#) ) AS mytable
GROUP BY UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName;

and Access "cannot see" 'my table' in the query (SELECT
Sum(SoldItems.Commision) AS MyCommission FROM mytable). I already
reference it in the the subquery below it!!

Thanks

(the query may look a little odd as I just try out the query)

Salad is right, you can't select userinfo.EmpID because by the time
you select it the field has become mytable.EmpID.

This would be easier to see if you specified fields instead of * in
your subquery.

[daicamad]

You guys are correct. I read about this on standard SQL, but if I do
this it work fine (may be something Access does not follow standard)
SELECT UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName,
Sum(SoldItems.Commision) AS TotalCommission, Sum(TIP.TipAmt) AS
TotalTip FROM
(SELECT * FROM ((SoldItems INNER JOIN UserInfo ON
SoldItems.EmpID=UserInfo.EmpID) INNER JOIN [Transaction] ON
SoldItems.TransID=Transaction.TransID) INNER JOIN TIP ON
(Transaction.TransID=TIP.TransID) AND (UserInfo.EmpID=TIP.EmpID)
WHERE ((DateValue(Transaction.Time)) Between #8/2/2007# And
#8/30/2007#) ) AS mytable
GROUP BY UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName;

But when I put ,(SELECT Sum(SoldItems.Commision) AS MyCommission FROM
mytable) as a field, it does not see mytable at all.
But if I use mytable.UserInfo.EmpID, it's ok

I am now even more baffle!!

[daicamad]

I think I understand this now. Correct me if I am wrong. The field
(SELECT Sum(SoldItems.Commision) AS MyCommission FROM
mytable) was calculate first; therefore, at that time, there is no
'mytable' has been referenced yet! It was computed first because it's
an subquery. The mytable.UserInfo.EmpID field refer to an actual field
that will computed after the query, thus it's ok.

And why User.EmpID alone still work then I just guess Access assume it
from 'mytable' as there is nothing else there beside it.

If I am right, it still does not solve my problem. There is another
field called IsRetailItem in SoldItems table. I want to include 2
fields in my report that sum up the commision if the IsRetailItem =
TRUE and IsRetailItem = FALSE . So I intend to include fields (SELECT
Sum(SoldItems.Commision) AS RetailCommission FROM mytable WHERE
SoldItems.IsRetailItem =TRUE), (SELECT Sum(SoldItems.Commision) AS
NonretailCommission FROM mytable WHERE SoldItems.IsRetailItem =FALSE)
Any thoughts? :^)

[John Winterbottom]

daicamad wrote:

You guys are correct. I read about this on standard SQL, but if I do
this it work fine (may be something Access does not follow standard)

AFAIK even in "Standard" SQL you cannot reference a materialized table
from within a sub-select of the outer query.

[John Winterbottom]

Post your simplified table structure with some sample data and the
output you need

daicamad wrote:

I think I understand this now. Correct me if I am wrong. The field
(SELECT Sum(SoldItems.Commision) AS MyCommission FROM
mytable) was calculate first; therefore, at that time, there is no
'mytable' has been referenced yet! It was computed first because it's
an subquery. The mytable.UserInfo.EmpID field refer to an actual field
that will computed after the query, thus it's ok.

And why User.EmpID alone still work then I just guess Access assume it
from 'mytable' as there is nothing else there beside it.

If I am right, it still does not solve my problem. There is another
field called IsRetailItem in SoldItems table. I want to include 2
fields in my report that sum up the commision if the IsRetailItem =
TRUE and IsRetailItem = FALSE . So I intend to include fields (SELECT
Sum(SoldItems.Commision) AS RetailCommission FROM mytable WHERE
SoldItems.IsRetailItem =TRUE), (SELECT Sum(SoldItems.Commision) AS
NonretailCommission FROM mytable WHERE SoldItems.IsRetailItem =FALSE)
Any thoughts? :^)

[daicamad]

On Aug 17, 12:46 pm, John Winterbottom <[e-mail]:

Post your simplified table structure with some sample data and the
output you need

SoldItems Table structure:

SoldItemTableIndex
UPC
TransID
EmpID
Commision
IsRetailItem

Data:

154, 56789, 07220104211-01, 1000, $9.00, FALSE
155, 56789, 07220113322-01, 4627, $1.00, TRUE
156, 74145, 07220113322-01, 4627, $2.00, FALSE
-----------------------------
UserInfo Table structure

EmpID
FirstName
LastName

Data:
1000, Terri, G
4627, Suzi, W
-----------------------------
Transaction table structure

TransID
[Time]

Data:
07220104211-01, 8/8/2007 10:42:11 AM
07220113322-01, 8/8/2007 11:33:22 AM

(Let ignore the TIP for now to simplify the database)
The output I need would be

FirstN LastN TotalCommision RetailCommision NonRetailCommission
------------------------------------------------------------------------------------
Terri G $9
$0 $9
Suzi W $3
$1 $2

[daicamad]

I think I found the solution. It's not elegant, so if anyone come with
a better, more SQL related solution, I still like to see it

This only work with Access

SELECT UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName,
Sum(SoldItems.Commision) AS TotalCommission,
Sum(IIF(SoldItems.IsRetailItem=TRUE,SoldItems.Comm ision,0))
FROM
(SELECT * FROM ( (SoldItems INNER JOIN UserInfo ON
SoldItems.EmpID=UserInfo.EmpID) INNER JOIN [Transaction] ON
SoldItems.TransID=Transaction.TransID)
WHERE ((DateValue(Transaction.Time)) Between #8/2/2007# And
#8/30/2007#) ) AS mytable
GROUP BY UserInfo.EmpID, UserInfo.FirstName, UserInfo.LastName;

I used IFF function to filter out whether the sold items are retail
items or not. Probbaly now I don't need mytable anymore.