[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

---

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.

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!

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

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!