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.
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.
How can I create a query that multiplies a unique value for each item from within that table?
Thanks in advance!
CB
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