Calculating the difference between two records in the same field

[Guest]

I have a database that I need to calculate the differene between Period 1
Total dated 2/5/2005 and Period 1 total dated 2/15/2005. There are many
enteries in the Period 1 Total field so I prompt for a date range to find the
difference between the two dates.

 

[ti976]

in general use datediff function:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
(more detail in VBA help)

to make access query:
use groupby function (sigma button) set 2 date's total criteria to min
and max; make one date field as expression to limit date range and one
field has the datediff function that reference the min & max date.

modify sql statement below to fit your data structure (mod tblDate to
your tablename) and place in an SQL query view (switch to designview if
you want to see it that way).
Run it and enter the limiting dates.

SELECT Min(tblDate.[Period 1]) AS minDate, Max(tblDate.[Period 1]) AS
maxDate, DateDiff("d",[minDate],[maxDate]) AS dateD
FROM tblDate
WHERE (((tblDate.[Period 1])>[mindate] And (tblDate.[Period
1])<[maxdate]));

 

[Guest]

These are not date records. They are currency totals. I need to figure out
what the difference is between the two totals from week to week. I need to
take the total from last week and subtract or add the total of this week.

Thank you for your quick response. I should have been more detailed.
Kel

 

[ti976]

I don't see any other choice but to build 2 queries, one to sum the
currency for each week and then subtract the sum field in a third query

 

[Guest]

OK. Thank you for your help. I was hoping there would be way to do this in
the query. I'll give that a try.

 

[Guest]

You can have the query request the dates you want to compare each time you
run it, then you can have only one query and it will change each time.

In the date fields put Between [[Field to query]![Beginning Date] And
[Field]![Ending Date]

This way it will prompt you to enter the dates.