Extracting multiple records from single record

[Flopper]

Greetings,

As the title suggest i'm attempting to extract multiple records from a single record. I have a set of data with a start and end date, and for each inclusive day within that period i would like a copy of the original record, with the exception of a single date file containing the inclusive date.

For example:

Original data:

[b]Ref	DateA	DateB	Code	Location[/b]
12345	01-Jan	06-Jan	Sev1	Liverpool
23456	16-Feb	19-Feb	Sev2	Manchester
34567	12-Mar	12-Mar	Sev2	London
45678	13-Feb	14-Feb	Sev3	Birmingham
56789	27-Apr	29-Apr	Sev2	Liverpool

Would become:
[b]Ref	Date	Code	Location[/b]
12345	01-Jan	Sev1	Liverpool
12345	02-Jan	Sev1	Liverpool
12345	03-Jan	Sev1	Liverpool
12345	04-Jan	Sev1	Liverpool
12345	05-Jan	Sev1	Liverpool
12345	06-Jan	Sev1	Liverpool
23456	16-Feb	Sev2	Manchester
23456	17-Feb	Sev2	Manchester
23456	18-Feb	Sev2	Manchester
23456	19-Feb	Sev2	Manchester
34567	12-Mar	Sev2	London
45678	13-Feb	Sev3	Birmingham
45678	14-Feb	Sev3	Birmingham
56789	27-Apr	Sev2	Liverpool
56789	28-Apr	Sev2	Liverpool
56789	29-Apr	Sev2	Liverpool

I'm using Access 2000, and would prefer to have the answer in a single SQL statement if possible.

Many thanks.

Flopper

 

[Golom]

First ... build a table called "Integers" with one field called "num" (Long). Load the table with integers from zero to the maximum number of days that you will need like this

CREATE TABLE Integers
(num Long)

INSERT INTO Integers (num) VALUES(0)
INSERT INTO Integers (num) VALUES(1)
INSERT INTO Integers (num) VALUES(2)
  :
INSERT INTO Integers (num) VALUES(10)

then

Select Ref, DateAdd("d", I.Num, DateA) As [NewDate], Code, Location

From MyTable, Integers As I

Where DateAdd("d", I.Num, DateA) <= DateB

Order By 1, 2

 

[PHV]

Create an integers table, say tblIntegers, with a single integer field, say Num, you may use as primary key.
Then populate it with consecutive numbers starting from 0 to the max expected number of days between DateA and DateB
Tip: use the autofill feature of excel.

Now your query:
SELECT Ref, DateA+Num AS [Date], Code, Location
FROM yourTable, tblIntegers
WHERE Num Between 0 And (DateB-DateA)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Flopper]

Thanks Golom,

Once upon a time, a long time ago, i did briefly code in SQL... but not to any great level. I was under the impression that using cursors(?) would be a more efficient method of achieving this... plus i'm reluctant to create an additional table based upon the possible number of days between the two dates. Although unlikely, the difference between these dates could in theory be as much as 2+ years.

Cheers

 

[Golom]

OK. There's a bit of a trick that you can use that may slow things down but it does relieve you of creating the table with thousands of entries.

First, load your Integers table with the digits 0 - 9 then

Select Ref, DateAdd("d", H.Num * 100 + T.Num * 10 + U.Num, DateA) As [NewDate], Code, Location

From MyTable, Integers As H, Integers As T, Integers As U

Where DateAdd("d", H.Num * 100 + T.Num * 10 + U.Num, DateA) <= DateB

Order By 1, 2

This just creates the integers from 0 to 999 without having to load all those values into the Integers table. You can of course just load it with code as in

Dim rs As Recordset
Set rs = db.Openrecordset("Integers")
For n = 0 to 1000
    rs.AddNew
    rs![num] = n
    rs.Update
Next