Range based functions in Access

[Guest]

Hi,
I am converting calculations from Excel to Access (2003).
As a newbie in Access, I am trying to find out how to use functions with a
range over a set number of records.
Suppose I want to calc the average, max, min or StDev of price over previous
20 records, for every record between two set records, how do I do that?
I do see listings for DAve, DMax, DMin, DStDev, etc in the Expression
Builder. I tried the Help function, where the different sections are
explained, but there is no
example.
Can anyone give me an example of how these functions are used in the
Expression
Builder?

Thanks a lot,

 

[John Vinson]

Hi,
I am converting calculations from Excel to Access (2003).
As a newbie in Access, I am trying to find out how to use functions with a
range over a set number of records.
Suppose I want to calc the average, max, min or StDev of price over previous
20 records, for every record between two set records, how do I do that?
I do see listings for DAve, DMax, DMin, DStDev, etc in the Expression
Builder. I tried the Help function, where the different sections are
explained, but there is no
example.
Can anyone give me an example of how these functions are used in the
Expression
Builder?

First off:

Excel is a spreadsheet, a good one.
Access is a relational database.

THEY ARE DIFFERENT. Applying spreadsheet logic to Access will get you
in all *sorts* of trouble; you can drive nails with a crescent wrench,
but that doesn't make it a hammer!

Secondly:

A Table is NOT A SPREADSHEET. For one thing, there *are no row
numbers*; a table has no order, it's an unsequenced "heap" of data. So
there is *no such concept* as "the previous 20 records". You can
create a Query to select a subset of records, based on criteria
referencing fields within the table - but thinking of records
"between" two records is just going to get you into trouble.

Totals queries are going to be the solution - if indeed you don't
decide to link the tables to Excel and do these spreadsheet-type
calculations there! If you're trying to loosen bolts, a wrench might
be the tool of choice!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John, Thanks for your reaction.
Although still very much a newbie in Access (and will stay so for a while),
I think I am aware of most of the differences between Access and Excel. I am
able to build databses and the simpler calculating queries (like totals), but
would like to find which types of calculations still can be done in Access
and from what point on additional features need to be written in VBA or C++.
Depending on the Access book, the nomenclature for field versus columns and
records versus row is used differently; "Access Inside Out" uses them more
intermittendly than others.
I need to move away from Excel, since the Excel sheets which I have now
(manipulated by a C++ application) are getting too large and the calculations
are getting too slow. All the data I have really belongs in a database
anyhow. The question is how to best carry out the calculation part on these
data.
One option is to use a simple query to get the raw data into a guery table,
write some C++ code to write these data to Excel, carry out the calcs there
and use C++ code to write them back to associated records in Access. That
seems fairly straighforeward but may be overly complex due to the additional
Excel sheets used for calculations only.
I hope there is another solution using the same raw data query result table
and make additional queries using the Expression Builder. Is there?
How can the Expression Builder be used say to calculate a running standard
deviation for the last 20 days (did not find a good example anywhere sofar)?
Does SQL Server has more calculation features build in?
What are the options to approach this problem.

 

[John Vinson]

How can the Expression Builder be used say to calculate a running standard
deviation for the last 20 days (did not find a good example anywhere sofar)?

Without knowing anything about the structure of your tables it's hard
to say, but some "air code":

SELECT [groupingfield], StDev([field])
FROM tablename
WHERE [DateField] BETWEEN DateAdd("d", -20, Date()) AND Date()
GROUP BY [groupingfield];

would use the WHERE clause to select the most recent 20 days of data
(based not on row numbers but on the value in DateField), group by the
grouping field, and calculate the standard deviation of [field] over
that period for that group.

Does SQL Server has more calculation features build in?

Not in that sense. You could write Stored Procedures but they'd be
just about as much work.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John, I am going to try that. Once it works for one, it should be
about the same for similar functions.
I will let you know how I am doing.

Thanks again,

How can the Expression Builder be used say to calculate a running standard
deviation for the last 20 days (did not find a good example anywhere sofar)?

Without knowing anything about the structure of your tables it's hard
to say, but some "air code":

SELECT [groupingfield], StDev([field])
FROM tablename
WHERE [DateField] BETWEEN DateAdd("d", -20, Date()) AND Date()
GROUP BY [groupingfield];

would use the WHERE clause to select the most recent 20 days of data
(based not on row numbers but on the value in DateField), group by the
grouping field, and calculate the standard deviation of [field] over
that period for that group.

Does SQL Server has more calculation features build in?

Not in that sense. You could write Stored Procedures but they'd be
just about as much work.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps