start and end dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a table in which i have three columns- start dates,end dates and
volume..the table has got about
80 000 rows ...
i want to distribute the volume between two dates such as the volume gets
distributed according to the no of days in that month.

lets say the start date is 22/05/2000 and end date is 20/06/2000 and the
volume is 121 , i need to distribute 121 in such a way that we find out the
volume for 22 days in may and then for 20 days in june...


i also want to create an additional column called month which should have
the month names and in the next column it should have the volumes??
can you please guide me??
 
You can calculate the daily amount, by creating a query that uses this
table. Type an expression like this into a fresh column in the Field row:
DailyAmount: [Volume] / DateDiff("d", [start date], [end date])

Now, you need to generate a record for each day in the period, to be able to
perform the grouping you require. The dates have to come from somethere, so
you will need a table of dates.

1. Create a new table with just one Date/Time type field named TheDate. Set
this field as the primary key. Save the table with the name tblDate.

2. Enter a record for every date you need to consider. At the end of this
post is a function that will populate the table for you. For example, to
populate it with every date from 2000 to 2020, type this in the Immediate
Window:
? MakeDates(#1/1/2000#, #12/31/2020)

3. Create a query that uses both your original table and tblDate.
Add TheDate from tblDate to the grid.
In the Criteria row under this field, enter:
Between [start date] And [end date]
This gives you a record for each date in the date range.
(Note: there must be no line joining the 2 tables to each other in the upper
pane of query design.)

4. Type the calculated field for DailyAmount, as shown above.

You now have a record for each date, with the daily amount. So, you can
create a report and group by month, or create another query that groups the
values in this one, or query by quarter, or year, or any other way you
choose.

Here is the function to save you typing all the dates in by hand:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
 
Just a correction to Allen's typo (he missed a temininating #).

It should be:

MakeDates(#1/1/2000#, #12/31/2020#)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Allen Browne said:
You can calculate the daily amount, by creating a query that uses this
table. Type an expression like this into a fresh column in the Field row:
DailyAmount: [Volume] / DateDiff("d", [start date], [end date])

Now, you need to generate a record for each day in the period, to be able
to perform the grouping you require. The dates have to come from
somethere, so you will need a table of dates.

1. Create a new table with just one Date/Time type field named TheDate.
Set this field as the primary key. Save the table with the name tblDate.

2. Enter a record for every date you need to consider. At the end of this
post is a function that will populate the table for you. For example, to
populate it with every date from 2000 to 2020, type this in the Immediate
Window:
? MakeDates(#1/1/2000#, #12/31/2020)

3. Create a query that uses both your original table and tblDate.
Add TheDate from tblDate to the grid.
In the Criteria row under this field, enter:
Between [start date] And [end date]
This gives you a record for each date in the date range.
(Note: there must be no line joining the 2 tables to each other in the
upper pane of query design.)

4. Type the calculated field for DailyAmount, as shown above.

You now have a record for each date, with the daily amount. So, you can
create a report and group by month, or create another query that groups
the values in this one, or query by quarter, or year, or any other way you
choose.

Here is the function to save you typing all the dates in by hand:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

mita said:
i have a table in which i have three columns- start dates,end dates and
volume..the table has got about
80 000 rows ...
i want to distribute the volume between two dates such as the volume gets
distributed according to the no of days in that month.

lets say the start date is 22/05/2000 and end date is 20/06/2000 and the
volume is 121 , i need to distribute 121 in such a way that we find out
the
volume for 22 days in may and then for 20 days in june...


i also want to create an additional column called month which should have
the month names and in the next column it should have the volumes??
can you please guide me??
Click to expand...
Click to expand...
 
Allen said:
Create a new table with just one Date/Time type field
Enter a record for every date you need to consider
Click to expand...

He's one I did just this morning and its only ... ahem ... three lines
of SQL:

Sub CreatCalendar()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Calendar ( dt DATETIME NOT" & _
" NULL CONSTRAINT pk__Calendar PRIMARY KEY);"
.Execute _
"INSERT INTO Calendar (dt) VALUES" & _
" (#1900-01-01 00:00:00#);"

Dim sql
sql = _
"INSERT INTO Calendar (dt) SELECT CDATE(Units.nbr" & _
" + Tens.nbr + Hundreds.nbr + Thousands.nbr" & _
" + TenThousands.nbr) AS dt FROM (SELECT" & _
" nbr FROM (SELECT 0 AS nbr FROM Calendar" & _
" UNION ALL SELECT 1 FROM Calendar UNION" & _
" ALL SELECT 2 FROM Calendar UNION ALL SELECT" & _
" 3 FROM Calendar UNION ALL SELECT 4 FROM" & _
" Calendar UNION ALL SELECT 5 FROM Calendar" & _
" UNION ALL SELECT 6 FROM Calendar UNION" & _
" ALL SELECT 7 FROM Calendar UNION ALL SELECT" & _
" 8 FROM Calendar UNION ALL SELECT 9 FROM" & _
" Calendar) AS Digits) AS Units, (SELECT" & _
" nbr * 10 AS nbr FROM (SELECT 0 AS nbr" & _
" FROM Calendar UNION ALL SELECT 1 FROM Calendar" & _
" UNION ALL SELECT 2 FROM Calendar UNION" & _
" ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
" 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
" Calendar UNION ALL SELECT 6 FROM Calendar" & _
" UNION ALL SELECT 7 FROM Calendar UNION" & _
" ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
" 9 FROM Calendar) AS Digits) AS Tens," & _
" (SELECT nbr * 100 AS nbr FROM (SELECT" & _
" 0 AS nbr FROM Calendar UNION ALL SELECT"
sql = sql & _
" 1 FROM Calendar UNION ALL SELECT 2 FROM" & _
" Calendar UNION ALL SELECT 3 FROM Calendar" & _
" UNION ALL SELECT 4 FROM Calendar UNION" & _
" ALL SELECT 5 FROM Calendar UNION ALL SELECT" & _
" 6 FROM Calendar UNION ALL SELECT 7 FROM" & _
" Calendar UNION ALL SELECT 8 FROM Calendar" & _
" UNION ALL SELECT 9 FROM Calendar) AS Digits" & _
") AS Hundreds, (SELECT nbr * 1000 AS nbr" & _
" FROM (SELECT 0 AS nbr FROM Calendar UNION" & _
" ALL SELECT 1 FROM Calendar UNION ALL SELECT" & _
" 2 FROM Calendar UNION ALL SELECT 3 FROM" & _
" Calendar UNION ALL SELECT 4 FROM Calendar" & _
" UNION ALL SELECT 5 FROM Calendar UNION" & _
" ALL SELECT 6 FROM Calendar UNION ALL SELECT" & _
" 7 FROM Calendar UNION ALL SELECT 8 FROM" & _
" Calendar UNION ALL SELECT 9 FROM Calendar" & _
") AS Digits) AS Thousands, (SELECT nbr" & _
" * 10000 AS nbr FROM (SELECT 0 AS nbr FROM" & _
" Calendar UNION ALL SELECT 1 FROM Calendar" & _
" UNION ALL SELECT 2 FROM Calendar UNION" & _
" ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
" 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
" Calendar UNION ALL SELECT 6 FROM Calendar" & _
" UNION ALL SELECT 7 FROM Calendar UNION"
sql = sql & _
" ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
" 9 FROM Calendar) AS Digits) AS TenThousands" & _
" WHERE Units.nbr + Tens.nbr + Hundreds.nbr" & _
" + Thousands.nbr + TenThousands.nbr BETWEEN" & _
" 3 AND 73050;"

.Execute sql

Dim rs
Set rs = .Execute( _
"SELECT MIN(dt) AS min_date," & _
" MAX(dt) AS max_date" & _
" FROM Calendar;")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
hi jamie
thanks for your help.. i appreciate ur efforts.. well i am not much of a
programmer...so the code which u ve given me...will it solve my problem for
even a million rows in the table???
 
hi doughlas ...i was able t ogenerate the daily amount but when i try to do
the grouping, i get the divide by zero error
 
Back
Top