SQL in unbound text box

[isaacski]

Hi All,
I have a doozy for you. I have a query that tells me two things. Take the following:
AnalystID AnalystCompleted Expr1
5 5 0
5 4 1
5 5 0
5 5 0

This is a look at an assignment, who it was originally assigned to (AnalystID), and who actually completed it (AnalystCompleted). Expr 1, counts the number of times the two columns don't match.

This tells me two things depending on how I look at it. If I were to throw this in a report and group by Analyst ID, it would tell me how many times someone else completed an Analysts assignment. If I grouped by AnalystCompleted, it would tell me how many times an analyst has done another person's assignment. In all, by analyst, the amount of assignments done BY others, and amount done FOR others.

I do NOT want to create two separate sub reports just to tell me this so that I can use the totals in calculations on a main report. I was attempting to do this on the query level but that doesn't seem possible either. I then thought perhaps a defined Function for SQL SELECT... SUM... FROM... GROUP BY... but I really have no idea how to make it work.

Any ideas? I hope this was clear enough to understand.. Let me know if you need more detail...

Thanks in advance for the help!

K

 

[CJ_London]

You can't use SQL directly in a control - you can either use the domain functions (DSum, DCount, etc) or run some VBA using recordsets to do the calculation and then assign the outcome to a control

 

[plog]

I was attempting to do this on the query level but that doesn't seem possible either.

That's music to my ears. If you want to summon me to a thread, post that sentence. So here's how you do what you want in a query--it's going to take a sub-query:

In the below queries be sure to replace 'YourTableName' with the name of your actual table.

SELECT YourTableName.AnalystCompleted AS CompletedBy, Sum(IIf([AnalystID]<>[ANalystCompleted],1,0)) AS Completed_ForAnother
FROM YourTableName
GROUP BY YourTableName.AnalystCompleted;

Name the above query 'sub_AnalystCompleted'. It gets all analysis completed by an analyst that they were not assigned.

Now here's the money query:

SELECT YourTableName.AnalystID, Count(YourTableName.AnalystID) AS Assigned, Sum(IIf([analystCompleted]=[AnalystID],1,0)) AS Completed_TheirOwn, Sum(IIf([analystCompleted]<>[AnalystID],1,0)) AS Completed_ByAnother, sub_AnalystCompleted.Completed_ForAnother
FROM YourTableName LEFT JOIN sub_AnalystCompleted ON YourTableName.AnalystID = sub_AnalystCompleted.CompletedBy
GROUP BY YourTableName.AnalystID, sub_AnalystCompleted.Completed_ForAnother;

That will get you the analyst, the number assigned, the number of their own completed, the number of theirs completed by someone else and the number they completed for someone else.

 

[isaacski]

eeek! Thank you Thank you Plog!!!! Clearly I should not doubt SQL language so hastily. This has seriously opened my eyes to the coolness of queries. I really want to go through all of the other reports now haha. Sadly, I'm the only data nerd in my office so it will have to be a cyber celebration for me