calculated fields in tables/ queries

[John young]

I have a table which has fields "year", "odometer", and "annual"....
what i currently do is enter manually data for all fields,... what i
want to have happen is the annual figure to be added automatically by
subtracting the previous year odo reading from current year reading
and haveing that value placed in "annual" field
Can any one assist with this ???? I am an "intermediate" user
Access Version is XP 2000

[fredg]

On 30 Jul 2004 19:56:25 -0700, John young wrote:

I have a table which has fields "year", "odometer", and "annual"....
what i currently do is enter manually data for all fields,... what i
want to have happen is the annual figure to be added automatically by
subtracting the previous year odo reading from current year reading
and haveing that value placed in "annual" field
Can any one assist with this ???? I am an "intermediate" user
Access Version is XP 2000

You have a couple of things you need to 'fix' first.
1) You have a field named "Year".
Year is a reserved word in Access/vba and should not be used as a
field name.
See the appropriate Microsoft Knowledgebase article:

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

Change the name of the Year field to something else.
In my example below I named it "ForYear".

2) All you need to save in your table for this is the Year and the
Odometer reading. You do not need the "Annual" field. The annual
mileage is a calculated field and need not be saved in any table.
Whenever you need the mileage for any given year, simply run the
calculation.

3) To calculate the mileage for the years in the table, here is a
query SQL which will do so.

SELECT YourTable.ForYear, YourTable.Odometer,
Abs(DMax("[Odometer]","YourTable","[ForYear]<" &
[ForYear])-[Odometer]) AS AnnualMileage
FROM YourTable;

The above assumes ForYear and [Odometer] are number datatype with Year
being an Integer and Odometer a Long Integer Size.

You can also use Text datatypes for those 2 fields, but then the SQL
needs a small change.

Here is a sample of the data returned

ForYear Odometer AnnuaMileage
1999 15236
2000 28237 13001
2001 42856 14619
2002 62123 19267
2003 78123 16000

If you would rather have the latest year on top, Order By Descending.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[John Young]

Fredg
thanks for that ....I have made changes to table/fieldsand modified your
sql example to fit ....but i get a completly wrong result.. one thinh i
did not mention is the original table has a Vehicle.ID field as there
are a number of different vehicles that data is being held for ( a
vintage car club)
here is my sql with additional selection material to select a car ..id
328

SELECT YourTable.VehicleID, YourTable.period, YourTable.Odometer,
Abs(DMax("[Odometer]","YourTable","[Period]<" & [Period])-[Odometer])
AS Annual
FROM YourTable
WHERE (((YourTable.VehicleID)=328))
ORDER BY YourTable.period DESC;

Here are the results as per above

VehicleID period Odometer Annual
328 2004 482 571330
328 2003 334 571478
328 2002 99882 471930
328 2001 97111 474701
328 2000 96411 3145
328 1999 95647

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Young]

Fredg
thanks for that ....I have made changes to table/fieldsand modified your
sql example to fit ....but i get a completly wrong result.. one thinh i
did not mention is the original table has a Vehicle.ID field as there
are a number of different vehicles that data is being held for ( a
vintage car club)
here is my sql with additional selection material to select a car ..id
328

SELECT YourTable.VehicleID, YourTable.period, YourTable.Odometer,
Abs(DMax("[Odometer]","YourTable","[Period]<" & [Period])-[Odometer])
AS Annual
FROM YourTable
WHERE (((YourTable.VehicleID)=328))
ORDER BY YourTable.period DESC;

Here are the results as per above

VehicleID period Odometer Annual
328 2004 482 571330
328 2003 334 571478
328 2002 99882 471930
328 2001 97111 474701
328 2000 96411 3145
328 1999 95647
As you can see ...not quiet rite

Where Have I Gone Wrong

P.S Ignore previous reply as appears i hit wrong button and sent only
part message

Cheers
John


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Young]

fredg
should have mentioned that your original reply works well if only
one vehicle is involved but we have lots of vehicles ID's
cheers
John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

Just add the VehicleID to the DMax() function's criteria. E.g.:

