lookup

[Guest]

Hi I have a basic query

I wish to look up an invoice number in a table and for it to return the date
value,

table 1 invoice range
invoice from - invoice to - period (yymm)
96300 96399 0504
96400 96499 0505

table 2 invoice details
invoice no - value
96301 100
96302 100
96401 100

qry to return
period - value
0504 200
0505 100

 

[Dale Fye]

Robbie,

Try this, untested.

Select IR.Period, SUM(ID.Value) as PeriodValue
FROM [Invoice Range] IR, [Invoice Details] ID
WHERE ID.[Invoice No] BETWEEN IR.[Invoice From] AND IR.[Invoice To]
GROUP BY IR.Period

Hope this Helps

Dale