Expand Date

[darreno]

I have a table with the following fields and data:

ID, DateFrom, DateTo, Qty
5, 1/1/08, 6/1/08, 3000

Can I convert the above to this:

ID, ShipDate, Qty
5, 1/1/08, 3000
5, 2/1/08, 3000
5, 3/1/08, 3000
5, 4/1/08, 3000
5, 5/1/08, 3000
5, 6/1/08, 3000

Thanks!

 

[pbaldy]

Sure; the tools you can use include

variables for the start and end dates
a Do loop comparing the current start date value to the end date

inside the loop:
insert a record in the target table (either executing SQL or using the AddNew method of a recordset)
increment the start date variable using the DateAdd function

 

[darreno]

Thanks Paul. I am lost as to where to begin. Is there a sample database that I can download?

 

[pbaldy]

Not that I'm aware of. If you post a sample db, I or someone else can try and code it.

 

[darreno]

Attached is a sample db. Thank you!

 

[pbaldy]

There is no table with the fields

ID, ShipDate, Qty

so where is the "converted" data to go?

Can I assume since you posted the question elsewhere, you do not expect a successful result here?

 

[darreno]

I thought the conversion is done thru a query... Attached is another db with the tbl_ship.

As for the other posting, no offense intended Paul. Just a bit anxious to see the results. I have gained tremendous knowledge and help from this forum.

Thanks.

 

[pbaldy]

I had to take the index off the ID field, but try this (I'm assuming you want all the records in the source table added):

  Dim strSQL             As String
  Dim db                 As DAO.Database
  Dim rsSource           As DAO.Recordset
  Dim rsTarget           As DAO.Recordset
  Dim dteStartDate       As Date
  Dim dteEndDate         As Date

  Set db = CurrentDb()

  strSQL = "SELECT * FROM tbl_sales;"
  Set rsSource = db.OpenRecordset(strSQL, dbOpenSnapshot)
  Set rsTarget = db.OpenRecordset("tbl_Ship", , dbAppendOnly)

  Do While Not rsSource.EOF
    dteStartDate = rsSource!start_date
    dteEndDate = rsSource!end_date
    Do Until dteStartDate > dteEndDate
      rsTarget.AddNew
      rsTarget!id = rsSource!id
      rsTarget!ship_date = dteStartDate
      rsTarget!qty = rsSource!qty
      rsTarget.Update
      dteStartDate = DateAdd("m", 1, dteStartDate)
    Loop

    rsSource.MoveNext
  Loop

  Set rsSource = Nothing
  Set rsTarget = Nothing
  Set db = Nothing

 

[gemma-the-husky]

the fact that you are having to manipulate the data to achieve what you want begs the questions -

1 why is the data structured as it is with start and finished dates - where is that entry coming from?
2. why do you need the expanded daily record?

so that, if you DO need the daily record, why not create those records immediately, and not bother with the start/finish records.

 

[darreno]

The entry is from an order form that specifies deliveries from start date to end date. I need a query/report with details of all outstanding orders sorted by month and thought that by expanding the delivery dates by month is the way to do it. Wrong approach?

Paul - thanks. Will try that out.

 

[gemma-the-husky]

if (effectively) the order form stores multiple deliveries, then it begs the question as to how you are representing the data at present

I presume you are storing the total order quantities one way, and the delivery schedule another - but can you clarify this

the point is you shouldnt NEED to store the delivery dates in the order header at all

you should have something like an order table, and an order items table

the order items table should store the items to be delivered ON the order, and the DATES required. then you can calculate the start and finish dates from the order schedule, not (the way you are looking to do) deconstruct the delivery schedule FROM the header.

 

[darreno]

I am so confused. If I understood correctly, instead of having 2 fields of StartDate and EndDate on the form, I should have 12 fields for each month?

 

[gemma-the-husky]

No

it comes back to looking at what data you are handling

if this entry really means that you are shipping 3000 of a given item to your customer on
a range of dates

ie if UK
1/ Jan/08
2/ Jan/08
3/ Jan/08
4/ Jan/08
5/ Jan/08
6/ Jan/08

but if US
Jan 1 2008
Feb 1 2008
Mar 1 2008
etc

then you are not storing the data correctly

The normalised way to store it would be

Order Table: OrderNo, CustomerNo

DeliverySchedule Table: OrderNo (referring to the order table), PlannedDate, PlannedQuantity

and not to store the date range in the order record at all. Note that because you are not storing any product IDs this implies you are only shipping 1 product.