asked on
Multiplying a column when record does not appear on a separate table
Hello,
I would like to multiple a column by a % unless a corresponding record is located in a separate table. For example, in the attached database, the query multiplies the entire Item Table by 90%. I would like for the query to not apply the 90% to Item# 2 since it appears on the Nondiscount Table. The expected output should appear as below, where all values are multipled by 90% (Expr1) except record 2.
Query1
Item# Value1 Expr1
1 $60.00 $54
2 $300.00 $300
3 $700.00 $630
4 $900.00 $810
Database-Price2.mdb
I would like to multiple a column by a % unless a corresponding record is located in a separate table. For example, in the attached database, the query multiplies the entire Item Table by 90%. I would like for the query to not apply the 90% to Item# 2 since it appears on the Nondiscount Table. The expected output should appear as below, where all values are multipled by 90% (Expr1) except record 2.
Query1
Item# Value1 Expr1
1 $60.00 $54
2 $300.00 $300
3 $700.00 $630
4 $900.00 $810
Database-Price2.mdb
Microsoft Access
Last Comment
Gustav Brock
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SELECT
[Item Table].[Item#],
[Item Table].Value1,
CCur(Nz([NonDiscount Table]![Value1],[Item Table]![Value1]*0.9)) AS NewValue
FROM
[Item Table]
LEFT JOIN
[NonDiscount Table]
ON [Item Table].[Item#] = [NonDiscount Table].[Item#];
/gustav