Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database query and calculation | Excel Programming | |||
Named Ranges & Calculation Times Query | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |