Calculation on incrementing record.

I have a probably simple question. I have a db with a usage field that
increments daily. When I query it I want to find out the difference in the
usage field between the start date in the query and the end date in the
query. In other words, what was the usage between those dates?

Thanks for any help.

Phil

Simplest example would be the following, assuming the Usage always
increases.

SELECT Max(Usage) - Min(Usage) as theDiff
FROM YourTable
WHERE UsageDate Between #1/1/2001# and #1/31/2001#

Sorry for the generic answer, but I didn't see many clues.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Phil" wrote in message
...
I have a probably simple question. I have a db with a usage field that
increments daily. When I query it I want to find out the difference in the
usage field between the start date in the query and the end date in the
query. In other words, what was the usage between those dates?

Thanks for any help.

Phil

Phil:

Try something like this:

PARAMETERS
[StartDate:] DATETIME,
[EndDate:] DATETIME;
SELECT
MAX(usage) – MIN(usage) AS totalusage
FROM YourTable
WHERE usagedate = [StartDate:]
AND usagedate [EndDate:] + 1;

Ken Sheridan
Stafford, England

"Phil" wrote:

I have a probably simple question. I have a db with a usage field that
increments daily. When I query it I want to find out the difference in the
usage field between the start date in the query and the end date in the
query. In other words, what was the usage between those dates?

Thanks for any help.

Phil

Thanks John, I think that might be it, but I'm an Access newbie. Where do I
enter that code?

Phil

"John Spencer" wrote:

Simplest example would be the following, assuming the Usage always
increases.

SELECT Max(Usage) - Min(Usage) as theDiff
FROM YourTable
WHERE UsageDate Between #1/1/2001# and #1/31/2001#

Sorry for the generic answer, but I didn't see many clues.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Phil" wrote in message
...
I have a probably simple question. I have a db with a usage field that
increments daily. When I query it I want to find out the difference in the
usage field between the start date in the query and the end date in the
query. In other words, what was the usage between those dates?

Thanks for any help.

Phil

That is a query.
Open a new query in design view
Select your table
Select View: SQL from menu
Paste in the code and replace field and table names with yours
Select Query Run

If it runs you have constructed a query.

Switch back to the design window if you want to see what it looks like with
the grid.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Phil" wrote in message
...
Thanks John, I think that might be it, but I'm an Access newbie. Where do
I
enter that code?

Phil

"John Spencer" wrote:

Simplest example would be the following, assuming the Usage always
increases.

SELECT Max(Usage) - Min(Usage) as theDiff
FROM YourTable
WHERE UsageDate Between #1/1/2001# and #1/31/2001#

Sorry for the generic answer, but I didn't see many clues.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Phil" wrote in message
...
I have a probably simple question. I have a db with a usage field that
increments daily. When I query it I want to find out the difference in
the
usage field between the start date in the query and the end date in the
query. In other words, what was the usage between those dates?

Thanks for any help.

Phil

Thanks John, it works. I have another issue when I add it into my existing
query, but i'll post that separately.

Phil

"John Spencer" wrote:

That is a query.
Open a new query in design view
Select your table
Select View: SQL from menu
Paste in the code and replace field and table names with yours
Select Query Run

If it runs you have constructed a query.

Switch back to the design window if you want to see what it looks like with
the grid.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Phil" wrote in message
...
Thanks John, I think that might be it, but I'm an Access newbie. Where do
I
enter that code?

Phil

"John Spencer" wrote:

Simplest example would be the following, assuming the Usage always
increases.

SELECT Max(Usage) - Min(Usage) as theDiff
FROM YourTable
WHERE UsageDate Between #1/1/2001# and #1/31/2001#

Sorry for the generic answer, but I didn't see many clues.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Phil" wrote in message
...
I have a probably simple question. I have a db with a usage field that
increments daily. When I query it I want to find out the difference in
the
usage field between the start date in the query and the end date in the
query. In other words, what was the usage between those dates?

Thanks for any help.

Phil