MS Query and calculation
 

I created a MS Query to retrieve data into excel, What I need to do is create
new fields in the query multiplying the fields that already exist times a
constant.

here is the SQL statement:

SELECT VW_PARENT_ROLL_LIST.S_PROLL_ID AS 'ROLL ID',
VW_PARENT_ROLL_LIST.D_PROLL_PTIME AS 'PROD TIME',
VW_PARENT_ROLL_LIST.S_SPEC_NAME AS 'GRADE',
VW_PARENT_ROLL_LIST.NT_TP_BP_AKD_REEL_TOTAL AS 'AKD',
VW_PARENT_ROLL_LIST.NT_TP_BP_POLYMER_REEL_TOTAL AS 'POLYMER',
VW_PARENT_ROLL_LIST.NT_TP_BP_HERCOBOND_REEL_TOTAL AS 'HERCOBOND',
VW_PARENT_ROLL_LIST.NT_Starch_Bar_Reel_Total AS 'STARCH BAR',
VW_PARENT_ROLL_LIST.NT_TP_DYE_REEL_TOTAL AS 'DYE',
VW_PARENT_ROLL_LIST.UOMX_203_ORIG_WT AS 'WT TONS'
FROM PROD_DMPC.dbo.VW_PARENT_ROLL_LIST VW_PARENT_ROLL_LIST
WHERE (VW_PARENT_ROLL_LIST.D_PROLL_PTIME=? And
VW_PARENT_ROLL_LIST.D_PROLL_PTIME<=?) AND (VW_PARENT_ROLL_LIST.S_SPEC_NAME=?)
ORDER BY VW_PARENT_ROLL_LIST.D_PROLL_PTIME

What I want to do is crear a new field that calculates the cost of using
POLYMER by multipling VW_PARENT_ROLL_LIST.NT_TP_BP_POLYMER_REEL_TOTAL times a
value that I want to be asked every time I run the query, this is because the
cost of Polymer changes with time and it is not a value in a field or table.

Basically the same apply for the rest of the columns, in the same way that I
want to creat new field for POLYMER COST, I want to create new fields for
AKD, HERCOBOND, STARCH and DYE.

I thought this can be solved as in Access, setting the new field like this:
VW_PARENT_ROLL_LIST.NT_TP_BP_POLYMER_REEL_TOTAL * [POLYMER COST] and once I
run the Query, it will ask me POLYMER COST? since it's a unknown variable,
but it did not work, I have a error message.

Is there a way I can do this?

Thanks, --
Cesar

MS Query and calculation
 

You can use an input box after pulling the data and put it in a loop.


Dim Message, Title, Default, MyValue
Message = "Enter a value between 1 and 3" ' Set prompt.
Title = "InputBox Demo" ' Set title.
Default = "1" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

VW_PARENT_ROLL_LIST.NT_TP_BP_POLYMER_REEL_TOTAL * MyValue

"Cesar" wrote:

I created a MS Query to retrieve data into excel, What I need to do is create
new fields in the query multiplying the fields that already exist times a
constant.

here is the SQL statement:

SELECT VW_PARENT_ROLL_LIST.S_PROLL_ID AS 'ROLL ID',
VW_PARENT_ROLL_LIST.D_PROLL_PTIME AS 'PROD TIME',
VW_PARENT_ROLL_LIST.S_SPEC_NAME AS 'GRADE',
VW_PARENT_ROLL_LIST.NT_TP_BP_AKD_REEL_TOTAL AS 'AKD',
VW_PARENT_ROLL_LIST.NT_TP_BP_POLYMER_REEL_TOTAL AS 'POLYMER',
VW_PARENT_ROLL_LIST.NT_TP_BP_HERCOBOND_REEL_TOTAL AS 'HERCOBOND',
VW_PARENT_ROLL_LIST.NT_Starch_Bar_Reel_Total AS 'STARCH BAR',
VW_PARENT_ROLL_LIST.NT_TP_DYE_REEL_TOTAL AS 'DYE',
VW_PARENT_ROLL_LIST.UOMX_203_ORIG_WT AS 'WT TONS'
FROM PROD_DMPC.dbo.VW_PARENT_ROLL_LIST VW_PARENT_ROLL_LIST
WHERE (VW_PARENT_ROLL_LIST.D_PROLL_PTIME=? And
VW_PARENT_ROLL_LIST.D_PROLL_PTIME<=?) AND (VW_PARENT_ROLL_LIST.S_SPEC_NAME=?)
ORDER BY VW_PARENT_ROLL_LIST.D_PROLL_PTIME

What I want to do is crear a new field that calculates the cost of using
POLYMER by multipling VW_PARENT_ROLL_LIST.NT_TP_BP_POLYMER_REEL_TOTAL times a
value that I want to be asked every time I run the query, this is because the
cost of Polymer changes with time and it is not a value in a field or table.

Basically the same apply for the rest of the columns, in the same way that I
want to creat new field for POLYMER COST, I want to create new fields for
AKD, HERCOBOND, STARCH and DYE.

I thought this can be solved as in Access, setting the new field like this:
VW_PARENT_ROLL_LIST.NT_TP_BP_POLYMER_REEL_TOTAL * [POLYMER COST] and once I
run the Query, it will ask me POLYMER COST? since it's a unknown variable,
but it did not work, I have a error message.

Is there a way I can do this?

Thanks, --
Cesar