Select Query with Multiple Expressions

[Melagan]

Greetings,

Using Access 97. I believe that in order to accomplish my goal with this thread, I will have to use a select query with multiple expressions. I'm not even sure if that is possible, but assuming it is, here goes:

The database I am working with is basically an order tracking system. One thing it does is store three different fees in each record along with the date the order "closed" and the name of the sales person who got credit for the order.

I'm using the following SQL to calculate gross revenue on a report for our fiscal year 2005 (Dec 1, 2004 ~ Nov 30, 2005). As I have it now, it works perfectly.

SELECT Sum([Escrow Fee]+[Policy Premium]+[Misc Fees]) AS Total, DLookUp("[Last Name]","User Names","[First_Last] = '" & [Sales Rep] & "'") AS SortOrder, Count(Escrows.[Sales Rep]) AS OrderCount, Escrows.[Sales Rep]
FROM Escrows
WHERE (((Escrows.[Closing Date])>#11/30/2004# And (Escrows.[Closing Date])<#12/1/2005#))
GROUP BY DLookUp("[Last Name]","User Names","[First_Last] = '" & [Sales Rep] & "'"), Escrows.[Sales Rep]
HAVING (((Escrows.[Sales Rep])<>"House Account"));

The DLookUp in the SELECT clause is just for sorting purposes. The criteria in the HAVING clause just eliminates "House Accounts" from showing on the report.

The thing I am looking to add to this query is an expression to Divide the TOTAL field by the OrderCount field so I can get an "Average Total per Order" type of thing.

When I attempt this by trying to Sum([Total] / [OrderCount]) in the query, I get an error message saying: "Subqueries can not be used in the expression (Sum([Total]/[OrderCount])).

I've also tried similar things using unbound control boxes on forms and VB code behind the form, to no success, but I believe the solution is in SQL anyway. Any ideas?

 

[lespaul]

Have you tried:

Sum(([Escrow Fee]+[Policy Premium]+[Misc Fees])/Count(Escrows.[Sales Rep]))

You usually can't use an alias within the same query.

I'm sure if you detail the tables involved and your expected results, someone can help you get rid of the DLookup's in your query. Those can really slow things down.

And additionally, the HAVING clause is usually used to limit aggregates. Say you want to find all the clients who have more than 3 orders, in your HAVING clause you would indicate:

HAVING COUNT(*) > 3

in your case you can move the "House Account" criteria to the WHERE clause and it shouldn't make any difference.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Melagan]

I just tried adding the following to my SQL:

Sum(([Escrow Fee]+[Policy Premium]+[Misc Fees])/Count([Escrows].[Sales Rep])) AS OdrAvg

Error message now reads: "Can't have aggregate function in expression {sum clause / count}, so unless I made a mistake with your suggestion, I'm still stuck here.

I also moved the House Account criteria to the WHERE clause as suggested, so there is no longer a HAVING clause in my SQL.

As for the DLookUp issue - I agree that it slows things down big time. Here are some more details.

The "Escrows" table stores the first and last name of the Sales Rep involved in each record on one field. I have another table that breaks each user's name down into seperate fields, called "User Names". I have a field for First Name, Last Name, First_Last, and User Name.

I am using the DLookUp function below to put each persons LAST NAME into a column that I can use to sort the report alphabetically by last name.

DLookUp("[Last Name]","User Names","[First_Last] = '" & [Sales Rep] & "'"),

If I can accomplish this by using some kind of INNER JOIN clause in my SQL, that'd be great!

 

[PHV]

And this ?
Sum([Escrow Fee]+[Policy Premium]+[Misc Fees])/Count([Escrows].[Sales Rep]) AS OdrAvg


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

Yes indeed, that adjusted statement did work. Funny how parenthesis in the right places can make some difference. As of now, the query and the report will do exactly what I want, but now I am curious about how to avoid the DLookUp function. I'll play around with inner joining my escrows and user names tables to see what I can come up with. If someone can beat me to it, though, feel free; otherwise I'll post successful results hopefully in the next little bit. Thanks again for the help Leslie and PHV; this is such a wonderful forum!

 

[PHV]

SELECT Sum(E.[Escrow Fee]+E.[Policy Premium]+E.[Misc Fees]) AS Total
, U.[Last Name] AS SortOrder, Count(E.[Sales Rep]) AS OrderCount, E.[Sales Rep]
, Sum(E.[Escrow Fee]+E.[Policy Premium]+E.[Misc Fees])/Count(E.[Sales Rep]) AS OdrAvg
FROM Escrows E INNER JOIN [User Names] U ON E.[Sales Rep] = U.First_Last
WHERE E.[Closing Date] > #2004-11-30# AND E.[Closing Date] < #2005-12-01#
AND E.[Sales Rep] <> 'House Account'
GROUP BY U.[Last Name], E.[Sales Rep]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

PHV, you are NUTS man! I think SQL is your native tongue and English is like a second or third language. That works perfectly. Thank you! I hope I'm bright enough to be able to put that type of code into practicle use elsewhere.

 

[PHV]

I confirm that I'm not a native english speaker ...

 

[lespaul]

but is SQL your native language?

les

 

[PHV]

I have to confess: No.