Dlookup vs. subquery (1 Viewer)

cricketbird

Registered User.
Local time
Today, 09:14
Joined
Jun 17, 2013
Messages
106
I am querying a table (tblMeasurements) with the following format:
ItemID MeasurementID MeasValue
1 23 10
1 24 1.5
1 25 2.3
2 23 20
2 24 1.6
2 25 3.4

I want to multiply everything in the table by the value of Measurement #23 for that item. The end result should be:
ItemID MeasurementID AdjustedMeasValue
1 23 100 'yes it gets multiplied by itself
1 24 15
1 25 23
2 23 400
2 24 32
2 25 68

I've tried using Dlookup, but it picks the same value of Value for every Item.
Code:
SELECT ItemID, MeasurementID, MeasValue, DLookUp("[MeasValue]","tblMeasurements","[MeasurementID] = 23") AS DMPct, [MeasValue]*[DMPct] AS Temp INTO ImportTmpTable
FROM tblMeasurements;

I've tried using a subquery (I'm still a little sketchy on how these work) but can't seem to link the ItemIDs either. I get the error "At most one record can be returned by this subquery". Not sure how to filter this by the current record in the main query.
Code:
SELECT tblMeasurements.ItemID, tblMeasurements.MeasurementID, tblMeasurements.MeasValue, (

SELECT tblMeasurements.MeasValue
FROM tblMeasurements
WHERE (((tblMeasurements.MeasurementID)=23));

) AS DMPct
FROM tblMeasurements;

How can I create a query that multiplies a unique value for each item from within that table?

Thanks in advance!
CB
 

plog

Banishment Pending
Local time
Today, 08:14
Joined
May 11, 2011
Messages
11,611
Your Dlookup (and query) isn't explicit enough. You have more than one MeasurementID=23 in tblMeasurements. If you want to be more restrictive you need to tell your criteria to do that:

Code:
DLookUp("[MeasValue]","tblMeasurements","[MeasurementID] = 23")

You need more than just Measurement=23, you need to use the ItemId value as well.
 

cricketbird

Registered User.
Local time
Today, 09:14
Joined
Jun 17, 2013
Messages
106
I can include ItemID in the dlookup, but how can I specify that it should be the itemID of THIS line of the query? The code below still picks "10" as the multiplier for all the lines.

Code:
SELECT tblMeasurements.ItemID, tblMeasurements.MeasurementID, tblMeasurements.MeasValue, DLookUp("[MeasValue]","tblMeasurements","[MeasurementID] = 23 and [ItemID] = ItemID") AS DMPct, [MeasValue]*[DMPct] AS Temp INTO ImportTmpTable
FROM tblMeasurements;
 

plog

Banishment Pending
Local time
Today, 08:14
Joined
May 11, 2011
Messages
11,611
The criteria is a string of text. To insert the current record's ItemID value you escape from that string:

"[ItemID]=" & ItemID
 

Users who are viewing this thread

Top Bottom