Date Range query design

[KarenO]

I am working on an expenses database for a small family business. For each
expense I have a start date and end date. I have the database then
“normalize†the expense. So for instance, it takes a bill for a thirty day
period and divides the total expense by thirty to give an Expense per day for
the bill.

I want to be able to query a range of dates (like a week or month period) and
have the query determine how many of the days (if any) of an expense fall
within that range and multiply that number of days by the “normalizedâ€
Expense per day value to give the contribution of that bill to the queried
date range.

I am only minimally proficient in Access and am not a programmer so any help
will be greatly appreciated!

 

[vanderghast]

The interval [start, end] does not intercept interval [START, END] if

end < START OR start > END

There is some overlap, in part or in full, otherwise:


end >= START AND start <= END


So, if start, end is the overall interval for the event, and if START, END
is the interval which concerns you, if the second criteria evaluates to
true, the overlapping period will be:

from max{ start, START} up to min{ end, END }


In JET SQL, that may looks like (***pseudo code***, since JET SQL does not
differentiate END and end, START and start, not to mention that these names
are poor fields / parameters names, since they are likely to collide
eventually with key words)


SELECT
iif(start>START, start, START) AS fromThis,
iif(end < END, end, END} AS upTo,
upTo-fromThis + 1 As numberOfDays,
Price/ ( 1 + END - START) AS pricePerDay,
numberOfDays * pricePerDay AS totalApplicable
FROM somewhere
WHERE
end >= START AND start <= END




Vanderghast, Access MVP

 

[KenSheridan via AccessMonster.com]

Another approach to this is to use an auxiliary Calendar table, which is
simply a table of all dates over a period of time in a column, calDate in the
example below. A simple way to create such a table is to serially fill down
a column with dates in Excel and then import it into Access as a table,
making the calDate column its primary key, or it can be created with code,
which I'll give you at the end of this post.

You can then create a query which joins the Expenses table to the Calendar
table where the calDate values in calendar are within a range entered as
parameters at runtime, so the query would be like this:

PARAMETERS
[Start of Range:] DATETIME,
[End of Range:] DATETIME;
SELECT ExpenseID, StartDate, EndDate,
[Start of Range:] AS StartOfRange,
[End of Range:] AS EndOfRange,
COUNT(*) AS DaysInRange
FROM Expenses,Calendar
WHERE calDate >= StartDate
AND CalDate<= EndDate
AND CalDate BETWEEN [Start of Range:]
AND [End of Range:]
GROUP BY ExpenseID, StartDate, Enddate;

Note that there is no JOIN clause in this query, the join is done by means of
criteria in the WHERE clause. The way it works is that having joined the
Expenses table to the Calendar table on:

WHERE calDate >= StartDate
AND CalDate<= EndDate

which would return all dates between the start and end date in each row of
expenses, it then further restricts the rows returned with:

AND CalDate BETWEEN [Start of Range:]
AND [End of Range:]

Which eliminates any rows outside the range, so we are left with only those
within the range. By grouping the query on each expense and counting the
rows returned we get the number of days per expense within the range. You
can then multiply this by the daily expense, by expanding the query along
these lines:

PARAMETERS
[Start of Range:] DATETIME,
[End of Range:] DATETIME;
SELECT ExpenseID, StartDate, EndDate, TotalExpense,
[Start of Range:] AS StartOfRange,
[End of Range:] AS EndOfRange,
(TotalExpense/((EndDate - StartDate) + 1)) AS DailyExpense,
COUNT(*) AS DaysInRange,
COUNT(*) * (TotalExpense/((EndDate - StartDate) + 1)) AS RangeExpense
FROM Expenses,Calendar
WHERE calDate >= StartDate
AND CalDate<= EndDate
AND CalDate BETWEEN [Start of Range:]
AND [End of Range:]
GROUP BY ExpenseID, StartDate, Enddate, TotalExpense;

So the first thing to do is create and fill the Calendar table with arrange
of dates, up to 5 or 10 years ahead for instance

