Monthly Report - combine different records into one based on date

[Datalore]

Hi all, hope you are well.

Simple question:

I have a table:

Username
Status
StartDate
EndDate

I would like to query this table, so that when I enter a parameter
[SelectMonth], so that it returns all records BETWEEN month([StartDate]) AND
month([EndDate]).

Basically in my SELECT statement, the WHERE clause is:

WHERE
(

int([SelectMonth] BETWEEN month([StartDate]) AND month([EndDate])

)

Is this the correct format?

My report that is based on this is a little complex, but for illustration I
have simplified it here -

It has days marked out from 1 to 31 across the width of the page header, as
it is a monthly report.

A user will have a status between two dates, e.g. P/T for part-time, between
Aug 8th '05 and Aug 15th '05.

So for that user's record line on the report, if [SelectMonth] = 8, under 8,
9, 10, 11, 12,13, 14 and 15, "P/T" will exist.

Now take Aug 8th to Sep 8th for one user, and Jul 8th to Aug 8th for another
user: A different story!

If [SelectMonth] = 8, both of these records should appear, but ONLY fill in
P/T under the dates applicable - the first user having P/T under days 8, 9,
10 ... 31, and the second user having P/T under days 1, 2, 3 .. 8.

I have made the report work properly when the dates are inside the month.
But the only way I can make it work when the dates go outside the month is
using the BETWEEN...AND statement mentioned above.

Is this correct?

[

For the more advanced:

The status is in fact a number, which has a description code in a tbl_Status
table.

A user can have multiple records within the same month and are
date-exclusive of each other, no overlap occurs between dates for individual
users.

I am using a running sum of the status (number) for each textbox in the user
detail section of the report, and totaling each textbox in a user group
footer. Remember that multiple records can exist for the same user within the
same month, and the status number may be different for different date ranges.

Also, in detail sections, each textbox - i.e. each day - checks to see if it
falls in between the date range for each record of this month and eveluates
the status number, else it evaluates to 0.

This is handy, as say (in dd-mm-yy format):

Ted, 6, 01-08-05, 08-08-05
Ted, 7, 14-08-05 - 30-08-05

For [SelectMonth] = 8:

The first record would have 6 under days 1 - 8, and 0's until day 31.
The second record would have 7 under days 14 - 30, and 0's for every other
day of the month.

Doing a Running Sum into the Group Footer, then I have 6 for days 1 - 8 and
7 for days 14 - 31.

Then make the Properties for the user detail section invisible.

So instead of two records displaying for the user, I only have one! (of
course, I have to also pull the user name into the Group Footer as well!)

Then for each textbox in the group footer, in the Control Source I perform a
DLOOKUP on tbl_Status to get the status description code to display instead
of the status number.

]

Any other suggections welcome.

If anyone would like it, I can e-mail them with a copy of the database as it
stands - (e-mail address removed).

 

[oozyscab via AccessMonster.com]

I think that I would modify the WHERE clause to evaluate the complete date
rather than just the month. How will your current SELECT statement work if
the start date is in Dec. 2004 and the end date is in Feb. 2005? Try
evaluating everything as a date...

WHERE CDate(SelectMonth) BETWEEN CDate(StartDate) AND CDate(EndDate)

Cheers,
Eric

I would like to query this table, so that when I enter a parameter
[SelectMonth], so that it returns all records BETWEEN month([StartDate]) AND
month([EndDate]).

Basically in my SELECT statement, the WHERE clause is:

WHERE
(

int([SelectMonth] BETWEEN month([StartDate]) AND month([EndDate])

)

Is this the correct format?