dcsimg
Results 1 to 5 of 5

Thread: [RESOLVED] SQL Query Split Record?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2008
    Posts
    101

    Resolved [RESOLVED] SQL Query Split Record?

    Firsty Apologies if this is in the incorrect section.

    I have a small database created in MS Access and Im trying to write a query that transfers data from one table to another after researching the topic I have come up with the following SQL Statement:

    Code:
    INSERT INTO tblTest (Code, Product, DaysCover, MaxCover, InStock)
    SELECT Code, Product, DaysCover, MaxCover, InStock FROM tblProducts
    WHERE IncludeInPlanning=True AND InStock=True;
    This statement works perfectly and fills the tblTest with the correct details passed from tblProducts.

    My question is I need to have a record for each day for the paticular Product, I Would like to store a Date into tblTest, The date would be required for everyday including week ends (7 Days). The Date does not come from the Products table but can be specified by the user (Start Date and End Date)

    so.. each Product in tblTest will have as many records as the user specifies associated with it (one for each day of the week)

    Is there any simple SQL query that do this easily?

    Thanks in advance

    edit: As mentioned before the user can specify the start and end date so im planning on writing a front end for the database possibly in vb.net, I will probably use two of the DateTimePicker to allow the user to select the start/end dates. after thinking about this a bit more would a loop be needed so that for each date specified, insert into the test table??

    I have found some sample code from this site that can grab the value of two specified dates and store within a listbox here is the code:

    Code:
    ListBox1.Items.Clear()
    
            Dim StartDate As DateTime = DateTimePicker1.Value.Date
            Dim EndDate As DateTime = DateTimePicker2.Value.Date
    
    
            ListBox1.Items.Add(StartDate)
    
            Dim currentDate As Date = StartDate
            Do
    
                currentDate = currentDate.AddDays(1)
                ListBox1.Items.Add(currentDate)
            Loop Until currentDate >= EndDate
    Last edited by enex; Feb 15th, 2010 at 11:16 AM. Reason: updated with more info

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,324

    Re: SQL Query Split Record?

    Do you mean that you want the four fields you've already got, but add an extra field for the date (and therefore have each row [number of days] times)?

    If so, using a loop would be the simplest way.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2008
    Posts
    101

    Re: SQL Query Split Record?

    Hi Si thanks for your reply,

    Yes thats exactly right I would like the fields: Code, Product, DaysCover, MaxCover, InStock and then allow the user to specify a startingdate and enddate then this will insert records in the table based on the number of dates specified so for example 1/1/2010 2/1/2010 3/1/2010 will create three rows each with the same details for Code, Product, DaysCover, MaxCover, InStock but having a unique date.

    being new to all this, I would be grateful if you could point me in the right direction you said a loop? but how would I go about doing this.

    again cheers for your help!

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,324

    Re: SQL Query Split Record?

    The loop you showed in post #1 looks about right, just replace the ListBox1.Items.Add line with one that executes your Insert statement using the value of the currentDate variable, eg:
    Code:
    INSERT INTO tblTest (Code, Product, DaysCover, MaxCover, InStock, DateField)
    SELECT Code, Product, DaysCover, MaxCover, InStock , <value here> FROM tblProducts
    WHERE IncludeInPlanning=True AND InStock=True;
    I would strongly recommend using a Parameter to do put the value in, as shown under "Saving changes directly to one or more records in the database." in Retrieving and Saving Data in Databases

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Oct 2008
    Posts
    101

    Re: SQL Query Split Record?

    Hi Si,

    I have tried what you have suggested but it throws out an SQL error. Is this because im inserting from a select statement??, how can I pass values from elsewhere within the same statement? as mentioned the Date does not come from the table but is user specified.

    here is my code so far:

    Code:
    'Allow the user to select start/end date from DateTimePickers
    Dim StartDate As DateTime = dtpStartDate.Value.Date
    Dim EndDate As DateTime = dtpEndDate.Value.Date
    Dim i As Integer
    Dim NumberOfDays As Integer
    
    'Calculate How many times the record must be added in the for loop
    NumberOfDays = EndDate.DayOfYear - StartDate.DayOfYear
    
    If MsgBox("You are about to insert into the table are you sure you wish to do this?", MsgBoxStyle.YesNo, "Insert INTO") = MsgBoxResult.Yes Then
    
    For i = 1 To NumberOfDays
    
    Dim SQL As String
    
    SQL = ("INSERT INTO tblTest (Code, Product, DaysCover, MaxDaysCover, InStock) SELECT Code, Product, DaysCover, MaxDaysCover, InStock FROM tblProducts WHERE IncludeInPlanning=True AND InStock=True")
    
    Next
    
    End If

    edit: actually after trying again its working.. cheers si!
    Last edited by enex; Feb 16th, 2010 at 08:47 AM. Reason: working code

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width