Calculations within query

[dab1477]

Thanks in advance for the assist.
I am attempting to create a calculation within a query that says the following: Fixed Days: field1+Field2
My issue is as follows: I need the query to perform the following in my Fixed Days calculation column.
If Field 3 = field 4, then Fixed Days =0, else Fixed Days = field1+Field2 or if Field 5= 1, then Fixed Days = Field 1, else Fixed Days = field1+Field2.
All these files reside within the main table of the query or within other tables used within the query

I believe this needs to be done using multiple queries, but am not sure how to set up. If I can do this using VBA, I'm not sure how to do this. I am an elementary user of VBA and would like to code using this. I need an assist in getting started. Any ideas?

 

[SkipVought]

hi,

this can be done in SQL code... no VBA required.

But you need to correct you logic

If Field 3 = field 4, then 
   Fixed Days =0 
else 
   Fixed Days = field1+Field2
End If

where does this OR happen???

 or if Field 5= 1, then Fixed Days = Field 1, else Fixed Days = field1+Field2.

maybe like this???

If Field 3 = field 4, then 
   Fixed Days =0 
else 
   if Field 5= 1 then 
      Fixed Days = Field 1
   else 
      Fixed Days = field1+Field2
   end if
End If

which in MS Access or MS Query SQL is

IIF([Field 3] = [field 4], 0,IIF([Field 5]= 1,[Field 1],[Field1]+[Field2]))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dab1477]

Skip, you are correct. That would work. I failed to include the fact that there is an offset in rows for fields 3 & 4. I've uploaded an excel sheet of my query.
I see that I need to restate my premise, as I initially mis-stated.
in the excel sheet example, cells g4 = g5, then I need fixed days for row 5 to be 0. If not, then Fixed Days = p5+q5 for row 4. What I think i want is this:

each row i will create a filed titled Fixed Days. IN this excel example, that will be X column.
For each cell in X, I need to state the following:
if (x2=x1,0,if d2=1, p2, p2+q2). As this gets entered for each row, I believe I need to tell the funciton to offset in some manner (i.e. x3=x2, etc). I'm not sure how to do this within the query.

Thanks for your help so far. I hope you can point me in the right direction. Sorry for not being clear initally.

 

[SkipVought]

many of us cannot access posted files doe to company security restrictions.

If you would like prompt assisitance, please post here directly with an appropriate example.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dab1477]

Sorry. Here is my Excel example:

Part_Nbr Next_Seq Current_Seq Step. Prev_Seq Std_Op Work-Center_Nbr Plan_Run_Mach_Time Plan_Run_Lbr_Time Plan_Su_Mach_Time Op_Start_Days Op_Finish_Days Plan_Su_Lbr_Time Lt_Su_Days CALC_Avail_Hrs_Day Wc_Queue_Days IDM
N2110S-06-350 10 5 1 0 P01 240 0.000 0.000 0.000 0.630 0.630 0.000 0.000 8.000 0.130 0.130
N2110S-06-350 20 10 2 5 CNC 1.408 0.352 0.500 2.010 7.220 0.500 0.030 14.908 1.250 0.130
N2110S-06-350 30 20 3 10 605 0.000 0.000 1.000 7.850 7.980 1.000 0.130 8.000 0.500 0.130
N2110S-06-350 40 30 4 20 605 0.000 0.000 1.000 7.980 8.110 1.000 0.130 8.000 0.500 0.130
N2110S-06-350 50 40 5 30 620 0.000 0.000 1.000 8.990 9.120 1.000 0.130 8.000 0.750 0.130
N2110S-06-350 60 50 6 40 301 0.000 0.000 0.750 9.750 9.840 0.750 0.090 8.000 0.500 0.130
Sorry I can't make this show clearer. Doesn't seem to be enough room.

 

[SkipVought]

So with respect to your example, please state your issue with specifics.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dab1477]

Part_Nbr Step # WC# QueueDays IDM Fixed Days
N2110S-06-350 1 240 0.13 0.13
N2110S-06-350 2 CNC 1.25 0.13
N2110S-06-350 3 605 0.5 0.13
N2110S-06-350 4 605 0.5 0.13
N2110S-06-350 5 620 0.75 0.13

Skip,
What I'm trying to do is this:

The above are the results of a query. Within that same query or an additional query, I need something that will calculate Fixed Days for each record as follows:
If WC# of a Record with Step # >1 = WC# of PRIOR record, Fixed Days = 0, if WC# of a Record with Step # >1 is not equal to WC# of PRIOR record, then Fixed Days = QueueDays + IDM or if Step # = 1 , then Fixed Days = QueueDays, otherwise, Fixed Days = QueueDays + IDM
I believe this requires some type of offset to account for the WC# of current vs Prior record. I' m not sure if this is required, nor do I know how to create this logic within a query. If I can't do within query, how do I create VBA to do the same thing? The desired outcome is that when I run this query that Fixed Days is calculated for EACH RECORD per the above.

You can see from Step# 3 & 4, that I need a calculation that looks at an offset within the query - at least that is what I think.
Thanks so much for you patience and direction.