Query Not Behaving as expected - It's asking for a parameter Access Built SQL posted.

  • Thread starter Thread starter Blueridge.net
  • Start date Start date
B

Blueridge.net

MS Access has "assisted" me to create the following query, that provide the
output I'm looking for as long as click through paramenter inputs for
"Sched_Uptime" and "Outage_Mins_Used" with no input. The problem is that I
didn't intentonally build parameters into the query. I am new to Access and
the "helpfile" wasn't very helpful to me. I feel the problem is related to
the ([Sched_Uptime]-[Outage_Mins_Used])/[Sched_Uptime]; statement but I'm
pretty inexperienced.



SELECT DISTINCTROW [Merial_Current Month_Detail].Field1, [Merial_Current
Month_Detail].Field2, [Merial_Current Month_Detail].Field20,
Sum([Merial_Current Month_Detail].Field4) AS Sched_Uptime,
Sum([Merial_Current Month_Detail].Field5) AS Outage_Mins_Used,
([Sched_Uptime]-[Outage_Mins_Used])/[Sched_Uptime] AS Availablity
FROM [Merial_Current Month_Detail]
GROUP BY [Merial_Current Month_Detail].Field1, [Merial_Current
Month_Detail].Field2, [Merial_Current Month_Detail].Field20,
([Sched_Uptime]-[Outage_Mins_Used])/[Sched_Uptime];


Any advice would be appreciated......
 
I must be honest, I don't understand any of your first sentence.

But, when Access can not find a field name in the table/query (i.e.
Merial_Current Month_Detail), then it will prompt for a value, rather than
just crashing. So, verify that all of your field names are valid and the
prompts should go away.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
MS Access has "assisted" me to create the following query, that provide the
output I'm looking for as long as click through paramenter inputs for
"Sched_Uptime" and "Outage_Mins_Used" with no input. The problem is that I
didn't intentonally build parameters into the query. I am new to Access and
the "helpfile" wasn't very helpful to me. I feel the problem is related to
the ([Sched_Uptime]-[Outage_Mins_Used])/[Sched_Uptime]; statement but I'm
pretty inexperienced.
Click to expand...

The problem is that you cannot use a calculated field (such as
Outage_Mins_Used) in a further expression; you must recapitulate the
calculation.


John W. Vinson[MVP]
 
I had to look up recapitulate, LOL. I'm doubly challenged, new to forum use
and new to access.

If I understand what you're telling me John, MS Access assumes a parameter
input is needed since I'm using the field output of two calculated (sum of)
fields to calculate a third. Does this represent a basic logic flaw in my
approach? The query does calculate correctly on all fields but those
parameter input boxes are annoying.

Perhaps I'm suffering from a spreadsheet mindset. If my approach is bad, can
you recommend a better approach that would still yield a correct result
without the annoying parameter input boxes popping up when the query runs?

1. Query1 isolates all current month records for selected customername.
2. Query2 rolls up those records by groupname summing scheduled up minutes
and outage minutes, then calculates the
last field using the sums of scheduled up minutes and outage minutes in
a simple expression.

Ultimately the query results will be exported to excel for charting.

They say we learn best by doing, I definitely appreciate the guidence and
knowledge offered on the forums.

Thanks.


John Vinson said:
MS Access has "assisted" me to create the following query, that provide the
output I'm looking for as long as click through paramenter inputs for
"Sched_Uptime" and "Outage_Mins_Used" with no input. The problem is that I
didn't intentonally build parameters into the query. I am new to Access and
the "helpfile" wasn't very helpful to me. I feel the problem is related to
the ([Sched_Uptime]-[Outage_Mins_Used])/[Sched_Uptime]; statement but I'm
pretty inexperienced.
Click to expand...

The problem is that you cannot use a calculated field (such as
Outage_Mins_Used) in a further expression; you must recapitulate the
calculation.


John W. Vinson[MVP]
Click to expand...
 
I had to look up recapitulate, LOL. I'm doubly challenged, new to forum use
and new to access.

