I currently have a qeury that calculates a value, this value can be anywhere from 0 - 3000, I then need to use this value and lookup in a range in a table. This table has three columns, (Min, Max, Setpoint)
I currently use
DLookup("Setpoint", "TableNamee", "Max > Calculatedvalue"))
I use this for every record in my query, very slow.
I need the setpoint number in table, the calculated value needs to be higher than the min, but lower then the max, then I found the correct record and need to retur the setpoint value.
Are the Max and Min values in the Range table arbitrary or do they have a regular increment? If they are regular it may be possible to calculate a value that could be used to join the two tables.
Otherwise the data could be processed in a procedure looping through the two recordsets.
BTW. Max, Min and Range are reserved words and should be avoided as names.
Also note your current technique is unreliable. It relies on the table being ordered by the Max field. DLookUp returns the first record it finds and tables are unordered.
The values I have can vary, there is no pattern between them, I do have the table sorted by Max Row, I am using other names, I just posted those for simplicity.
As you have probably realised the Min field isn't really necessary in the Range table since it is one more than the previous Max and hence technically a breach of normalization unless you have gaps in the series.
If you haven't already, you should index the Max field in the table. This is essential to maximise the speed of finding this value and might even make the existing DLookUp tolerable.
The recordset advantage is due to replacing the individual DLookUps with a single recordset that remains open during the whole process. The query with the DLookUp has to open a new recordset (or some mysterious secret Microsoft equivalent) for each record.
Open two recordsets. One will be based on the query and include an empty field for the one you currently populate with the DLookup, I have called it SetPoint).
When you open the Range recordset be sure to order it by the field Max or you will potentially get the wrong answer due to the unorderd nature of tables as I pointed out previously.
Then do this (assuming DAO recordset)
Code:
With rsYourQuery
Do While Not !EOF
rsRange.FindFirst "Max >" & !CalculatedValue
!Edit
!SetPoint = rsRange!SetPoint
!Update
!MoveNext
Loop
End With
Set Yourform.Recordset = rsYourQuery
It is late here and I am very tired. Hope I got it right.
I mostly use ADO recordsets. This is nearly the same except the Edit and Update commands are not required and FindFirst is replaced with Find.
There should also be something to catch the out of Range value error just before moving into Edit.
I have created a function, I call the function in the query.
This is the code I have, it works and finds the correct value, but did not seem to improve the speed over the Dlookup with indexed feild.
Should I have down this differently, or is this the fastest that this code will run.
Function MinSE(APS As Double, VATYPE)
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("valves_Lab")
If APS = 0 Then
MinSE = 0
Else:
If rs1.RecordCount = 0 Then Exit Function
rs1.MoveFirst
rs1.FindFirst "M_VAV_MAX_CFM > " & APS
MinSE = rs1!M_VAV_MIN_CON_CFM
rs1.Close
Set rs1 = Nothing
Set db = Nothing
End If
End Function
Your function basically emulates what is done by the DLookUp function.
Like the DLookUp it is being called for each record in the query.
Notice in my code fragment there are two separate recordsets. One (rsYourQuery) is based on the query. This can either have the actual saved query name as its argument or it can use the SQL text of the query. The important thing to notice is that it includes an unpopulated field which will accept the result of the processing.
The other recordset (rsRange) is the one is based on the Range table and operates as you have done in your function with valves_lab.
The difference is that the query recordset is used instead of the query itself which is never opened directly. The entire processing previously done in this query with function calls for each record is carried on inside the sub/function in a single instance of both recordsets.
This keeps the Range recordset open throughout, saving the time it takes to recreate this recordset in a function call for each record in the query. I don't know how much time this would save but I would expect it to be significant. I would be interested to know the comparative times.
The last line in my fragment is used to display the recordset in a form as you would with the normal query. The form must be already open at this point.
However if you then need to continue processing in another query it is probably easier to use a table field in the recordset and store the value. Technically this could breach normalization but it is a reasonable alternative if the calculation is very slow or if the looked up values and ranges change from time to time.
Of course if they do change you will need to manage this regardless of whether you choose to store the looked up figure. The properly normalized structure would add a date to the Range records and continue to look up the values. But now I am getting off the question.
I still have trouble understanding recordsets and where I should put this code.
I can grasp creating a recordset that would stay open while my query runs, so that I only creat in once, instead of one for every record in the query.
I just do not understand where I would put this code, does it go in the query in one of the rows of the fiedls, or do I have to modify the SQL behind the query, which i am not that good at.
If you can provide some more help on the recordsets or the subqueries, it would be great. i will also try some searching regarding both.
In the query design grid you can create a field (type a name in the row called field) and assign it a value using syntax like ...
Code:
MyTestField: MyFunction(FieldFromTable)
This runs the function MyFunction, passing in the value of FieldFromTable from your table which returns a value and assigns it to MyTestField for that record.
A subquery works the same as that function...
Code:
MyTestField: ( SELECT TOP 1 t2.SetPoint FROM tSetPoints As t2 WHERE t2.Max > [AQueryTable].[FieldInSaidTable] ORDER BY t2.SetPoint )
...and you need to take some pains that it only ever returns one record. But the query designer will warn you about that...
Does that help?
First try lagbolt's subquery. It is probably a much better solution.
Unfortunately, in my haste, I have undoubtedly provided too little information about an advanced way to use Access to someone who was not really quite ready for it.
This is the usual way to use a function in a query. GMan understands this and I thnk this is why he is gettting confused with the code I provided.
Now having contributed to the confusion I probably should expalin it more. It is a good thing to know how to do though many developers will never need it.
The query doesn't get run in the normal way. My code fragment is meant to be part of a sub that generates a recordset that actually replaces the query as the recordset of a form.
It is done entirely in VBA, completely eliminating the need for the form to have a RecordSource property (usually the query itself). In this case it is really just a technique to get the data to the form without using the query in a way that forces it into calling a function for every record, hopefully saving some time.
As I said above note the crucial line that makes the recordset able to be displayed.
Set YourForm.Recordset = rsYourQuery
The rest of the form is the same as any bound form. The ControlSource of the text boxes on the form simply refer to the fieldname in the usual way.
There are a couple of important points about working with the form when using a recordset in this way.
I normally use this technique with ADO recordsets where I want to present transient information on a form. It is a good alternative to temporary tables which some developers use for this type of task.
The ADO recordset can be constructed piecewise in memory without a connection to any tables. It not only completely eliminates any problems with bloat but largely avoids the danger of sensitive information being left behind in temporary files.
(Of course the swapfile, pagefile.sys is still a security threat.)
In the Query (called "Roomcalculations-2") I have calculate a value based on other feilds in this query. I can have 1 to 1000 records. This calcuated value can be different for each record. this calculated value filed name is [Actual_Peak_Design_Sup_CFM].
I am trying to write code for the subquery to take this value, and compare it to the M_VAV_MAX_CFM in a query called "Valves_Lab". the calculated value needs to be less than this the max, if it is then I want to return M_VAV_MIN_CON_CFM in the same record of the Valves_Lab query.
this is my code, but when I run the query in datasheet mode, it prompts me for the [Actual_Peak_Design_Sup_CFM]. This vlaue shold come the the query, but does not. if I enter a number in the prompt, the feild cfm returns the correct value from the Valves_Lab query. But this value is the same for all records. Why doesnt it grab the calculated filed in the
query to use. also do not understand the as AS [OLD] part of the code, i was copy other subquery code.
cfm: (SELECT min([M_vav_min_con_cfm]) FROM [VALVES_LAB] AS [OLD] WHERE [m_vav_max_cfm] > [Actual_Peak_Design_Sup_CFM] )
Also, this query is only for my reports, it mainly does my calcualtions i need for reports.
Notice how a subquery raises the possibility that there will be ambiguity in respect to which table a field comes from. To solve this you want to alias the tables, as you've done using the As clause, and then explicitly indicate those table references. Consider...
cfm: (SELECT min([OLD].[M_vav_min_con_cfm]) FROM [VALVES_LAB] AS [OLD] WHERE [OLD].[m_vav_max_cfm] > [ParentQuery].[Actual_Peak_Design_Sup_CFM] )
See how the subquery looks to it's own table for it's own data, but looks out to the ParentQuery for data to execute its WHERE clause for the current record.
I tired that, but when I run the parent query ( which is where i put the code) it still prompts me for the [RoomCalcualtions-2].[Actual_Peak_Design_Sup_CFM.
This is a calcualted value in the parent query.
I switched this to another field in the parent query which is not calculated (testing) and it worked.
It gave me the correct values in the Valves_Lab query, it does not seem to work with a Calculated feild in the parent query.
also, what is the old, there is not field in either query named OLD, what should this be.
[OLD] is from sql you posted at 10:26. The only things I changed are in red.
Also, there are three typos in your last post. Are you sure there are none in your SQL? One typo and SQL prompts you for a value for the unknown fieldname.
Not sure, i was copying code, so I may have mistakes,
so let me recap,
I have parent query where I want to put subquery SQL in a field to lookup value in another query called "Vavles_Lab"
Parent Query = Roomcalculations-2
The value in the parent query is calcuated and is called [Actual_Peak_Design_Sup_CFM]
I want to use this as the lookup value, this value
should be less than the M_VAV_MAX_CFM in the Valves_Lab query.
When this is meet, I want to return the M_VAV_MIN_CON_CFM from the same query.
If I put htis code in, it still prompts me for the [RoomCalculations-2].[Actual_Peak_Design_Sup_CFM]
cfm: (SELECT min([Valves_Lab].[M_vav_min_con_cfm]) FROM [VALVES_LAB] WHERE [Valves_Lab].[m_vav_max_cfm] > [RoomCalculations-2].[Actual_Peak_Design_Sup_CFM] )
