Faster Than Dlookup

[MarkK]

Your db contains linked tables from Interim Lab Loads, so nothing really works.
Apart from the immediate problem, your table RoomCalcs can't be right: there are just too many columns and it looks like you might be happier working in Excel. What you're doing in that table doesn't really work well in a database.
But back on topic: The 'parent table' is called RoomCalcs ....

( SELECT min([valves_Lab].[M_vav_min_con_cfm]) FROM [VALVES_LAB] WHERE [Valves_Lab].[m_vav_max_cfm] > [RoomCalcs].[Actual_Peak_Design_Sup_CFM] ) AS cfm

See? You don't reference the name of the query you are working in, you reference the name of one of the tables that the query contains.
Cheers,

 

[Gman]

I have many more tables, queries and forms and reports in my actucal databse,
I just exported the tables and queries that I am trying work on as this time. files is much to large to attach.

The table roomcalcs does not have a field called [Actual_Peak_Design_Sup_CFM], this is a field in the query roomcalculations-2 that is calculated in the query.

The [Actual_Peak_Design_Sup_CFM] uses a two other fields in the Roomcalcualtion-2 query and does a calculations.

So the only way to get the value is to run the query.

 

[Gman]

If I use this code in the parent query, and change the lookup value from [Actual_Peak_Design_Sup_CFM] to a none calcuated feild in the query, it works fine.

cfm: (SELECT min([valves_Lab].[M_vav_min_con_cfm]) FROM [VALVES_LAB] WHERE [Valves_Lab].[m_vav_max_cfm] > [Cooling CFM] )

 

[MarkK]

Does that mean the problem is solved?

 

[Galaxiom]

[Actual_Peak_Design_Sup_CFM] is not a field in [VALVES_LAB] so the query asks for it.

I also took a look at lagbolt's suggestion and I don't think it is going to work because the subquery refers to a field in T1 which is not in the the FROM clause of the subquery.

If both tables were added to the subquery the number of records in the subquery is the mathematical product of the number of records in both tables because there is no field that can be used as a join. This can still work so long as the Where and Odrer clauses get the right outcomes to the top.

It sounds like an unlikely solution with so many records but I have worked with another memeber here where a similar solution using a query with no join was compared to a function applied to each record. The query was about twice the speed of the function. One of the tables contained a record for each State of the USA.

However we didn't try processing entirely in recordsets.

 

[Gman]

I have not solved the problem yet,

I was mentioning that when I refered to a feild in the table that was in the underlying table, it worked, the lookup value was a different value for each record, and the subquery returned the correct value.

But for it to work, it needs to refer to a differnt value in my query.

When I used a calculated value in the query for the lookup, it prompted me for value.

I was thinking the subquery fired first before the calculated field??

Not sure where to go from here.

 

[smig]

sorry I'm jumping in the middle of this but your Dlookup function will bring only the first time where Max > Calculated field

if you need all records where Max > CalculatedField you should use a Where clue in a query
how do you calculate the field CalculatedField ? if it's calculated using a query grid why fon't you add the < [Max] to the criteria

 

[Galaxiom]

I have just had a look at your database. You are worrying about the problem you posted about far too early.

You have some massive normalization issues to deal with first.

 

[Gman]

The database I provided was a small portion of my large database.
I only included the required table ans queires.

The roomcalculations-2 query in my database refers to Query Roomcalculations-1, I dumped the outout of this query into a table so I could post.

The calcuated feild in Roomcalcuations-2 is bascially a custom function that uses three or four feilds in roomcalculations-2. So, the calcuated value is different for every record in roomcalculations-2. The lookup table that I am using, has a minimum and a maximum value and a min control setpoint.
If the calculated value is below the max, then I want the corresponding setpoing for that same record.

I may have some nomalize problems in my main database, but sometimes to get the outputs I need, I may go against that.

Now, I took the calculated feild in Roomcalcualtions-2 and move it into Roomcalculations-1, then brought that feild into Roomcalculations-2 ( as 2 refers to 1) . Then I used this subquery code and i works the way I need it to work. The only difference is that [Actual_Peak_Design_Sup_CFM] is not calculated in the same query.

CFM: (SELECT min([valves_Lab].[M_vav_min_con_cfm]) FROM [VALVES_LAB] WHERE [Valves_Lab].[m_vav_max_cfm] > [Actual_Peak_Design_Sup_CFM])

So as far as speed improvements, this methods is takes about 15 secs to run, vs Dlookup whichs takes about 60 secs to run.

Still not sure if If this was the correct way to go, but I am getting the correct results,

Thanks For the Help.

 

[Galaxiom]

Ok. I understand what you have done with the calculations to condense the relevant fields into one table. Good to know you do understand normalization afterall.

However, in your sample, Valves is a linked table so it is not available.

I would still like to try the double recordset technique so could you post another sample with it included?

 

[Guus2005]

Faster than Dlookup is Tlookup at least for attached tables. For local tables the domain functions are faster. Tlookup uses a recordset to do the same as domain functions.
Search the forum for Tlookup.

HTH