Enter Parameter Value Error Message

[cfrance1]

Hello,

I recently started working with access for a school project in my Information Systems Management class. I'm a Finance major so it's not like I had to get this deep into this stuff, but I have a wondering mind. I put together my table and proceeded to creating my queries and they all work fine but one. I have read forum after forum trying to figure out the solution to my problem but no luck I could do without this query but after so much reading of all this code stuff I'm actually curious as to where the problem lies.

In the problematic query I want a field to perform a calculation that divides one of the columns in the problematic querry by a number in another querry, that just happens to be a one number query. I grouped my suppliers by supplierID and summed the amount of books I get from each giving me a UnitsInStock for each supplier. I would like to divide each supplier's amount of book by the total which is 371 but this number is the answer to one of my queries, inventory book total. SO I used the expression maker to create a field that divides SumOfUnitsInStock of each supplier, a field in the problematic query, by SumOfUnitsInStock a field in a query that adds up the UnitsInStock field in one of my tables. Everytime I run it, it asks me to Enter Parameter Value for Inventory Book Total!SumOfUnitsInStock. If I enter the 371, which is the of the corresponding query it works. How do I get it to stop asking so it just finds the value on its own. This is the SQL of the querry. I also used the documenter so I have a copy of that if it helpful. Any hep would be nice.

SELECT Suppliers.SupplierID, Suppliers.SupplierName, Count(Textbooks.SupplierID) AS CountOfSupplierID, Sum(Textbooks.UnitsInStock) AS SumOfUnitsInStock, [SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock] AS Expr1
FROM Suppliers INNER JOIN Textbooks ON Suppliers.SupplierID = Textbooks.SupplierID
GROUP BY Suppliers.SupplierID, Suppliers.SupplierName
ORDER BY Suppliers.SupplierID;

[NeoPa]

