Counting Days

G

Guest

Hi,

I have query that feeds a report in Access 2003. The query is based off of
2 tables (tblMain and tblDates).

Basically, there is one record in tblMain that could have one or multiple
dates stored in tblDates. The record in tblDates includes a start date and
stop date. I would like to count the number of calendar days between the
start and stop date. That is the easy part.

What I can't determine is trying to count days on overlapping records. For
example:

RecordA from tblMain:
DateRecord1 from tblDates: StartDate: 06/01/2006 and StopDate: 06/15/2006
DateRecord2 from tblDates: StartDate: 06/05/2006 and StopDate: 06/10/2006
DateRecord3 from tblDates: StartDate: 06/20/2006 and StopDate: 06/30/2006

The result should not calculate DateRecord2 because it is within
DateRecord1. It should only calculate the number of days in DateRecord1 and
the number of days in DateRecord3. The query sums the 2 together based on
the record in tblMain.

I'm sure I'm not explaining myself very well, but if anyone can read minds,
I certainly appreciate it.

Thank you in advance.
 
G

Guest

Somebody here may come up with a more elegant solution, but it seems to me
you need to use the DMax() and DMin() functions to grab (and save to
temporary variables) the earliest and latest dates in your tblDates that are
associated with WHATEVER-IS-YOUR-LINKING-FIELD in tblMain. Having done that,
you can do the obvious math using the two variables, and use the result in
whatever way you need to. A caution: in my
not-minor-but-still-far-short-of-major experience, the domain aggregate
functions like DMax and DMin can really slow things down if they have to be
run repeatedly against a slew of records. Since you didn't specify how big
your tables are, that may or may not be a concern. I've used them against
tables of a few thousand records and not found them too onerous.
 
G

Guest

Thanks for the response, Larry.

I thought about the Max and Min functions, but I am concerned when there is
a gap between dates. In the example I showed, the Min would be 6/1, and the
Max would be 6/30. However, there is a gap between 6/15 and 6/20 and I do
not want those days counted.

As far as the number of records, it would only run against 75-125
records...Not many.
 
G

Guest

Okay, so then use DSum(), with "[EndDate]-[StartDate]" as the first argument,
and of course still using the linking field as your third argument. Only way
I can think of that you might come to grief there is if you hit a null date.
 
G

Guest

Ooops -- should have gone back and re-read your initial statement of the
problem -- DSum() ain't gonna do it, as I'm sure you've concluded already,
but now you've got me hooked. While I'm having my first cuppa Joe I'm gonna
play around with this. Stay tuned....
 
G

Guest

Okay, how about this approach:

First, get the maximum possible number of days using the DMin/DMax
functions, and store that number in a variable.

Second, Dim an array of integers with the number of elements matching your
max-possible-days, and set them all to zeroes. BUT, instead of
zero-to-whatever, set your array subscripts to the date values of your min
and max dates, e.g., 38279 to 38304.

Then go through your tblDates and, using the start and end date for each
record as the bookends of a for-next loop, set the corresponding array value
to 1.

Finally, sum the entire contents of the array.

Seems to me that, referring back to your example, this would (1) set up an
array of 30 zeroes, subscripted as 38869 thru 38898; (2) change 26 of the
zeroes to 1's; and (3) produce a total of 26. Make sense? I didn't actually
try to write the code, but will take a stab at it if you like.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top