473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Insert years based on first and last year

48
Hi guys,

Hope you can help out. I would like to create a query or piece of code to create records based on a start and end value. So I got this data:

Name Value Start End
DF334 ABC 2004 2007
DF335 DEF 1998 2001
DF336 DEF 2013 2016

And I want to make it like this:
DF334 ABC 2004
DF334 ABC 2005
DF334 ABC 2006
DF334 ABC 2007
DF335 DEF 1998
DF335 DEF 1999
DF335 DEF 2000
DF335 DEF 2001
etcetera

How could I resolve this?

Hope to hear from you! Thanks
Feb 16 '16 #1

✓ answered by mbizup

You'd need recordset code with a nested loop - the 'inner' loop would run from the start to end years; the 'Outer' loop would run through each record in your table...

Something like this:
Expand|Select|Wrap|Line Numbers
  1. Do Until rs.EOF
  2.   for intYear = rs!Start to rs!End
  3.     strSQL = "INSERT INTO yourTable (Name, Value, Year) Values ('" & rs!Name & "', '" & rs!Value & "', " & intYear & ")"
  4.      currentdb.execute strSQL, dbfailonerror
  5.   Next
  6. rs.MoveNext
  7. Loop
That code is neither perfect syntactically nor complete, but should give you a general idea to start coding a solution with. Give it your best try, and post back with any issues.

2 901
mbizup
80 64KB
You'd need recordset code with a nested loop - the 'inner' loop would run from the start to end years; the 'Outer' loop would run through each record in your table...

Something like this:
Expand|Select|Wrap|Line Numbers
  1. Do Until rs.EOF
  2.   for intYear = rs!Start to rs!End
  3.     strSQL = "INSERT INTO yourTable (Name, Value, Year) Values ('" & rs!Name & "', '" & rs!Value & "', " & intYear & ")"
  4.      currentdb.execute strSQL, dbfailonerror
  5.   Next
  6. rs.MoveNext
  7. Loop
That code is neither perfect syntactically nor complete, but should give you a general idea to start coding a solution with. Give it your best try, and post back with any issues.
Feb 16 '16 #2
sanniep
48
Hi mbizup, and thanks for your response. Code was almost done, only had to add the recordset and change the fieldnames. Turned out to be simple thanks to your start. Thanks again!
Feb 18 '16 #3

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

Similar topics

2
by: John | last post by:
If anyone can help me out with a good way to do this in javascript I would greatly appreciate it. I need to compute three dates in javascript - all of which relate to a given date, say todays...
1
by: Dan Leeder | last post by:
stroccur = DCount("", "empnotes", " = " & Chr(34) & Me.Rpt_Card_Type & Chr(34) & " And = " & Me.SSN & " And #" & & "# > " & DateSerial(Year(Me.datetime) - 1, Month(Me.datetime),...
0
by: bdtmike | last post by:
I'm using the GridView control and have the Mode of the Pager set to "NumericFirstLast". However, when there are several pages to display, the pager shows only page numbers--and no First/Last...
1
by: Frank Bishop | last post by:
I have been spoiled by some report writing tools that have intrinsic functions like Last Year Month-to-date. I'm looking for a way to emulate this in SQL Server now with my fields that are...
5
by: rjfjohnson | last post by:
Hey, Today is Thursday 16-Feb-06. The same thursday last year is 17-Feb-05. Because I am comparing daily sales between years, I need to know the date of the same weekdayname as last year, so...
2
by: Tony Ciconte | last post by:
Does anyone know of or have any VBA code or similar logic that can help distinguish similar first/last name combinations? For example, we would like to prompt the user of a possible match when any...
6
by: phforum | last post by:
If user input the date is 2006-07-01. How to convert it to last year 2005-07-01? Thanks
2
by: Janick Bernet | last post by:
I just stumbled upon this olap function and wondered why the "NULLS FIRST/LAST" clause is not possible in normal order by but only using row_number() over(). Sure, I can do a SELECT *,...
0
by: pratapmysql | last post by:
Hello All, I am trying to get same day (name) of week of last year in this case date of day is not constraint eg. if december 2007-12-19 day is wednesday ,then last year 2006 wednesday for same is...
5
by: edwardcga | last post by:
I am trying to write a query to extract product sales data. Columns are product code, qty, current month sales, ytd sales for current year and comparative number for last year. How do I write the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.