need help with calculating fields

[Cam]

Hello,

I have an Access Database 2000 to track performance. Within the db I have a
performance table where user input their ID, date, part#, start sequence &
stop sequence daily (there can be two entries per day). Then on another table
with field: part#, sequence#, time (min).

What I would like to calculate in the query and report is look at the start
& stop sequence based on the part#, then calculate how long (time) the
operator spend on the job (part#). Thanks

Example:
Table1
ID date part# start seq stop seq
001 1/8/08 1360-1 100 500
002 1/10/08 1560-3 600 1000

Table2
part# start seq time (min)
1360-1 100 5
1360-1 200 10
1360-1 300 10
1360-1 400 30
1360-1 500 15
1360-1 600 60
1560-3 100 10
...........

Result
query or report output:
ID date part# start seq stop seq time (min)
001 1/8/08 1360-1 100 500 70

 

[Marshall Barton]

I have an Access Database 2000 to track performance. Within the db I have a
performance table where user input their ID, date, part#, start sequence &
stop sequence daily (there can be two entries per day). Then on another table
with field: part#, sequence#, time (min).

What I would like to calculate in the query and report is look at the start
& stop sequence based on the part#, then calculate how long (time) the
operator spend on the job (part#). Thanks

Example:
Table1
ID date part# start seq stop seq
001 1/8/08 1360-1 100 500
002 1/10/08 1560-3 600 1000

Table2
part# start seq time (min)
1360-1 100 5
1360-1 200 10
1360-1 300 10
1360-1 400 30
1360-1 500 15
1360-1 600 60
1560-3 100 10
..........

Result
query or report output:
ID date part# start seq stop seq time (min)
001 1/8/08 1360-1 100 500 70

SELECT ID, [date], [part#], [start seq], [stop seq],
Sum([time] As TotalTime
FROM table1 INNER JOIN Table2
ON Table2.[time] Between Table1.[start seq] And
Table1.[stop seq]
GROUP BY [part#]

 

[Cam]

Marshall,

Thanks for the help. But the code you provided I am not sure where to put
them.
In the query or report or field. Can you please give me more details?

I have an Access Database 2000 to track performance. Within the db I have a
performance table where user input their ID, date, part#, start sequence &
stop sequence daily (there can be two entries per day). Then on another table
with field: part#, sequence#, time (min).

What I would like to calculate in the query and report is look at the start
& stop sequence based on the part#, then calculate how long (time) the
operator spend on the job (part#). Thanks

Example:
Table1
ID date part# start seq stop seq
001 1/8/08 1360-1 100 500
002 1/10/08 1560-3 600 1000

Table2
part# start seq time (min)
1360-1 100 5
1360-1 200 10
1360-1 300 10
1360-1 400 30
1360-1 500 15
1360-1 600 60
1560-3 100 10
..........

Result
query or report output:
ID date part# start seq stop seq time (min)
001 1/8/08 1360-1 100 500 70

SELECT ID, [date], [part#], [start seq], [stop seq],
Sum([time] As TotalTime
FROM table1 INNER JOIN Table2
ON Table2.[time] Between Table1.[start seq] And
Table1.[stop seq]
GROUP BY [part#]

 

[John W. Vinson]

Thanks for the help. But the code you provided I am not sure where to put
them.
In the query or report or field. Can you please give me more details?

It's a Query. Create a new query, not adding any tables. Select View... SQL to
see the SQL view of the query (that's the *real* query, the grid is only a
tool to build SQL); copy and paste Marshall's SQL; change the table and
fieldnames to match your table and fieldnames and try it out.

John W. Vinson [MVP]