Multi-step query/automation problem

[Tony in Michigan]

Hello,
I have a multi step problem that I'm trying to streamline and automate. All
of the required fields are in the same table. I'd like to reduce the number
of queries, and especially the hand work that the user applies to the data.

Step 1,
I can't get the syntax correct (still a newbie)
I need to calculate and update field P_Price.

Here is the formula I need to get to work.

If Q1 > 0 then P_price = Value/Ql, If Q1=0 then P_price = value/Q2


Step 2

I need to group by part_num and determine if the calculated P_Price is +/-
ValueRange% from median P_price. This may be a static value, or possible
user input, or at some later point, calculating ½* (Max-min P_price) and
averaging this result by source country.

Step 3
I need to output the out of range results to a table by transaction # and
invoice# and add calculated field variance – Range%

 

[John Spencer MVP]

The calculation would use the expression below and there should be no real
reason to actually store the calculation results. It can always be calculated.
IIF(Q1>0,[Value]/Q1,[Value]/Q2)


SELECT Part_Num
, IIF(Q1>0,[Value]/Q1,[Value]/Q2) as P_Price
FROM SOMETable
WHERE IIF(Q1>0,[Value]/Q1,[Value]/Q2)>[MedianPrice]* (1+[ValueRange])
OR IIF(Q1>0,[Value]/Q1,[Value]/Q2)<[MedianPrice]* (1-[ValueRange])

That might get you started. Your description of what you need done is a bit
confusing. For instance, in STEP 2 you introduce averaging a result by
country. So is the median price by country, is Part_Num and Q1, Q2, and Value
by country also.

Then Step 3 you introduce Transaction numbers and invoice numbers.

Good luck with solving your problem.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tony in Michigan]

Hi John,

Thanks for the SQL.

In step 2 I was considering three potential options for value range.
Foreign exchange fluctiuation would account for the acceptable variation,
input error would account for the rest. Input error would cause errors in
what was reported to the government.

A static number, say 5%
A user determined input perhaps based on foreign exchange fluxuation
or a calculated value based on the range of variation for a given
country/currency.

step three. >>>> newbie error <<<<
I just need to add the transaction and invoice fields in the query.

The calculation would use the expression below and there should be no real
reason to actually store the calculation results. It can always be calculated.
IIF(Q1>0,[Value]/Q1,[Value]/Q2)


SELECT Part_Num
, IIF(Q1>0,[Value]/Q1,[Value]/Q2) as P_Price
FROM SOMETable
WHERE IIF(Q1>0,[Value]/Q1,[Value]/Q2)>[MedianPrice]* (1+[ValueRange])
OR IIF(Q1>0,[Value]/Q1,[Value]/Q2)<[MedianPrice]* (1-[ValueRange])

That might get you started. Your description of what you need done is a bit
confusing. For instance, in STEP 2 you introduce averaging a result by
country. So is the median price by country, is Part_Num and Q1, Q2, and Value
by country also.

Then Step 3 you introduce Transaction numbers and invoice numbers.

Good luck with solving your problem.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,
I have a multi step problem that I'm trying to streamline and automate. All
of the required fields are in the same table. I'd like to reduce the number
of queries, and especially the hand work that the user applies to the data.

Step 1,
I can't get the syntax correct (still a newbie)
I need to calculate and update field P_Price.

Here is the formula I need to get to work.

If Q1 > 0 then P_price = Value/Ql, If Q1=0 then P_price = value/Q2


Step 2

I need to group by part_num and determine if the calculated P_Price is +/-
ValueRange% from median P_price. This may be a static value, or possible
user input, or at some later point, calculating ½* (Max-min P_price) and
averaging this result by source country.

Step 3
I need to output the out of range results to a table by transaction # and
invoice# and add calculated field variance – Range%