Jolien .A Jolien .A - 5 months ago 17
SQL Question

ACCESS SQL: Combining SELECT and USER INPUT

I'm creating a form in ACCESS where users can select a commodity out of a list of possible commodities, and then a query calculates the average price of the selected commodity.

The input field for the user is a list (List147). Let's say the user selects Copper, then I want the average to be returned for Copper. The prices of all commodities are in a table called CommMaterial. The snip below shows what the table looks like.

enter image description here

I'm fairly new at SQL and am not sure how to code this. It appears as if the select statement needs to be dynamic, but I don't know how to do this. I envision something like this:

SELECT AVG(CommMaterial.[Forms]![NameForm]![List147])
FROM CommMaterial;

Answer

To keep from dynamic sql and VBA, You could use some SQL to get your table into a slightly more query-able format:

SELECT DateComm, 'Copper' as Metal, Copper as Price FROM CommMaterial
UNION ALL
SELECT DateComm, 'Nickel' as Metal, Nickel as Price FROM CommMaterial
UNION ALL
SELECT DateComm, 'Aluminum' as Metal, Aluminum as Price FROM CommMaterial;

Which will give you a result set with three columns:

DateComm | Metal | Price

You could save that as a query qry_CommMaterial and then your SQL would be:

SELECT Avg(Price) FROM qry_CommMaterial WHERE metal = [Forms]![NameForm]![List147];

You could also just force it all into one big statement too:

 SELECT Avg(Price) 
 FROM (
         SELECT DateComm, 'Copper' as Metal, Copper as Price FROM CommMaterial
        UNION ALL
        SELECT DateComm, 'Nickel' as Metal, Nickel as Price FROM CommMaterial
        UNION ALL
        SELECT DateComm, 'Aluminum' as Metal, Aluminum as Price FROM CommMaterial) as subUnion
 WHERE metal = [Forms]![NameForm]![List147];