Use result of one query for criteria of another

[nyquilx]

Good morning everyone,



I have one table
iadjustments / dDate / iRepID
.3 1/1/10 9000
.2 1/1/10 9000

(SELECT Sum(tblcalladjustments.tTimeadjusted) AS SumOftTimeadjusted FROM tblcalladjustments WHERE (((tblcalladjustments.ddate)=[Enter Date]) AND ((tblcalladjustments.iagentID)=[Enter Rep])) AS Adjustments

Second Query

SELECT [tblagentcallstats]![iavail]+[tblagentcallstats]![italkavail]+[tblagentcallstats]![iovfl]+[tblagentcallstats]![iagentovfl]+[tblagentcallstats]![ientper]+[tblagentcallstats]![iintper]+[tblagentcallstats]![Iextper]+[tblagentcallstats]![iintper2]+[tblagentcallstats]![iotherper] AS [Total Avail %]
FROM tblagentcallstats
WHERE (((tblagentcallstats.ddate)=[Enter Date]) AND ((tblagentcallstats.iagentid)=[Enter Rep]));

Now i need to combine them

[Total Avail %] - [Adjustments] AS Actual

Any ideas?

[Robertt]

Start by adding your primary key(s) to the select statement of each of your two queries. Next join them on the primary keys you added above. I.e.


select * from (select pk1, pk2 ... first query) q1
right join (select pk1, pk2,... second query) q2
on q1.pk1=q2.pk1 and q1.pk2=q2.pk2.

Next adjust the "select *" portion to include your formula that subtracts your to calculated values

HTH,

Robert

[TheShabz]

NEVERMIND!!! I noticed those are all calculated fields. My apologies. I'll leave the comment below anyway for anyone it may benefit


Also, I'd suggest using & as your concatenator instead of +. + works fine if they are all strings but if you get a number field thrown in there, it can mess things up. It will actually add them together instead of putting them together.

example
11+11=22
11&11=1111

by convention most people I know use & always.