That is not only excellent that you found a solution, but that you shared it with us is even more so. I KNEW there had to be a simpler solution than defaulting to code; I was just too tired to think of any, given that it was after 2:00 AM. Of course, it was a more reasonable hour during my second post, but I elected to wait upon your research.
What you have there is a solution based on a Cartesian Product (albeit a filtered one because of the query criteria) which is what you'll always get if you involve two or more tables without joining them. Simple, but elegant. The only caution I'd raise is that the solution posted at the other site uses at least one reserved word (Number) and so have you - but you should not. Bookmark this site:
Microsoft Access tips: Problem names and reserved words in Access
Here is what I tweaked when I implemented the other site's solution and saw the result:
Code:
SELECT samples.[Sample ID], Numbers.Nums AS [Jar Number] INTO NewSamples
FROM samples, Numbers WHERE (((Numbers.Nums)<=[Number of Jars]))
ORDER BY samples.[Sample ID], Numbers.Nums;
You might notice I eliminated the number of jars field as it didn't seem important to repeat it in the new table. I also implemented a sort order. The only thing I don't like about the solution is that it creates a table and will do so each time. If you continually overwrite objects, it can cause db corruption, or at least significant db bloat. I would either run this query once, then open the new table in design view and either create a composite index using SampleID and JarNumber or a composite primary key, OR simply design a table from scratch and do the same. Some say never to create a composite key, but I've never had any issues doing so and don't know what the objection is. After doing so, I'd change the query sql to
Code:
INSERT INTO NewSamples ( [Sample ID], [Jar Number] )
SELECT samples.[Sample ID], Numbers.Nums AS [Jar Number]
FROM samples, Numbers
WHERE (((Numbers.Nums)<=[Number of Jars]))
ORDER BY samples.[Sample ID], Numbers.Nums;
so that it appends new records to the same table. If you have created the composite index or PK, you will get a warning about not being able to append all the duplicates (because the query doesn't discriminate between what is new and what is not), you'd want to suppress that message unless you were only going to be the user of the db. This could be done in either of 2 ways:
1) the button click event that drives this has to handle the warnings one of two ways (so a wee bit of code would be required). Warnings are either turned off then back on again at the end of the query execution, OR the db.Execute method is used to run the query
2) the sql could be modified to attempt to prevent duplicate records from being appended. This seems to work:
Code:
INSERT INTO NewSamples ( [Sample ID], [Jar Number] )
SELECT DISTINCT samples.[Sample ID], Numbers.Nums AS [Jar Number]
FROM Numbers, NewSamples INNER JOIN samples ON NewSamples.[Sample ID] = samples.[Sample ID]
WHERE (((Numbers.Nums)<=[Number of Jars] And [Numbers].[Nums]<>[NewSamples].[Jar Number]))
ORDER BY samples.[Sample ID], Numbers.Nums;
Notice that this solution requires the target table to be part of the query, as well as I'm using the DISTINCT predicate. Go to the query in design view and on the property sheet, look for Unique Values and Unique Records and read your help file on these attributes. Without this setting, the query will return some duplicate rows for each sample ID.
P.S.: I almost never have spaces in my object names, but I ended up with some here by way of copying someone else's sql statement.