You have no source defined (in the FROM clause) as [Inventory Book Total].
The SQL engine will look at that expression and will treat it as an enterable parameter. Essentially unrecognised.
If you want to include the data from a separate query, you must include it in the FROM clause (It's possible, though not advised, simply to use a DLookup() here). If I understand you correctly, this query always returns a single result. If so, try this :

SELECT Suppliers.SupplierID,
       Suppliers.SupplierName,
       Count(Textbooks.SupplierID) AS CountOfSupplierID,
       Sum(Textbooks.UnitsInStock) AS SumOfUnitsInStock,
       [SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock] AS Expr1
FROM Suppliers INNER JOIN Textbooks
  ON Suppliers.SupplierID = Textbooks.SupplierID,
     [Inventory Book Total]
GROUP BY Suppliers.SupplierID, Suppliers.SupplierName
ORDER BY Suppliers.SupplierID;

Whether or not it makes any sense to divide one [SumOfUnitsInStock] value by another I'll leave up to you to sort out ;)

[puppydogbuddy]

Hello,

I recently started working with access for a school project in my Information Systems Management class. I'm a Finance major so it's not like I had to get this deep into this stuff, but I have a wondering mind. I put together my table and proceeded to creating my queries and they all work fine but one. I have read forum after forum trying to figure out the solution to my problem but no luck I could do without this query but after so much reading of all this code stuff I'm actually curious as to where the problem lies.

In the problematic query I want a field to perform a calculation that divides one of the columns in the problematic querry by a number in another querry, that just happens to be a one number query. I grouped my suppliers by supplierID and summed the amount of books I get from each giving me a UnitsInStock for each supplier. I would like to divide each supplier's amount of book by the total which is 371 but this number is the answer to one of my queries, inventory book total. SO I used the expression maker to create a field that divides SumOfUnitsInStock of each supplier, a field in the problematic query, by SumOfUnitsInStock a field in a query that adds up the UnitsInStock field in one of my tables. Everytime I run it, it asks me to Enter Parameter Value for Inventory Book Total!SumOfUnitsInStock. If I enter the 371, which is the of the corresponding query it works. How do I get it to stop asking so it just finds the value on its own. This is the SQL of the querry. I also used the documenter so I have a copy of that if it helpful. Any hep would be nice.

SELECT Suppliers.SupplierID, Suppliers.SupplierName, Count(Textbooks.SupplierID) AS CountOfSupplierID, Sum(Textbooks.UnitsInStock) AS SumOfUnitsInStock, [SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock] AS Expr1
FROM Suppliers INNER JOIN Textbooks ON Suppliers.SupplierID = Textbooks.SupplierID
GROUP BY Suppliers.SupplierID, Suppliers.SupplierName
ORDER BY Suppliers.SupplierID;

I see 2 possible causes for parameter message. Try the remedy in #1 first. If that doesn't fix it, then try the remedy in #2:

1. [SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock] AS Expr1
You have [SumOfUnitsInStock] appearing twice in this expression. They apparently come from 2 different queries, one of which is identified as coming from [Inventory Book Total] and the other is not identified as to source. This is what Access calls "an ambiguous expression". If this is the problem, all you need to do is put the query name associated with the ambiguous field name like you did for the one coming from [Inventory Book Total].

2. Try declaring your Parameters on the Access Parameter List. To invoke this Parameter List, right mouse click on the top part of the query and choose Parameters. A parameter declaration form appears to let you list each parameter name and its type. Declaring Parameters in the list of parameters is sometimes needed in order for Access to know how to handle them.

[puppydogbuddy]

Sorry Adrian...did not know you were working on this one....but at least we were both thinking along the same lines.

[cfrance1]

I copied what you sent me and put it in once I saw it in design view, I realized that I had tried that already. And when try and run after adding the source, what you gave me, I get a message that says:

"You tried to execute a query that does not include the specified expression '[SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock]' as part of an aggregate function."

The query I am trying to run should give me a field with 5 different values corresponding to 5 different suppliers. What I did is one querry I summed up the total amount of books hence [Inventory Book Total]![SumOfUnitsInStock] in the denominator of the, problematic, equation. Then I ran another query that counts UnitsInStock by supplierID that way I know how how many books are from each supplier. Now within this same querry I want to create a field that puts the UnitsInStock, by supplier, over the entire stock 317 which is the annswer to the query [Inventory Book Total]![SumOfUnitsInStock]. I could just write 317 in the denominator of the equation therefore when it asks for the parameter value and I put 317 I get my percentages but if I change the level of units in stock in the table these ratios won't change accordingly.

I am sorry If I'm being a bit redudant here, just trying to be very detailed. Now what did you say Dlookup, I'm not familiar with too much stuff as you can tell.
What do you think is causing the error message above.

Thanks for all your help

[cfrance1]

I see 2 possible causes for parameter message. Try the remedy in #1 first. If that doesn't fix it, then try the remedy in #2:

1. [SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock] AS Expr1
You have [SumOfUnitsInStock] appearing twice in this expression. They apparently come from 2 different queries, one of which is identified as coming from [Inventory Book Total] and the other is not identified as to source. This is what Access calls "an ambiguous expression". If this is the problem, all you need to do is put the query name associated with the ambiguous field name like you did for the one coming from [Inventory Book Total].

2. Try declaring your Parameters on the Access Parameter List. To invoke this Parameter List, right mouse click on the top part of the query and choose Parameters. A parameter declaration form appears to let you list each parameter name and its type. Declaring Parameters in the list of parameters is sometimes needed in order for Access to know how to handle them.

I understand what you are trying to say in remedy one. THat my denominator has a source [Inventory Book Total] but not my numerator. Thats because the source of the [SumOfUnitsInStock] on the top part of the equation is the actual query I am trying to put the [SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock] AS Expr1 into. Can I use the open query as a source for an equation within the query?

ANd in remedy 2 I've found that box plenty of times but I'm such a newbie I have no idea what to do with that.

[puppydogbuddy]

I understand what you are trying to say in remedy one. THat my denominator has a source [Inventory Book Total] but not my numerator. Thats because the source of the [SumOfUnitsInStock] on the top part of the equation is the actual query I am trying to put the [SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock] AS Expr1 into. Can I use the open query as a source for an equation within the query?

ANd in remedy 2 I've found that box plenty of times but I'm such a newbie I have no idea what to do with that.

<<Can I use the open query as a source for an equation within the query?>>
As far as I know......No, but you can use a domain aggregate function, such as Dsum (see below).

Replace the denominator (Inventory Book Total]![SumOfUnitsInStock]) with the DSum function, short for Domain sum function that has this syntax where the domain is the total for all suppliers:

