Round query values up to nearest table value

kicrewd

Registered User.
Local time
Today, 05:39
Joined
Sep 3, 2014
Messages
18
Hi,

I have a table of standard Circuit Breaker (CB) sizes. I then calculate a minimum CB size in a query field. I want to use that calculated minimum value to look up the next largest CB size from the table and fill a field in the query with it.

Any suggestions?

For now, I am going to add a field to the CB size table with the smallest size CB which would be assigned that standard value. I will then use a Dlookup with conditions of greater than "smallest size" and less than "standard size" fields from the CB size table.

I think this will work fine, but there must be a better way.

Thanks.
 
Thanks, that look like it should work well.

When I try to implement that, in the MotorQ query, I am using the code:

Standard Breaker Size: DMin("[SC Rating]","[BreakerT]","[BreakerT].[SC Rating]>"& [MotorQ].[SC Rating])

but it keeps asking me to input the parameter value:

MotorQ.SC Rating

Why is it trying to get me to input that instead of just retrieving it?
 
I haven't been using SQL, that is what I put in the "Field:" box for the field I am making in the MotorQ query.
 
And I realize now that if I change to SQL view I have:

SELECT MotorT.ID, MotorT.[Motor Name], MotorT.Voltage, MotorT.Phase, MotorT.[Nameplate Current], MotorT.CircuitLength, MotorT.Horsepower, MotorT.RacewayMaterial, DLookUp("[Current]","[NEC430_250IndT]","[NEC430_250IndT].[HP]=" & [MotorT].[Horsepower] & "And [NEC430_250IndT].[V]=" & [MotorT].[Voltage]) AS [Table Current], [Table Current]*1.25 AS [Conductor Ampacity], [Table Current]*8 AS [SC Rating], DMin("[SC Rating]","[BreakerT]","[BreakerT].[SC Rating]>" & [MotorQ].[SC Rating]) AS [Standard Breaker Size], [Nameplate Current]*1.25 AS [Overload Rating]
FROM MotorT;
 
[SC Rating] isn't part of MoterT, that's the reason you are being prompted for its value. You can't calculate a field then immediately reference it in the same query.
 
Using a domain function on every record in a query is very much slower than joining the table into the query.

If a non-updateable query is acceptable then use an aggregate query with Min() to select the smallest value from the table field after using a Where criteria to pick only the records that are greater than the value being tested.

That query can be built in the designer. However, it will perform better if the greater than is done in the Join. Access can support joins in SQL View that cannot be represented in the Design View.
 
I'm going to try moving to using Join's in SQL, but I haven't done anything in SQL yet, so first I want to try to get it to work with my options in Design View.

I created a second query "MotorQ2" which receives the information calculated in the first query "MotorQ" and calculates one more value.

This is the SQL code for MotorQ2:

SELECT MotorQ.ID, MotorQ.[Motor Name], MotorQ.Voltage, MotorQ.Phase, MotorQ.[Nameplate Current], MotorQ.CircuitLength, MotorQ.Horsepower, MotorQ.RacewayMaterial, MotorQ.[Table Current], MotorQ.[Conductor Ampacity], MotorQ.[SC Rating], MotorQ.[Overload Rating], DMin("[SC Rating]","[BreakerT]","[BreakerT].[SC Rating]>=" & [MotorQ2].[SC Rating]) AS [Breaker Rating]
FROM MotorQ, BreakerT;

When I try to go back to datasheet view it is prompting me to enter "MotorQ2.SC Rating". This is a value MotorQ2 is getting directly from MotorQ. I checked, MotorQ is calculating this value as intended. Why isn't it getting to MotorQ2?

Regarding Galaxiom's suggestions:

What does an updateable vs. nonupdateable query mean?

What is a domain vs. non-domain function?

Thanks.
 
[MotorQ2].[SC Rating] doesn't exist, so its asking you what it is supposed to be. This is similar to the previous [SC Rating] issue you had (See post #8). You've essentially created a ciruclar loop: Inside the query for MotorQ2 you are using a value found inside MotorQ2 - you can't do that. You don't know what value it will produce is until you run the query and you can't run the query until you know what value it produces and you don't know the value it will produce until...etc.

Additionally, why aren't MotorQ and BreakerT joined in your query? You've create a cartesian product (http://en.wikipedia.org/wiki/Cartesian_product). It doesn't look like you use an fields in that query from BreakerT.

Lastly, an updateable query means a query which allows you to perform edit statements on (INSERT, UPDATE, DELETE) and a non-updateable one won't allow you to (mainly because of the way you have joined various datasources together in it. A domain function looks at a dataset to do its calculation (DMin(), DMax(), DFirst(), DLast()) and a non-domain function doesn't need to (Date(), Len(), Abs()). Looking into a dataset is costly in terms of performance because you have to open it and look through all the records.
 
Last edited:
Alright, I think I am understanding how to use a query better now. I thought I should put all the fields I want to use in my calculations in the query, but for the most part I shouldn't put fields used in calculations in the query. Query fields should be calculated from fields outside the query unless it is a simple operation like FieldName*2.

I don't see how I have created a Cartesian Product, I pull one value from BreakerT for each record in MotorQ2. What would it mean to join BreakerT and MotorQ?
 
You can't reference fields from the query you are currently running. You can reference fields from data sources a query is built on (datasources in FROM clause, or in a JOIN clause).

When you use multiple tables in your FROM clause and don't establish a relationship among them in a WHERE clause you always create a cartesian product. Run this query and tell me how many results you get (assuming it doesn't time out):

SELECT MotorQ.ID, BreakerT.[SC Rating] FROM MotorQ, BreakerT;

That's how many results your previous query will produce (once you fix the issue you have)--I'm sure its a lot more than you expect/want.
 

Users who are viewing this thread

Back
Top Bottom