While it is possible to build the above query from scratch in design view its
probably simpler to paste in the above in SQL view, and then change the table
and column names to the real ones. You can add other columns form the
Expenses table, but remember these must also be included in the GROUP BY
clause if added to the SELECT clause. In fact if you switch to design view
before adding any extra columns you can then add them in design view.

Another way to create and fill a calendar table is with code. The following
function does this:

Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

Paste the function into any standard module. Make sure you have a reference
to the DAO object library by selecting references for the Tools menu on the
VBA menu bar, and se if you have the Microsoft DAO #.# Object Library checked
(where #.# will be a version number). If not scroll down the list and check
the one with the highest version number.

Its not necessary to save the module, but if you want to keep the function
for future use then save the module under a name other than the name of the
function, e.g. mdlCalendarStuff. The in the debug window (aka immediate
window – press Ctrl+G to open it) enter the following to create a calendar
from the start of 2009 to the end of 2019 say:

MakeCalendar_DAO "Calendar",#2009-01-01#,#2019-12-31#,0

Ken Sheridan
Stafford, England

 

[KarenO via AccessMonster.com]

First, thank you SO much for your help and your rapid response. I got it
converted to my field names
BeginDate = expense range start, TerminateDate = expense range end, IStartD
ate= queried interval start, and IEndDate = queried interval end and
ENorm1Day =PricePerDay So I finished with the following in the SQL

SELECT
iif(BeginDate>IStartDate, BeginDate, IStartDate) AS fromThis,
iif(TerminateDate < IEndDate, TerminateDate, IEndDate) AS upTo,
upTo-fromThis + 1 As numberOfDays,
PaymentAmount/ ( 1 + TerminateDate - BeginDate) AS ENorm1Day,
numberOfDays * ENorm1Day AS totalApplicable
FROM Expenses
WHERE
TerminateDate >= IStartDate AND BeginDate <= IEndDate

The only problem I am having is with the numberOfDays calculation.(upTo-
fromThis +1 As numberOfDays) I am getting a #Error . I cannot figure it out.
upTo and fromThis display correctly in short date format (I even tried
setting the property for each to short date format in the design screen.) All
of my dates for the Database are in short date format.
I can not figure out why this simple calculation is not working. It of course
is throwing off the totalApplicable value also. ENorm1Day calculates
correctly.

What am I missing?

Thanks,
Karen

The interval [start, end] does not intercept interval [START, END] if

end < START OR start > END

There is some overlap, in part or in full, otherwise:

end >= START AND start <= END

So, if start, end is the overall interval for the event, and if START, END
is the interval which concerns you, if the second criteria evaluates to
true, the overlapping period will be:

from max{ start, START} up to min{ end, END }

In JET SQL, that may looks like (***pseudo code***, since JET SQL does not
differentiate END and end, START and start, not to mention that these names
are poor fields / parameters names, since they are likely to collide
eventually with key words)

SELECT
iif(start>START, start, START) AS fromThis,
iif(end < END, end, END} AS upTo,
upTo-fromThis + 1 As numberOfDays,
Price/ ( 1 + END - START) AS pricePerDay,
numberOfDays * pricePerDay AS totalApplicable
FROM somewhere
WHERE
end >= START AND start <= END

Vanderghast, Access MVP

I am working on an expenses database for a small family business. For each
expense I have a start date and end date. I have the database then

[quoted text clipped - 13 lines]

help
will be greatly appreciated!

 

[John Spencer]

Often you have to redo the entire calculation and cannot use the reference
(alias) to the earlier calculations.

Try something like the following - hopefully I got all the parentheses in the
right places.

SELECT
iif(BeginDate>IStartDate, BeginDate, IStartDate) AS fromThis
, iif(TerminateDate < IEndDate, TerminateDate, IEndDate) AS upTo
, iif(TerminateDate < IEndDate, TerminateDate, IEndDate)
-iif(BeginDate>IStartDate, BeginDate, IStartDate)+ 1 As numberOfDays
, PaymentAmount/ ( 1 + TerminateDate - BeginDate) AS ENorm1Day
, (iif(TerminateDate < IEndDate, TerminateDate, IEndDate)
-iif(BeginDate>IStartDate, BeginDate, IStartDate)+ 1)
* PaymentAmount / ( 1 + TerminateDate - BeginDate) AS TotalApplicable
FROM Expenses
WHERE TerminateDate >= IStartDate AND BeginDate <=IEndDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

First, thank you SO much for your help and your rapid response. I got it
converted to my field names
BeginDate = expense range start, TerminateDate = expense range end, IStartD
ate= queried interval start, and IEndDate = queried interval end and
ENorm1Day =PricePerDay So I finished with the following in the SQL

SELECT
iif(BeginDate>IStartDate, BeginDate, IStartDate) AS fromThis,
iif(TerminateDate < IEndDate, TerminateDate, IEndDate) AS upTo,
upTo-fromThis + 1 As numberOfDays,
PaymentAmount/ ( 1 + TerminateDate - BeginDate) AS ENorm1Day,
numberOfDays * ENorm1Day AS totalApplicable
FROM Expenses
WHERE
TerminateDate >= IStartDate AND BeginDate <= IEndDate

The only problem I am having is with the numberOfDays calculation.(upTo-
fromThis +1 As numberOfDays) I am getting a #Error . I cannot figure it out.
upTo and fromThis display correctly in short date format (I even tried
setting the property for each to short date format in the design screen.) All
of my dates for the Database are in short date format.
I can not figure out why this simple calculation is not working. It of course
is throwing off the totalApplicable value also. ENorm1Day calculates
correctly.

What am I missing?

Thanks,
Karen

The interval [start, end] does not intercept interval [START, END] if

end < START OR start > END

There is some overlap, in part or in full, otherwise:

end >= START AND start <= END

So, if start, end is the overall interval for the event, and if START, END
is the interval which concerns you, if the second criteria evaluates to
true, the overlapping period will be:

from max{ start, START} up to min{ end, END }

In JET SQL, that may looks like (***pseudo code***, since JET SQL does not
differentiate END and end, START and start, not to mention that these names
are poor fields / parameters names, since they are likely to collide
eventually with key words)

SELECT
iif(start>START, start, START) AS fromThis,
iif(end < END, end, END} AS upTo,
upTo-fromThis + 1 As numberOfDays,
Price/ ( 1 + END - START) AS pricePerDay,
numberOfDays * pricePerDay AS totalApplicable
FROM somewhere
WHERE
end >= START AND start <= END

Vanderghast, Access MVP

I am working on an expenses database for a small family business. For each
expense I have a start date and end date. I have the database then

[quoted text clipped - 13 lines]

help
will be greatly appreciated!

 

[KarenO via AccessMonster.com]

Thanks for the input. Not using references to earlier calculations is a
really helpful to know. I will be more aware in the future.

Your parentheses were all in the right places - no syntax errors. But when I
plugged it in, I got the same #Error for numberOfDays. The fromThis and upTo
both display correctly in short date format, but I still get the #Error on
numberOFDays and on the Total Applicable which should now be unrelated to the
numberOfDays. The strange thing is on an earlier Query when I was trying to
work this out in my mind, I have the whole normalization working with these
same formulas but without the queried interval. I have a numberOfDays divided
into the PaymentAmount to give a Normalized by day value that I call
ENorm1Day.

Would it possibly work to reference this normalization query to pull these
values to the Interval query instead of going directly from the Expenses
table (so breaking it into two consecutive queries instead of one) or could
this cause me problems down the line? (ideally I guess I would feel more
confident referencing straight from the Expenses Table).

Thanks again

Karen

Often you have to redo the entire calculation and cannot use the reference
(alias) to the earlier calculations.

Try something like the following - hopefully I got all the parentheses in the
right places.

SELECT
iif(BeginDate>IStartDate, BeginDate, IStartDate) AS fromThis
, iif(TerminateDate < IEndDate, TerminateDate, IEndDate) AS upTo
, iif(TerminateDate < IEndDate, TerminateDate, IEndDate)
-iif(BeginDate>IStartDate, BeginDate, IStartDate)+ 1 As numberOfDays
, PaymentAmount/ ( 1 + TerminateDate - BeginDate) AS ENorm1Day
, (iif(TerminateDate < IEndDate, TerminateDate, IEndDate)
-iif(BeginDate>IStartDate, BeginDate, IStartDate)+ 1)
* PaymentAmount / ( 1 + TerminateDate - BeginDate) AS TotalApplicable
FROM Expenses
WHERE TerminateDate >= IStartDate AND BeginDate <=IEndDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

First, thank you SO much for your help and your rapid response. I got it
converted to my field names

[quoted text clipped - 62 lines]

 

[KarenO via AccessMonster.com]

Realized that Query based on the query is not going to work becasue the
calculated number of days is not for the correct range. It is for the expense
range not the queried range so the problem still stands. The only thing of
value I could pull from that Query is the ENorm1Day which is working in the
interval query. This dealing with tow different ranges is driving me crazy,
Sorry.

Karen

Thanks for the input. Not using references to earlier calculations is a
really helpful to know. I will be more aware in the future.

Your parentheses were all in the right places - no syntax errors. But when I
plugged it in, I got the same #Error for numberOfDays. The fromThis and upTo
both display correctly in short date format, but I still get the #Error on
numberOFDays and on the Total Applicable which should now be unrelated to the
numberOfDays. The strange thing is on an earlier Query when I was trying to
work this out in my mind, I have the whole normalization working with these
same formulas but without the queried interval. I have a numberOfDays divided
into the PaymentAmount to give a Normalized by day value that I call
ENorm1Day.

Would it possibly work to reference this normalization query to pull these
values to the Interval query instead of going directly from the Expenses
table (so breaking it into two consecutive queries instead of one) or could
this cause me problems down the line? (ideally I guess I would feel more
confident referencing straight from the Expenses Table).


Thanks again

Karen

Often you have to redo the entire calculation and cannot use the reference
(alias) to the earlier calculations.

[quoted text clipped - 24 lines]

 

[KarenO via AccessMonster.com]

Realized that Query based on the query is not going to work becasue the
calculated number of days is not for the correct range. It is for the expense
range not the queried range so the problem still stands. The only thing of
value I could pull from that Query is the ENorm1Day which is working in the
interval query. This dealing with tow different ranges is driving me crazy,
Sorry.

Karen

Thanks for the input. Not using references to earlier calculations is a
really helpful to know. I will be more aware in the future.

Your parentheses were all in the right places - no syntax errors. But when I
plugged it in, I got the same #Error for numberOfDays. The fromThis and upTo
both display correctly in short date format, but I still get the #Error on
numberOFDays and on the Total Applicable which should now be unrelated to the
numberOfDays. The strange thing is on an earlier Query when I was trying to
work this out in my mind, I have the whole normalization working with these
same formulas but without the queried interval. I have a numberOfDays divided
into the PaymentAmount to give a Normalized by day value that I call
ENorm1Day.

Would it possibly work to reference this normalization query to pull these
values to the Interval query instead of going directly from the Expenses
table (so breaking it into two consecutive queries instead of one) or could
this cause me problems down the line? (ideally I guess I would feel more
confident referencing straight from the Expenses Table).


Thanks again

Karen

Often you have to redo the entire calculation and cannot use the reference
(alias) to the earlier calculations.

[quoted text clipped - 24 lines]

 

[KarenO via AccessMonster.com]

Realized that Query based on the query is not going to work becasue the
calculated number of days is not for the correct range. It is for the expense
range not the queried range so the problem still stands. The only thing of
value I could pull from that Query is the ENorm1Day which is working in the
interval query. This dealing with tow different ranges is driving me crazy,

Sorry.
Karen

Thanks for the input. Not using references to earlier calculations is a
really helpful to know. I will be more aware in the future.

Your parentheses were all in the right places - no syntax errors. But when I
plugged it in, I got the same #Error for numberOfDays. The fromThis and upTo
both display correctly in short date format, but I still get the #Error on
numberOFDays and on the Total Applicable which should now be unrelated to the
numberOfDays. The strange thing is on an earlier Query when I was trying to
work this out in my mind, I have the whole normalization working with these
same formulas but without the queried interval. I have a numberOfDays divided
into the PaymentAmount to give a Normalized by day value that I call
ENorm1Day.

Would it possibly work to reference this normalization query to pull these
values to the Interval query instead of going directly from the Expenses
table (so breaking it into two consecutive queries instead of one) or could
this cause me problems down the line? (ideally I guess I would feel more
confident referencing straight from the Expenses Table).


Thanks again

Karen

Often you have to redo the entire calculation and cannot use the reference
(alias) to the earlier calculations.

[quoted text clipped - 24 lines]

 

[KenSheridan via AccessMonster.com]

Have you tried using an auxiliary calendar table in the way I described?
Such tables are commonly used when dealing with data where a period of time
is recorded by start and end dates in a table. The calendar table supplies
the intervening date values to make up the sequence, and it then becomes very
easy to compute aggregated values over all or part of the sequence. As well
as situations like yours calendar tables can be used for appointment systems,
hotel room bookings, leave assignment etc, etc.

As you'll have seen from my first post a query which joins the expenses and
calendar table can easily return the aggregated values for the part of the
full range for an expense which intersects with the queried range.

Ken Sheridan
Stafford, England

Realized that Query based on the query is not going to work becasue the
calculated number of days is not for the correct range. It is for the expense
range not the queried range so the problem still stands. The only thing of
value I could pull from that Query is the ENorm1Day which is working in the
interval query. This dealing with tow different ranges is driving me crazy,

Sorry.
Karen

Thanks for the input. Not using references to earlier calculations is a
really helpful to know. I will be more aware in the future.

[quoted text clipped - 14 lines]

this cause me problems down the line? (ideally I guess I would feel more
confident referencing straight from the Expenses Table).

Thanks again

[quoted text clipped - 5 lines]

 

[KarenO via AccessMonster.com]

As a matter of fact I was just working on it! Thank you for your response!
I am trying several paths concurrently and was just working on your
suggestion. I am not a programmer so I am a little slow and I like to
understand what I am doing instead of just plugging in the code so that slows
me down too, but it helps me learn. Like I said, I am working on your
suggestion and appreciate your time so much. I will get back to you as I
have questions... and I will have questions.

Thanks again,

Karen

Have you tried using an auxiliary calendar table in the way I described?
Such tables are commonly used when dealing with data where a period of time
is recorded by start and end dates in a table. The calendar table supplies
the intervening date values to make up the sequence, and it then becomes very
easy to compute aggregated values over all or part of the sequence. As well
as situations like yours calendar tables can be used for appointment systems,
hotel room bookings, leave assignment etc, etc.

As you'll have seen from my first post a query which joins the expenses and
calendar table can easily return the aggregated values for the part of the
full range for an expense which intersects with the queried range.

Ken Sheridan
Stafford, England

Realized that Query based on the query is not going to work becasue the
calculated number of days is not for the correct range. It is for the expense

[quoted text clipped - 16 lines]

 

[KarenO via AccessMonster.com]

I just got all the Tables and Code put in place and it works great. I wasn't
sure initially if I had the skill to implement it but it was really easy once
I got into it and it works like a dream! Thank you so much for your time and
I appreciate the way you explained the process.
Thanks again,

Karen

Have you tried using an auxiliary calendar table in the way I described?
Such tables are commonly used when dealing with data where a period of time
is recorded by start and end dates in a table. The calendar table supplies
the intervening date values to make up the sequence, and it then becomes very
easy to compute aggregated values over all or part of the sequence. As well
as situations like yours calendar tables can be used for appointment systems,
hotel room bookings, leave assignment etc, etc.

As you'll have seen from my first post a query which joins the expenses and
calendar table can easily return the aggregated values for the part of the
full range for an expense which intersects with the queried range.

Ken Sheridan
Stafford, England

Realized that Query based on the query is not going to work becasue the
calculated number of days is not for the correct range. It is for the expense

[quoted text clipped - 16 lines]