Use offset within query?

[dab1477]

Thanks in advance for the assist.
I am attempting to create a calulation 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?

 

[scottylad2]

your quoting Fields, are these Fields the sum of columns or rows or what? or are the Fileds individual cells? It doesn't sound ttoo difficult, but would be easier if you can elaborate on what the Fields represent

 

[Joe4]

It is possible that you may have some database design issues as well (it is impossible to tell without knowing what each of the fields really represent). If your data is not Normalized, it could make the calculations much harder than they need to be.

You may want to take a look at this link here on Relational Database Design, especially the part on the rules on Normalization: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx.

Another things to consider is using User Defined Functions (UDFs) for complex calculations. I often find that easier than trying to write a complex calculation in a Calculated Query field.

 

[dab1477]

your quoting Fields, are these Fields the sum of columns or rows or what? or are the Fileds individual cells? It doesn't sound ttoo difficult, but would be easier if you can elaborate on what the Fields represent

These are columns within the query. Within the query, each column contains Field,Table, Sort, Show, Criteria, Or. I have multiple columns within the query.

 

[jackd]

I am attempting to create a calulation 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 really don't understand this

All these files reside within the main table of the query or within other tables used within the query

Try this is your query

FixedDays:iif(Field3=Field4,0,iif(Field5 =1,Field1,Field1+Field2))

 

[dab1477]

I am attempting to create a calulation 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 really don't understand this

All these files reside within the main table of the query or within other tables used within the query

Try this is your query

FixedDays:iif(Field3=Field4,0,iif(Field5 =1,Field1,Field1+Field2))

Let me try this as part of a better explanation...

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

What I'm trying to do is this:

The above are the results of a query (I've omitted some fields from actual query for clarity). Within that same query or an additional created 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.

 

[jackd]

Did you try the expression I provided based on your first post?

 

[dab1477]

Did you try the expression I provided based on your first post?

Yes. It does not consider the WC of the PRIOR record. This prior record issue is my problem. Thanks.