DSum("[UnitsInStock]", "[Inventory Book Total]”)

For your information, if you wanted to you could aggregate by supplier using the following syntax. This syntax assumes that SupplierID is numeric.

DSum("[UnitsInStock]", "[Inventory Book Total]”, "[SupplierID] = " & Suppliers.SupplierID)

[cfrance1]

<<Can I use the open query as a source for an equation within the query?>>
As far as I know......No, but you can use a domain aggregate function, such as Dsum (see below).

Replace the denominator (Inventory Book Total]![SumOfUnitsInStock]) with the DSum function, short for Domain sum function that has this syntax where the domain is the total for all suppliers:

DSum("[UnitsInStock]", "[Inventory Book Total]”)

For your information, if you wanted to you could aggregate by supplier using the following syntax. This syntax assumes that SupplierID is numeric.

DSum("[UnitsInStock]", "[Inventory Book Total]”, "[SupplierID] = " & Suppliers.SupplierID)

WHen I try DSum("[UnitsInStock]", "[Inventory Book Total]”) in the denominator of the expression I get this error message:

"You tried to execute a query that does not include the specified expression '[SumOfUnitsInStock]/DSum("[UnitsInStock]", "[Inventory Book Total]”) as part of an aggregate function."

I have tried a bunch of different things. But it always asks me the Inventory ook Total and when I run that querry it comes up wtih the 317 I want. Its almost like it dosen't fid that number when it looks for it. I know what I'm trying to do can not be too much for access. If I ran the analyze documenter thing and included the table and query that this expression is oming from would it be clearer to see the problem.

THanks

[puppydogbuddy]

WHen I try DSum("[UnitsInStock]", "[Inventory Book Total]”) in the denominator of the expression I get this error message:

"You tried to execute a query that does not include the specified expression '[SumOfUnitsInStock]/DSum("[UnitsInStock]", "[Inventory Book Total]”) as part of an aggregate function."

I have tried a bunch of different things. But it always asks me the Inventory ook Total and when I run that querry it comes up wtih the 317 I want. Its almost like it dosen't fid that number when it looks for it. I know what I'm trying to do can not be too much for access. If I ran the analyze documenter thing and included the table and query that this expression is oming from would it be clearer to see the problem.

THanks

In general, that error message means the Group by clause must contain the same columns from the table as specified in the Select Statement. .. so modify your group by as follows:

GROUP BY Suppliers.SupplierID, Suppliers.SupplierName, Suppliers.UnitsInStock

[NeoPa]

Sorry Adrian...did not know you were working on this one....but at least we were both thinking along the same lines.

That's never a problem PDB ;)
I'm sure we'll crack it together.

[NeoPa]

I copied what you sent me and put it in once I saw it in design view, I realized that I had tried that already. And when try and run after adding the source, what you gave me, I get a message that says:

"You tried to execute a query that does not include the specified expression '[SumOfUnitsInStock]/[Inventory Book Total]![SumOfUnitsInStock]' as part of an aggregate function."

The query I am trying to run should give me a field with 5 different values corresponding to 5 different suppliers. What I did is one querry I summed up the total amount of books hence [Inventory Book Total]![SumOfUnitsInStock] in the denominator of the, problematic, equation. Then I ran another query that counts UnitsInStock by supplierID that way I know how how many books are from each supplier. Now within this same querry I want to create a field that puts the UnitsInStock, by supplier, over the entire stock 317 which is the annswer to the query [Inventory Book Total]![SumOfUnitsInStock]. I could just write 317 in the denominator of the equation therefore when it asks for the parameter value and I put 317 I get my percentages but if I change the level of units in stock in the table these ratios won't change accordingly.

I am sorry If I'm being a bit redudant here, just trying to be very detailed. Now what did you say Dlookup, I'm not familiar with too much stuff as you can tell.
What do you think is causing the error message above.

Thanks for all your help

You got that error message because I forgot to include the extra item in the GROUP BY clause.

SELECT Suppliers.SupplierID,
       Suppliers.SupplierName,
       Count(Textbooks.SupplierID) AS CountOfSupplierID,
       Sum(Textbooks.UnitsInStock) AS SumOfUnitsInStock,
       [SumOfUnitsInStock]/[Inventory Book Total].[SumOfUnitsInStock] AS Expr1
FROM Suppliers INNER JOIN Textbooks
  ON Suppliers.SupplierID = Textbooks.SupplierID,
     [Inventory Book Total]
GROUP BY Suppliers.SupplierID,
         Suppliers.SupplierName,
         [Inventory Book Total].[SumOfUnitsInStock]
ORDER BY Suppliers.SupplierID;

[NeoPa]

While the SQL in the previous post should work for what you're trying to do, it is a pretty crappy way to get this to work (as you'll need multiple queries designed for the various categories). Try sharing the underlying table structures with us (Table MetaData) and what you REALLY want, and we'll see if we can't come up with a more appropriate solution for you.
Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[cfrance1]

You got that error message because I forgot to include the extra item in the GROUP BY clause.

SELECT Suppliers.SupplierID,
       Suppliers.SupplierName,
       Count(Textbooks.SupplierID) AS CountOfSupplierID,
       Sum(Textbooks.UnitsInStock) AS SumOfUnitsInStock,
       [SumOfUnitsInStock]/[Inventory Book Total].[SumOfUnitsInStock] AS Expr1
FROM Suppliers INNER JOIN Textbooks
  ON Suppliers.SupplierID = Textbooks.SupplierID,
     [Inventory Book Total]
GROUP BY Suppliers.SupplierID,
         Suppliers.SupplierName,
         [Inventory Book Total].[SumOfUnitsInStock]
ORDER BY Suppliers.SupplierID;

Oh Now I see Why you are the Leader. THis worked out just like I wanted it. I'm more of a stock guy but this coding stuff is kind of addictive. Now that I have the answer I'm just gonna stare at this to see if I can figure out where the problem was. I also noticed that in my suppliers table, now, there is a plus sign next to the records and when open them I see each book that corresponds to that suplier. It's Proffesional looking I like it. Where did it come from, is it from this query I have been trying to make work and now that it works it travels up to the table. OR is it something you put in the code of the Querry.

While the SQL in the previous post should work for what you're trying to do, it is a pretty crappy way to get this to work (as you'll need multiple queries designed for the various categories).

Yeah I know it is probably not the neatest quickest way to do things, but up until now All I knew about access was stupid stuff like sort and other options you get from clicking around the options. But I see there is a whole world behind that. Thats all I needed for the project but might be back just out of curiosity, I bet you culd come up with some really cool things with all this coding stuff. Thanks for all your help

[NeoPa]

Feel free to come back and ask more questions. You're a member after all ;)
BTW. All the Moderators are just Experts with extra responsibilities on here. Being a Moderator (or even Forum Leader), is not a reflection of how clever we are, or even how expert we are at Access. Most of us have gaps in our knowledge (I know I do), but the overlay of all of our skills should make a fairly decent cover for the subject.