Subtract values of one record from another???

[Guest]

Is there any SIMPLE way for a query to subtract the values of one record from
another? Can the value be calculated in a field in the query. Such as
Difference 1, Difference 2, etc?

I want to take the latest 2 dates and subtract the less recent from the more
recent.
PLEASE HELP! This is driving me insane!!!

(e.g.)

Date Field 1 Field2 Field3 Field 4 Field 5
Dif 1 Dif 2
1. 10/01/06 10 8 10 10 10
4 2
2. 09/15/06 6 6 6 6
6 2 2
3 09/01/06 4 4 3 2
3


Thanks!!!

 

[Guest]

Tim:

Can you provide more context? The example is unclear to me. I suspect
there is a way to accomplish what you are attempting.

Seth

 

[Guest]

Your data example is confusing when compared to your explaination of what you
want to do.
You want to compare dates and subtract but you give three dates and 5 fields
of data and want 2 results.
Are you want to compare every instance of dates, each with the next in time
sequence? Then subtract every field?

Try these two queries.

Tim_1 ---
SELECT Date, Field1, Field2, Field3, Field4, Field5, (SELECT COUNT(*)
FROM [Tim] T1
WHERE T1.Date >= T.Date) AS Rank
FROM Tim AS T
ORDER BY Date DESC;


SELECT Tim_1.Date, Tim_1.Rank, Tim_1_1.Field1-Tim_1.Field1 AS Field1_Diff,
Tim_1_1.Field2-Tim_1.Field2 AS Field2_Diff, Tim_1_1.Field3-Tim_1.Field3 AS
Field3_Diff, Tim_1_1.Field4-Tim_1.Field4 AS Field4_Diff,
Tim_1_1.Field5-Tim_1.Field5 AS Field5_Diff
FROM Tim_1, Tim_1 AS Tim_1_1
WHERE (((Tim_1_1.Rank)=[Tim_1].[Rank]+1))
ORDER BY Tim_1.Rank;

 

[Guest]

Sorry about the confusion. It's actually pretty simple (I think.) I listed 3
records(dates) to show that there are mulitple entries in the table. I could
only fit 2 results in my example without the line wrapping (which it did
anyway.) So hence the 2 results should have been 5.

To clarify:

I only want to select the top 2 records. The two latest (by date.)
Then just subtract the field values of the record with the earlier date from
the later date.

Date Field 1 Field2 Field3 Field 4 Field 5
1. 10/01/06 10 8 10 10 10
2. 09/15/06 6 6 6 6
6

Results (Difference of)
Field 1 Field 2 Field 3 Field 4 Field 5
4 2 4 4
4


I hope this makes more sense!


Thanks!!!!

Your data example is confusing when compared to your explaination of what you
want to do.
You want to compare dates and subtract but you give three dates and 5 fields
of data and want 2 results.
Are you want to compare every instance of dates, each with the next in time
sequence? Then subtract every field?

Try these two queries.

Tim_1 ---
SELECT Date, Field1, Field2, Field3, Field4, Field5, (SELECT COUNT(*)
FROM [Tim] T1
WHERE T1.Date >= T.Date) AS Rank
FROM Tim AS T
ORDER BY Date DESC;


SELECT Tim_1.Date, Tim_1.Rank, Tim_1_1.Field1-Tim_1.Field1 AS Field1_Diff,
Tim_1_1.Field2-Tim_1.Field2 AS Field2_Diff, Tim_1_1.Field3-Tim_1.Field3 AS
Field3_Diff, Tim_1_1.Field4-Tim_1.Field4 AS Field4_Diff,
Tim_1_1.Field5-Tim_1.Field5 AS Field5_Diff
FROM Tim_1, Tim_1 AS Tim_1_1
WHERE (((Tim_1_1.Rank)=[Tim_1].[Rank]+1))
ORDER BY Tim_1.Rank;

Is there any SIMPLE way for a query to subtract the values of one record from
another? Can the value be calculated in a field in the query. Such as
Difference 1, Difference 2, etc?

I want to take the latest 2 dates and subtract the less recent from the more
recent.
PLEASE HELP! This is driving me insane!!!

(e.g.)

Date Field 1 Field2 Field3 Field 4 Field 5

1. 10/01/06 10 8 10 10 10
2. 09/15/06 6 6 6 6
6
3 09/01/06 4 4 3 2