By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,068 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,068 IT Pros & Developers. It's quick & easy.

Insert Rows Based on Serial Range

P: 5
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?
May 30 '20 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   [SerialNumbers].[S/N Start]+[tblDigit].[Digit] AS [SerialNumber]
  2. FROM     [SerialNumbers]
  3.        , [tblDigit]
  4. WHERE    ([SerialNumbers].[S/N Start]+[tblDigit].[Digit]<=[SerialNumbers].[S/N End])
  5. ORDER BY [SerialNumbers].[S/N Start]
  6.        , [tblDigit].[Digit]

Share this Question
Share on Google+
10 Replies


cactusdata
Expert 100+
P: 102
You can use a Cartesian (multiplying) query:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     Val([Sequence]) + Factor AS SerialNumber
  3. FROM 
  4.     SerialNumbers, 
  5.     (SELECT DISTINCT Abs([id] Mod 10) AS Factor
  6.     FROM MSysObjects
  7.     WHERE Abs([id] Mod 10) Between 0 And 9) 
  8. ORDER BY 
  9.     Val([Sequence]) + Factor
May 31 '20 #2

NeoPa
Expert Mod 15k+
P: 31,761
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   Val([SerialNumbers].[S/N]) + [tblDigit].[Digit] AS [SerialNumber]
  2. FROM     [SerialNumbers]
  3.        , [tblDigit]
  4. ORDER BY [SerialNumbers].[S/N]
  5.        , [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.
Jun 4 '20 #3

P: 5
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.
Jun 4 '20 #4

cactusdata
Expert 100+
P: 102
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     SerialNumbers.Sequence, 
  3.     Val([Sequence]) + (Factor + Factor10) AS SerialNumber
  4. FROM 
  5.     SerialNumbers, 
  6.     (SELECT DISTINCT Abs([id] Mod 10) AS Factor
  7.     FROM MSysObjects
  8.     WHERE Abs([id] Mod 10) Between 0 And 9) As T1,
  9.     (SELECT DISTINCT Abs([id] Mod 10) * 10 AS Factor10
  10.     FROM MSysObjects
  11.     WHERE Abs([id] Mod 10) Between 0 And 9) As T10
  12. WHERE
  13.     Val([Sequence]) + (Factor + Factor10) <= Val(Mid([Sequence], 1 + InStr([Sequence], "-"))) 
  14. ORDER BY 
  15.     Val([Sequence]) + (Factor + Factor10)
Jun 4 '20 #5

NeoPa
Expert Mod 15k+
P: 31,761
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   Val([SerialNumbers].[S/N])+[tblDigit].[Digit] AS [SerialNumber]
  2. FROM     [SerialNumbers]
  3.        , [tblDigit]
  4. WHERE    (Val([SerialNumbers].[S/N])+[tblDigit].[Digit]
  5.         <=Val(Mid([SerialNumbers].[S/N],InStr([SerialNumbers].[S/N],'-')+1))
  6. ORDER BY [SerialNumbers].[S/N]
  7.        , [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.
Jun 4 '20 #6

P: 5
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.
Jun 5 '20 #7

NeoPa
Expert Mod 15k+
P: 31,761
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   [SerialNumbers].[S/N Start]+[tblDigit].[Digit] AS [SerialNumber]
  2. FROM     [SerialNumbers]
  3.        , [tblDigit]
  4. WHERE    ([SerialNumbers].[S/N Start]+[tblDigit].[Digit]<=[SerialNumbers].[S/N End])
  5. ORDER BY [SerialNumbers].[S/N Start]
  6.        , [tblDigit].[Digit]
Jun 5 '20 #8

P: 5
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?
Jun 5 '20 #9

P: 5
Nevermind. Needed 0 digit in the number table. Thanks again.
Jun 5 '20 #10

NeoPa
Expert Mod 15k+
P: 31,761
Ah great. I'm very happy as I learned a really neat technique from my fellow expert CactusData along the way.
Jun 5 '20 #11

Post your reply

Sign in to post your reply or Sign up for a free account.