Use offset within query?

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
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

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,731
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jul 30, 2002
Messages
65
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,236
Code:
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
Code:
FixedDays:iif(Field3=Field4,0,iif(Field5 =1,Field1,Field1+Field2))
 

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
Code:
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
Code:
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,236
Did you try the expression I provided based on your first post?
 

Forum statistics

Threads
1,084,889
Messages
5,380,446
Members
401,679
Latest member
saffar

Some videos you may like

This Week's Hot Topics

Top