If I understand what you're telling me John, MS Access assumes a parameter
input is needed since I'm using the field output of two calculated (sum of)
fields to calculate a third. Does this represent a basic logic flaw in my
approach? The query does calculate correctly on all fields but those
parameter input boxes are annoying.
Click to expand...

The parameter boxes are popping up because Access doesn't recognize
the value as a field. It should - it uses it! - but the way the
software was written, an expression defined with an alias name is a
"dead end" - you cannot then use that alias name in a different
expression. Since it doesn't recognize the fieldname, it prompts you
for it.
Perhaps I'm suffering from a spreadsheet mindset. If my approach is bad, can
you recommend a better approach that would still yield a correct result
without the annoying parameter input boxes popping up when the query runs?

1. Query1 isolates all current month records for selected customername.
2. Query2 rolls up those records by groupname summing scheduled up minutes
and outage minutes, then calculates the
last field using the sums of scheduled up minutes and outage minutes in
a simple expression.
Click to expand...

Just use a more complex expression:

SELECT DISTINCTROW [Merial_Current Month_Detail].Field1,
[Merial_Current Month_Detail].Field2,
[Merial_Current Month_Detail].Field20,
Sum([Merial_Current Month_Detail].Field4) AS Sched_Uptime,
Sum([Merial_Current Month_Detail].Field5) AS Outage_Mins_Used,
(Sum([Merial_Current Month_Detail].Field4) - Sum([Merial_Current
Month_Detail].Field5))/Sum([Merial_Current Month_Detail].Field4) AS
Availablity
FROM [Merial_Current Month_Detail]
GROUP BY [Merial_Current Month_Detail].Field1, [Merial_Current
Month_Detail].Field2, [Merial_Current Month_Detail].Field20;

I'd suggest NOT grouping by Availability - change it to Expression
instead in the query grid.

John W. Vinson[MVP]
 
John that worked like a charm. I'd like to express my thanks for your time,
expertise and willingness to guide me to a solution with my problem.


John Vinson said:
I had to look up recapitulate, LOL. I'm doubly challenged, new to forum use
and new to access.

If I understand what you're telling me John, MS Access assumes a parameter
input is needed since I'm using the field output of two calculated (sum of)
fields to calculate a third. Does this represent a basic logic flaw in my
approach? The query does calculate correctly on all fields but those
parameter input boxes are annoying.
Click to expand...

The parameter boxes are popping up because Access doesn't recognize
the value as a field. It should - it uses it! - but the way the
software was written, an expression defined with an alias name is a
"dead end" - you cannot then use that alias name in a different
expression. Since it doesn't recognize the fieldname, it prompts you
for it.
Perhaps I'm suffering from a spreadsheet mindset. If my approach is bad, can
you recommend a better approach that would still yield a correct result
without the annoying parameter input boxes popping up when the query runs?

1. Query1 isolates all current month records for selected customername.
2. Query2 rolls up those records by groupname summing scheduled up minutes
and outage minutes, then calculates the
last field using the sums of scheduled up minutes and outage minutes in
a simple expression.
Click to expand...

Just use a more complex expression:

SELECT DISTINCTROW [Merial_Current Month_Detail].Field1,
[Merial_Current Month_Detail].Field2,
[Merial_Current Month_Detail].Field20,
Sum([Merial_Current Month_Detail].Field4) AS Sched_Uptime,
Sum([Merial_Current Month_Detail].Field5) AS Outage_Mins_Used,
(Sum([Merial_Current Month_Detail].Field4) - Sum([Merial_Current
Month_Detail].Field5))/Sum([Merial_Current Month_Detail].Field4) AS
Availablity
FROM [Merial_Current Month_Detail]
GROUP BY [Merial_Current Month_Detail].Field1, [Merial_Current
Month_Detail].Field2, [Merial_Current Month_Detail].Field20;

I'd suggest NOT grouping by Availability - change it to Expression
instead in the query grid.

John W. Vinson[MVP]
Click to expand...
 
John that worked like a charm. I'd like to express my thanks for your time,
expertise and willingness to guide me to a solution with my problem.
Click to expand...

You're most welcome - glad to have been of assitance!

John W. Vinson[MVP]
 
Back
Top