Abs(DMax("[Odometer]","YourTable","[Period]<" & [Period] & " AND
VehicleID=" & [VehicleID]))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
John Young wrote:

Fredg
thanks for that ....I have made changes to table/fieldsand modified your
sql example to fit ....but i get a completly wrong result.. one thinh i
did not mention is the original table has a Vehicle.ID field as there
are a number of different vehicles that data is being held for ( a
vintage car club)
here is my sql with additional selection material to select a car ..id
328

SELECT YourTable.VehicleID, YourTable.period, YourTable.Odometer,
Abs(DMax("[Odometer]","YourTable","[Period]<" & [Period])-[Odometer])
AS Annual
FROM YourTable
WHERE (((YourTable.VehicleID)=328))
ORDER BY YourTable.period DESC;

Here are the results as per above

VehicleID period Odometer Annual
328 2004 482 571330
328 2003 334 571478
328 2002 99882 471930
328 2001 97111 474701
328 2000 96411 3145
328 1999 95647

[John Young]

MGFoster
.... thanks for your reply but am unable to get that ammendment to work
either..... it gived me a zero ( 0 ) in the calculated field.... have
looked at the Dmax() Method and can not get it to work as suggested
.....

Cheers
John
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well I should have read the original query w/ more attention. Instead
of DMAX() it should be using the DSUM(), and, the Period comparison
should be less than or equal to (<=), and, the Abs() function isn't
needed [presumably, the odometer reading will always be a positive
number]. Try this:

SELECT VehicleID, [Period], Odometer, DSum("[Odometer]", "YourTable",
"[Period] <= " & [Period] & " AND VehicleID=" & [VehicleID]) AS Annual
FROM YourTable
ORDER BY VehicleID, [Period] DESC

This assumes Period and VehicleID are both numeric data types.

This query will sum the Odometer for the current period & all previous
periods for each VehicleID in the table. This is what's know as a
running-sum query. Here is a MS KB article that explains:

http://support.microsoft.com/support.../q138/9/11.asp

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ6Hq4echKqOuFEgEQJocACguD4+dOEqy7o5x/Ix1ejNzCebeF4An30p
j9MlhvEn61V0NTl35VAmrQxX
=oYjk
-----END PGP SIGNATURE-----

John Young wrote:

MGFoster
... thanks for your reply but am unable to get that ammendment to work
either..... it gived me a zero ( 0 ) in the calculated field.... have
looked at the Dmax() Method and can not get it to work as suggested
....

[John Young]

MGFoster ...

your amendment works for giving running totals as you describe. but
what i am look ing for is annual total ie difference between this
years and last years figure
i.e. 2003 year was 40000 LAST year was 35000 giving a "annual total" of
5000....
...as you assumed all data is numeric..

I have tried various formula / criteria and have so far got no
satisfactory result ....

NOTE... need for this figure is for annual transportation department
returns for each vehicle
cheers ..and thanks for your assistance
John


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah... Maybe one of these (these qrys assume there is only one odometer
reading per period):

SELECT VehicleID, [Period], Odometer, [Odometer] - DLookup("[Odometer]",
"YourTable", "[Period] = " & [Period]-1 & " AND VehicleID=" &
[VehicleID]) AS Annual
FROM YourTable
ORDER BY VehicleID, [Period] DESC

Same version using a subquerey (might be faster 'cuz can use indexes):

SELECT VehicleID, [Period], Odometer,
Odometer - (SELECT Odometer FROM YourTable WHERE Period = T.Period-1 AND
VehicleID = T.VehicleID) AS Annual
FROM YourTable As T
ORDER BY VehicleID, [Period] DESC

If the periods are not sequential, try this:

SELECT VehicleID, [Period], Odometer,
Odometer - (SELECT Odometer FROM YourTable WHERE
Period = (SELECT MAX(Period) FROM YourTable WHERE Period < T.Period)
AND VehicleID = T.VehicleID) AS Annual
FROM YourTable As T
ORDER BY VehicleID, [Period] DESC

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ/W0oechKqOuFEgEQLLwwCgmN1Q9nNb0q3ampd7G+NsRlHMEqMAo P0W
yrK4hG0fiRpFvrfhcsSx2hYl
=Y5fn
-----END PGP SIGNATURE-----
John Young wrote:

MGFoster ...

your amendment works for giving running totals as you describe. but
what i am look ing for is annual total ie difference between this
years and last years figure
i.e. 2003 year was 40000 LAST year was 35000 giving a "annual total" of
5000....
...as you assumed all data is numeric..

I have tried various formula / criteria and have so far got no
satisfactory result ....

NOTE... need for this figure is for annual transportation department
returns for each vehicle

[John Young]

MGFoster ..

Success at last ...for a moment there i thought it mite be in the
two hard basket ..but your solution is just what i was looking for
.....I dont do a lot of work in access so when i need to i am not
"expert" enough to know all the finer 'hidden' solutions.... It is good
to know there is help close to hand....

Mant thanks for your assistance.....

Cheers

John Y

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!