Using Start and End for a Series to Determine Batch

[francisco]

Hello

I have a pretty simple problem but am having a heck of a time getting it to d
what I want. I have a table which defines batches of coupons that have bee
created. For each batch a Starting and Ending Serial Number are defined

I also have a table that indicates which coupons have been redeemed whic
includes individual serial numbers

I would like to come up with the following queries
1) For each batch, which coupons have been redeemeed
2) For each coupon, which batch does it pertain to

I've tried coding this in both a table trying to apply some sort of logica
expression as well as creating a query but have really failed in getting th
desired result

Thanks

 

[Marshall Barton]

I have a table which defines batches of coupons that have been
created. For each batch a Starting and Ending Serial Number are defined.

I also have a table that indicates which coupons have been redeemed which
includes individual serial numbers.

I would like to come up with the following queries:
1) For each batch, which coupons have been redeemeed?
2) For each coupon, which batch does it pertain to?

To get the batch number for each redeemed coupon, use a
query like:

SELECT tblredeemed.serialnum, tblbatches.batchnum
FROM tblredeemed INNER JOIN tblbatches
ON tblredeemed.serialnum Between tblbatches.serialstart
And tblbatches.serialend

IMPORTANT: You can not do that kind of join in the query
designer so stay in SQL view. If that's too tough a
restriction, you can get the equivalent in the query
designer from:
SELECT tblredeemed.serialnum, tblbatches.batchnum
FROM tblredeemed, tblbatches
WHERE tblredeemed.serialnum Between tblbatches.serialstart
And tblbatches.serialend

I suspect, but have not verified, that the performance
should be the same?