473,837 Members | 1,634 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Insert Rows Based on Serial Range

5 New Member
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
10 2462
cactusdata
214 Recognized Expert New Member
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
32,584 Recognized Expert Moderator MVP
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
AmateurHour
5 New Member
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
214 Recognized Expert New Member
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
32,584 Recognized Expert Moderator MVP
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
AmateurHour
5 New Member
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
32,584 Recognized Expert Moderator MVP
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
AmateurHour
5 New Member
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
AmateurHour
5 New Member
Nevermind. Needed 0 digit in the number table. Thanks again.
Jun 5 '20 #10

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

Similar topics

11
7173
by: grumfish | last post by:
I'm trying to add a row to a MySQL table using insert. Here is the code: connection = MySQLdb.connect(host="localhost", user="root", passwd="pw", db="japanese") cursor = connection.cursor() cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s, %s)", ("a", "b", "c") ) connection.close() After running, a SELECT * on the table shows no new rows added. Adding
1
469
by: smile | last post by:
Hi All I want to insert rows from a table in a server into another table in another server using INSERT SELECT command. For example : INSERT INTO Server1.database1.dbo.Tab1 SELECT * FROM Server2.database2.dbo.Tab1 WHERE Col1 = 1
2
35228
by: Mansi | last post by:
I'm trying to automate excel from c#. One of the things I need to do is to copy/paste/insert rows in excel via c# code. I tried to do the following: 1) Select a row in excel (a12 to k12) 2) Insert a row. 3) copy contents of a12 to k12. 4) Paste these contents to new row. The "Record Macro" function returns the following code:
4
8610
by: RG | last post by:
Using VB.NET, How do I insert rows from a SQL Server table into an Access table with the same structure (and also the reverse, from Access to SQL)? I’m new to this, so here’s what I’ve tried so far (unsuccessfully): 1. Fill Dataset ‘S’ from a SQL Data Adapter (many rows). I see it in a DataGrid. 2. Fill Dataset ‘A’ from an Access OLE DB Data Adapter (just a few rows). In a second DataGrid. 3. Merge dataset ‘S’...
7
8397
by: rcamarda | last post by:
I wish to build a table based on values from another table. I need to populate a table between two dates from another table. Using the START_DT and END_DT, create records between those dates. I need a new column that is the days between the date and the MID_DT The data I wish to end with would look something like this: PERIOD DATE DAY_NO 200602 2005-07-06 -89 200602 2005-07-07 -88 200602 2005-07-08 -87
7
2830
by: tasmontique | last post by:
I have an access table that outputs to excel using a query . However what I am trying to do is under the arrival date column specify a criteria based on the Datepart function that only displays output based on specific day of week. I want query to check the stored date in the arrival column and based on that date that is examined by the date part function only return rows based on day of week .Here is my sql. SELECT...
6
7610
by: kareemaffan | last post by:
Hello Everyone I want to insert values into MS Access database through VB.NET in Visual Studio 2005 . I have used the following code which is not working. Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DSA.mdb") con.Open() Dim cmd As New OleDb.OleDbCommand("insert into...
2
3292
by: gnewsgroup | last post by:
I am new to the asp.net Table web control. I am using it to customize my presentation of data. I cannot implement the idea with DataGrid or GridView. Basically, I would like to have something like what is shown in the following PNG image. http://farm3.static.flickr.com/2183/1805431357_1facb1ed9b_o.png I know how to dynamically add rows to the end of the table, but how do
4
5474
by: ravir81 | last post by:
Hi, I am currently working on excel validation using Perl. I am new to Excel validation but not for Perl. I have a question regarding one of the validation. Could anyone please tell me how to get the number of duplicate rows based on a particular cell value of each these duplicate rows. I mean all the cell values of a row will not be duplicated but a individual columns cell value will be duplicated and I need to create a separate excel with...
3
5030
by: Vinda | last post by:
Hi Bytes, Using a previous question as a base Access 2000 Inserting multiple rows based on a date range. I also wanted to insert multiple rows into a table according to a date range supplied by a user (eg txtRDateStart & txtRDateEnd). The script was envisaged to be able to also gather a time and text description that would be repeated within each row. For example: The user would enter... Start Date: 13/03/2010 End Date: 17/03/2010...
0
10562
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10617
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10263
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9391
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7803
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5666
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4469
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4036
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3122
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.