Adding in a field for every two records in another field

[cokeblue8]

Hi everyone!

I have a report based on a query which shows the actual and standard change-over times. Is there a way to get Access to show the difference (or (Act.- Std.) / Std. ) for every Act. and Std. change-over pair?

Thanks in advance

 

[ajetrumpet]

Are the "actual times" and "standard change-over times" just separate fields in the query??

If they are, add another field to the query as an expression (If what you said is what you want...you said you wanted the DIFFERENCE)...

SELECT [act], [std.], ([act]-[std.]) AS [Difference Between the Two]

If in fact, you want what you wrote in your first post (([Act]-[Std.]) / [Std.]), then just write that expression in the query and assign the temporary field name to it...

SELECT [act], [std.], (([act]-[std.]) / [std.]) AS [The expression in your first post]

A lot of people wouldn't know this, but the "order of operations" actually comes into play here, because its rule conflicts with the "left-to-right" nature in which the expression is written. E.G. - (([act] - [std.]) / [std.]) will not yield the same result as ([act] - [std.] / [std.])

I have actually seen it cause many heachaches among users.

 

[cokeblue8]

Act. and Std. are in the same field. The reason for this is because Act. and Std. change-over times are a subset of Machine problems.

 

[ajetrumpet]

How do the values read in your field??

Are they numbers??

Are they separated by a delimiter??

Like ##, ##??

Act = Actual?? & Std. = Standard Deviation??

 

[cokeblue8]

When I use "Act." I mean actual changeover, and "Std." to mean standard changeover.

Simplified example of what I have:

Date Problem Name Duration


09/05/07 Act. 120
09/05/07 Std. 100
09/10/07 Act. 90
09/10/07 Std. 100


Is there a way to have another column that shows ((Act.-Std.)/Std.) for every PAIR (Act. and Std.) ?

 

[ajetrumpet]

Coke,

Is there anyway you can get your data in this format...?

9/5/07 Act. 120 Std. 100
9/10/07 Act. 90 Std. 100

I would say this format would be closer to a normalized version of your data.

To do what you want the way you have your data set up right now would probably take a "Do While" Loop in Visual Basic on a newly created field.

I have written plenty of complicated queries in Access, but because a table's unit of measure (data) is a record, it is very difficult (if not impossible) to perform operations on data vertically using the built-in query operation (unless of course you are using a column-oriented function, like DSUM or something).

Have you tried posting this in the VBA section of the forum??

 

[cokeblue8]

Thanks for all the help, ajetrumpet. I really appreciate it!

I thought about having two separate queries (one for each of the changeover types) and unioning the two (so the act. and std. would be on one record), but I thought that there would be a simpler way that I might have overlooked.

In the end, I guess it isn't that difficult. Thanks again!