Subquery and joined tables

[minchazo]

I'm working on a SQL query in Access 2003 to find the average value for a given "Procedure Name."

tblPickList has the list of procedures. tblPickListItem lists each supply and quantity used in the procedure, while tblSupplies has the cost for each item.

The following code is supposed to list the procedure and average cost for the procedure. For the SubTotal column, I sum the cost of all items being used and divide by the number of procedures with an identical name. I have verified that replacing "SAMPLE NAME" with any given procedure will give me the correct number to get the average, but I can't seem to find a variable that I can use to change the number of procedures for each new record in the query.

SELECT tblPickList.[Procedure Name], (Sum([Number Open]*[Cost]+[Number PRN]*[Cost]))/(SELECT Count(Physician) as PhysicianCount
FROM tblPickList GROUP BY [Procedure Name] HAVING [Procedure Name] = "SAMPLE NAME" ) AS SubTotal
FROM tblSupplies INNER JOIN (tblPickList INNER JOIN tblPickListItem ON tblPickList.PickListID = tblPickListItem.PickListID) ON tblSupplies.[Supply name] = tblPickListItem.[Supply name]
GROUP BY tblPickList.[Procedure Name];

(I would format this correctly, but I have no idea what the normal formatting conventions for SQL are).

[Atli]

Hi.

I have moved your thread from the MySQL forums over to the Access forums.
Please try to post your threads in the appropriate forum.

Thank you :)

[minchazo]

I figured it out! I guess this shows how little experience I have at SQL. I used the following as a query:

SELECT tblPickList.[Procedure Name], tblPickList.Physician, tblPickList.Location, Sum([Number Open]*[Cost]+[Number PRN]*[Cost]) AS SubTotal
FROM tblSupplies INNER JOIN (tblPickList INNER JOIN tblPickListItem ON tblPickList.PickListID = tblPickListItem.PickListID) ON tblSupplies.[Supply name] = tblPickListItem.[Supply name]
GROUP BY tblPickList.[Procedure Name], tblPickList.Physician, tblPickList.Location;

This lists each PickList and the total for each PickList. Then I built the next query off of the above query:

SELECT qryPickListCost.[Procedure Name], Avg(qryPickListCost.SubTotal) AS AvgOfSubTotal
FROM qryPickListCost
GROUP BY qryPickListCost.[Procedure Name];

This query groups the results by Procedure, averaging the cost of each line just like I wanted.