Pulling data when two criteria from other table match

[philippilat]

Hi All,



Apologies if this has been addressed before or if this is possible, I'm rather new to access, or at least getting back into it after a few years. Tried searching for a similar problem but not sure if I'm using the correct terminology.

I need to update 6 columns in a table. For the sake of the example I'll call them multiplier 1 through 6. Each line item has 6 multipliers that are correspond to the primary key called the Pcode.

Now the database that contains these multiplier is formatted such that there are 6 line items per Pcode containing the multiplier and a column that contains a multiplier identifier (contains a digit that specifics if that multiplier is multiplier 1, 2,3...)

Example:

Pcode	Multiplier	Multiplier Identifier
10001	1.56	1
10001	1.99	2
10001	2.65	3
10001	5.98	4
10001	3.00	5
10001	4.21	6

Does anyone know a query that would pull the multiplier if both the Pcode and the Multiplier Identifier match my criteria. So for example if Pcode = 10001 and Multiplier Identifier = 2 then pull 1.56 into the cell.

Many Thanks in Advance.

[orange]

Do the fields you have shown reside in a table?
Are there multiple PCodes where each has different multipliers?

I suggest you elaborate a little on your requirement.

[ssanfu]

I agree with orange.... need more info.

Does anyone know a query that would pull the multiplier if both the Pcode and the Multiplier Identifier match my criteria. So for example if Pcode = 10001 and Multiplier Identifier = 2 then pull 1.56 into the cell.

Strange....... for Pcode = 10001 and Multiplier Identifier = 2, I get 1.99 (not 1.56)


But I did a bit of guessing and came up with this - very simple and no error handling.

[philippilat]

Yes, so the fields shown are within a table and yes each Pcode will have 6 unique multipliers.

[philippilat]

Do the fields you have shown reside in a table?
Are there multiple PCodes where each has different multipliers?

I suggest you elaborate a little on your requirement.

Yes, so the fields shown are within a table and yes each Pcode will have 6 unique multipliers.

I agree with orange.... need more info.



Strange....... for Pcode = 10001 and Multiplier Identifier = 2, I get 1.99 (not 1.56)


But I did a bit of guessing and came up with this - very simple and no error handling.

Thanks Steve, this was useful, however, is there a way to make a query so the results updated the columns within another table?

And yes, your correct it should be 1.99 that was a typo.

[ssanfu]

.... is there a way to make a query so the results updated the columns within another table?

I suppose you could use a query to look up the Multiplier, then use that query as the base in an update query to update a field in a different table.
But I would go the VBA way - so much easier and more control. (IMHO)