CODEWITHSUNDEEP
sqlms-access
TJrhithron
TJrhithron

Reputation: 23

Increment and record creation based on field value

Access 2003

Ultimately I need to create a report for printing labels "Sample ID, Jar x of x".

My table has Sample ID and Number of Jars. Is it possible to create a query that gives me 3 fields: Sample ID, Jar Number, Number of Jars - where the Jar Number creates records in increments based on Number of Jars?

  Query:  Sample ID - Jar Number - Number of Jars
          Sample 1    1            4
          Sample 1    2            4
          Sample 1    3            4
          Sample 1    4            4
          Sample 2    1            2
          Sample 2    2            2

The table information is: 

  Table:  Sample ID - Number of Jars
          Sample 1    4
          Sample 2    2

I want to avoid creating a table record for each jar.

Upvotes: 2

Views: 391

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

You can take advantage of a numbers table:

SELECT samples.[Sample ID], 
       samples.[Number of Jars], 
       Numbers.Number AS [Jar Number] INTO NewSamples
FROM samples, Numbers
WHERE Numbers.Number<=[Number of Jars]

Samples is the name of your current table.

The numbers table contains integers between 1 and what ever number is the highest number you can get for jars, say 100.

If you already have a zero-based numbers table, you could use:

WHERE Numbers.Number Between 1 And [Number of Jars]

Upvotes: 2

Related Questions