Insert Rows Based on Serial Range

[AmateurHour]

I have a data set that is aggregated by serial numbers and I would like to create a row in a table for each unique serial. Ex: current data shows serial range 2400-2409 and all other variables are the same. I want 10 rows, 2400, 2401, [...] 2409.

So I may currently have 5 rows:
S/N
2400-2409
2410-2419
2420-2429
2430-2439
2440-2449

I need a table with 50 records and each unique serial.

Best ideas?

[cactusdata]

You can use a Cartesian (multiplying) query:

SELECT 
    Val([Sequence]) + Factor AS SerialNumber
FROM 
    SerialNumbers, 
    (SELECT DISTINCT Abs([id] Mod 10) AS Factor
    FROM MSysObjects
    WHERE Abs([id] Mod 10) Between 0 And 9) 
ORDER BY 
    Val([Sequence]) + Factor

[NeoPa]

My suggestion would avoid using MSysObjects. I'm not familiar enough with the contents to know that I could guarantee each digit having at least one record to represent it. Maybe that's a reliable assumption but I can't say. It's a very clever solution of course (If you didn't realise then take it from me ;-) )

The alternative would be to create a separate table and populate it with ten simple records each containing a single digit value from 0 to 9. Assuming the table's named [tblDigit] & the field [Digit] the SQL would then simply be :

SELECT   Val([SerialNumbers].[S/N]) + [tblDigit].[Digit] AS [SerialNumber]
FROM     [SerialNumbers]
       , [tblDigit]
ORDER BY [SerialNumbers].[S/N]
       , [tblDigit].[Digit]

Obviously this assumes your question represents your data accurately and contains the range as a string as you've shown.

Notice that this is fundamentally just the SQL already posted by CactusData with a few minor changes.

[AmateurHour]

I think I oversimplified the question possibly.

The ranges vary, not as clean as represented above. For instance row 2 may be range 4506-4587. Row 3 may be 4588-4602

I think the Cartesian is the way to go, but I'm not exactly sure how to tailor the SQL to fit the varying ranges. I've created a table called tblNumber that has numbers 1-99999 to fit all of the possibilities.

[cactusdata]

You don't need a table. Just add a factor of 10 for a maximum of 100 items in a sequence, and limit that to the range of the sequence:

SELECT 
    SerialNumbers.Sequence, 
    Val([Sequence]) + (Factor + Factor10) AS SerialNumber
FROM 
    SerialNumbers, 
    (SELECT DISTINCT Abs([id] Mod 10) AS Factor
    FROM MSysObjects
    WHERE Abs([id] Mod 10) Between 0 And 9) As T1,
    (SELECT DISTINCT Abs([id] Mod 10) * 10 AS Factor10
    FROM MSysObjects
    WHERE Abs([id] Mod 10) Between 0 And 9) As T10
WHERE
    Val([Sequence]) + (Factor + Factor10) <= Val(Mid([Sequence], 1 + InStr([Sequence], "-"))) 
ORDER BY 
    Val([Sequence]) + (Factor + Factor10)

[NeoPa]

AmateurHour:
I think I oversimplified the question possibly.

I think it's fair to say the data included was poorly chosen to give a misleading understanding. Don't worry. That sort of thing will be easier to recognise with experience.

CactusData has shown, again, a very clever way of providing the data to support a cartesian product for ten times more values. While you may not want to include all of this complication in your query I strongly recommend you recognise the technique and how it can be used, by extension, to provide values from 0 to any power of ten. Each extra power is reached by the simple addition of another table in the FROM clause. You may even want to set up a stored QueryDef to encapsulate the complexity within and thus enable you to benefit from that any time you are required to use a record source of a list of number from 0 up to whatever.

I'll leave that up to you to decide what suits you best. It may be daunting for a beginner, but show that to a superior and you can expect them to be impressed.

However, you may find it easier to follow if I illustrate what can be done using the simpler table approach.

SELECT   Val([SerialNumbers].[S/N])+[tblDigit].[Digit] AS [SerialNumber]
FROM     [SerialNumbers]
       , [tblDigit]
WHERE    (Val([SerialNumbers].[S/N])+[tblDigit].[Digit]
        <=Val(Mid([SerialNumbers].[S/N],InStr([SerialNumbers].[S/N],'-')+1))
ORDER BY [SerialNumbers].[S/N]
       , [tblDigit].[Digit]

You'll see this is very much a repeat of much of what CactusData has already posted. Hopefully it's a little easier to understand as it's a more basic approach.

Before I finish let me just add that this is unnecessarily more complicated because the data for the start and end values is held in a text field. It would work more efficiently, and be easier to code, if it were normalised and each of the lower and upper values were stored in separate numeric fields.

[AmateurHour]

I appreciate all of the responses and explanations. This was my first post so bear with me on the detail I provide, I guess I figured I should keep it generic.

That being said, let me just provide the detail in more depth and maybe I can save time and/or trouble for anyone involved.

These are for barrels of whiskey provided in a table from a distiller. They are grouped in serials based on locations in a warehouse. Column I is "S/N Start" and column J is "S/N End", both numeric. All other columns are applicable to those serial ranges, which is why they are grouped together. However, for inventory management I would prefer to track barrels individually. I just prefer to not manually break them out monthly. To build the tables, queries and reports I've had to break some out already just to have data to build off of. I'm a proponent of automation when feasible, hence why I'm seeking advice.

If you need more information, let me know! Thanks again.

[NeoPa]

That's all very reasonable. It is a call how much to include. It's easy to overfill the cup and it's easy to underfill it. As I say, experience will guide you as you progress.

For now, I believe you've been given a full answer to your request. Please let us know if you don't feel that way.

I would add an update in the light of the fact that the data is held in two separate numeric fields.

SELECT   [SerialNumbers].[S/N Start]+[tblDigit].[Digit] AS [SerialNumber]
FROM     [SerialNumbers]
       , [tblDigit]
WHERE    ([SerialNumbers].[S/N Start]+[tblDigit].[Digit]<=[SerialNumbers].[S/N End])
ORDER BY [SerialNumbers].[S/N Start]
       , [tblDigit].[Digit]

[AmateurHour]

This worked well. I have one final piece and I'll be on my way. Is there a simple way to get the S/N Start to be included in the query results?

[AmateurHour]

Nevermind. Needed 0 digit in the number table. Thanks again.

[NeoPa]

Ah great. I'm very happy as I learned a really neat technique from my fellow expert CactusData along the way.