on integers.i between 1 and yourtable.quantity
will work for me in Access if I start a new query, go into SQL view, and enter the SQL string. I can switch between SQL view and worksheet view and everything seems fine. Then I can save the query with a name. But then if I try to open the query I just created, either in Open or Design mode, it will error out with this message:
Between operator without And in query expression 'integers.i Between 1'
It is easy to fix by using "integers.i >=1 And...." , but I was wondering why Access fails with the "i Between 1 And quantity" expression.
I wasn't able to get r937's version to work either. But if you are looking for something that works I will throw in a possibility. r937 will probably cringe and I will warn you that it won't be efficient with large numbers of records. I am not sure what you are using it for, but hopefully it is for printing small batches of labels or at least small range of values (1 to 10).
FROM yourtable, integers
WHERE (yourtable.quantity<>0) And (integers.i) Between 1 And (yourtable.quantity)
ORDER BY yourtable.name;
It will take all of the possible names and pair them up with all possibilities of integers in the integer table, then filter out the ones that don't match the criteria. (Not exactly sure of the order that Access performs these steps) But you can see with a large number of names (10,000) and a large range (1,000) you will have to match up 10,000,000 combinations then filter out the ones you want. I forget the name of this type of join(?).