Simplifying a DLookup and DMin

[SatanLuciferJones]

Hello,

About Me
I know there has to be a better way to do what I'm doing, but I'm a beginner at Access, so I don't know exactly how to do it. I'm using Access 2010.


About My Setup
I have a parent table (Tests) that gives general information about a particular product and child table (SubTest) that shows the temperature (Temp) results of each component (TCLocation) where the product was tested.


The Objective
I need to find the minimum value of each TestID and use that to do calculations on each subID. Essentially, what I'm doing is finding the ambient temperature and normalizing it to 25°C. Then apply that same normalization difference to each value temp.

For example, subID 11 has a temperature of 24.6. The difference from the room temperature (25-24.6) is 0.4. Therefore, I need to add 0.4 to the Temp values that share the same TestID, so that subID 11 = 25.0, subID 12 = 42.4, subID 13 = 38.8, etc.

Notes

• The minimum value is not always name "Room Ambient," so I can't use that as a basis for the search.
• I currently have a very clunky formula as shown in the Notepad section on the image attached. The formula works, but it seems dumb to use a DLookup when I'm using the values within the same query.
• I saw a post that said that I can't post images before I reach 10 posts, so I included the zip in case the image doesn't display.

Any help is appreciated.

 

[theDBguy]

Hi. Welcome to the forum! It's not dumb to use DLookup() if the value you're looking for is not in the same row as where you need it. Otherwise, you could try using a subquery. But if the DLookup() works, you don't have to changes it unless it's really slowing down your query.

 

[SatanLuciferJones]

How would I go about making a sub query?
Never mind. I figured out how to make the sub query. Thank you so much. I really needed this because some other queries I have use too many DLookups and it was really slowing things down.

 

[theDBguy]

How would I go about making a sub query?
Never mind. I figured it out how to make the sub query. Thank you so much. I really needed this because some other queries I have use too many DLookups and it was really slowing things down.

Check out this tutorial on Subquery Basics.

 

[Mark_]

How I would approach this is a little different.

I'd make a query that returns ONE row for each parent. This would have
Parent ID
Delta (25-Dmin(Field, ChildTable, ChildTable.ParentID = ParentID)

I'd then join this query to your child query and return your temp as temp+Delta.

In simple terms,
Make a list of what the minimum is for each parent record.
Calculate the difference between your "Base" value and your minimum value.
Return THAT in a query.

Make a second query that needs to reference this value (your child record)
Join the two queries by the parent's ID.
Return your adjusted temperature as your recorded temperature plus your delta.

Let me know if this approach would work for you.