Access 2010 Query Expression field calculation

[shahshah]

Hi ,
i have a table contains the following fields
personalID,Workplace,worktime(minutes)
every person could work in multi Workplaces.
i want to make a Query of this table, and to add a new field (Training) in minutes which hold an Expression,
(Training = 900 - worktime)
then
the next Training field should contain the (900- last training value) of the same PersonalID and Workplace..
i hope this is clear..
any Ideas??
thanks

 

[plog]

Could you provide some sample data of the table and then based on that sample data what the result would be from this query you want?

Also, define 'next Training field' when looking at 3 records of data how would you know which is next?

 

[shahshah]

thanks for your replay,
i have a table
personalID,workplace,worktime(minutes)
10 , AA1 , 300
30 , AB12 , 400
10 , AA1 , 200

Query
PersonalID, Workplace, worktime, Training
10 , AA1 , 300 , (900 - 300)----> (900 Minus worktime)
30 , AB12 , 400 , (900 - 400)
10 , AA1 , 200 , (900 - last training time)-----> which is in the above with same personalID and workPlace
.. it means, every person has only 900 minutes on EACH Workplace

and so on..
i don't know if i can do that with query expressions, or with VBA..
if VBA so how?

thanks

 

[plog]

The main problem you have is you keep using ordering words (First, Next, Last) without explicitly stating what the ordering criteria is. How come this is the correct order:

10 , AA1 , 300
10 , AA1 , 200

And not this:

10 , AA1 , 200
10 , AA1 , 300

You can make assumptions and see patterns, but in a table order does not exist. Order only exists in a database in a query where you explicitly use the ORDER BY clause. What field in your table do you use to order your table so that you can explicitly put it in the order you have described?

 

[shahshah]

sorry i didn't mentioned that i use the (date) order..so fields are ordered using dates

 

[plog]

Suppose this is your data:

personalID,workplace,worktime, workdate
10 , AA1 , 300 , 1/1/2012
10 , AA1 , 400 , 1/2/2012
10 , AA1 , 200 , 1/3/2012

Is this the data you want returned in your query:

PersonalID, Workplace, worktime, Training
10, AA1, 300, 600
10, AA1, 400, 300
10, AA1, 200, 600

If so, I can give you a method for calculating that, if not let me know what it should be.

 

[shahshah]

this is the supposed Query
PersonalID, Workplace, worktime, Training
10, AA1, 300, 600
10, AA1, 400, 200
10, AA1, 200, 0

i hope you got it now..thanks really

 

[plog]

I think I do but what you just showed me is not what you have been describing. In all your prior posts you calcualted the new Training time as (900-last training time). In this instance you've described it as (900 - the summation of all training times up to and including the current training time.)

 

[shahshah]

every person has 900 training minutes
when he work 300 minutes in AA1 then remains 600
the next Date he worked 400 minutes , now he has 200 minutes remains
then he worked 200 minutes, now he has 0 minutes remains

this is what i want the query to do

 

[shahshah]

any idea plog..thx

 

[plog]

Finally got it. Below is the SQL for your query:

SELECT YourTableNameHere.personalID, YourTableNameHere.workplace, YourTableNameHere.worktime, 900-DSum("[worktime]","YourTableNameHere","[workplace]='" & [workplace] & "' AND [workdate]<=#" & [workdate] & "#") AS Training
FROM YourTableNameHere;

Replace all instances of 'YourTableNameHere' with your actual table name. And I used 'workdate' as the datefield of your table by which you order your data.

 

[shahshah]

WOW it's working wonderful , thank you so much my Friend!