Help?

[Cam]

Hello,

Within the db I have a performance table where user input their ID, date,
part#, side, startseq & stopseq daily. Then on another reference table with
field: part#, side, seq, time (min).

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

Example:
Table1
ID date part# side startseq stopseq
001 1/8/08 1360-1 1st 100 1000
002 1/10/08 1360-1 1st 300 400

Table2
part# side seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 1000 15
1360-1 1st 1200 60
1560-3 2nd 100 10
...........

Result for query:
ID date part# side start seq stop seq Total time (min)
001 1/8/08 1360-1 1st 100 1000 70

 

[Marshall Barton]

Within the db I have a performance table where user input their ID, date,
part#, side, startseq & stopseq daily. Then on another reference table with
field: part#, side, seq, time (min).

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

Example:
Table1
ID date part# side startseq stopseq
001 1/8/08 1360-1 1st 100 1000
002 1/10/08 1360-1 1st 300 400

Table2
part# side seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 1000 15
1360-1 1st 1200 60
1560-3 2nd 100 10
..........

Result for query:
ID date part# side start seq stop seq Total time (min)
001 1/8/08 1360-1 1st 100 1000 70

I think this should do that:

SELECT Table1.[part#], Table1.side,
Table1.startseq, Table1.stopseq,
Sum(Table2.[time (min)]) As [Total time (min)]
FROM Table1 INNER JOIN Table2
ON Table2.seq >= Table1.startseq
And Table2.seq < Table1.stopseq
GROUP BY Table1.[part#], Table1.side,
Table1.startseq, Table1.stopseq

You can not represent that kind of join in the query design
grid so you must work with the query in SQL view.