Avg/Mean Time on Report

[Guest]

Hi All.
I've been reviewing former postings and have a static query that will report
the working days between two dates. Good start, but not enough.

How do I generate this dynamically (allow the user to select the two "date"
fields from a form) and report these days along with the avg onto a report.
I have a form that provides a selectable list of employees (names w/
associated checkbox) and want to add two date drop down boxes.

I want these boxes to reflect date fields in my table (i.e., DateHired,
DateSubmitted, DateReceived, DateBriefed, DateDebriefed, DateContacted, etc.)
allowing the users to select their start-ending dates and click report to
generate.
The differences (I am using Arvin Meyers function WorkingDays).

THANKS!

 

[Jeff Boyce]

If you are saying you want to give the user the ability to choose any two
date-related fields to calculate the working-day-difference between, you'll
probably need to dynamically create your SQL statement (that's all a query
is...).

By the way, having multiple "repeating fields" (DateThis, DateThat,
DateThese, ...) is how your data would be organized ... if you were using a
spreadsheet. You won't get the benefit of Access' relationally-oriented
features/functions if you feed it 'sheet data. A more normalized structure
would include something like:

trelXXXX
....
"AccountID" (since I don't know what these dates relate to)
ActionDate
ActionType (this is what would hold your "Hired", "Submitted", ...)

This is a "many" table in a one-to-many relationship (I am assuming an
"account" could have zero, one, or many of these "dates").

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I Jeff,

Thanks for responding. The database is an expanded Employee 'type' base
which includes date fields such as Date of Birth (DOB), Date of Hire (DOH),
Date of Termination (DOT).
This Employee table is linked to a secondary table (1-to-many) relationship
and this holds additional info. with dates which are specific to the vender
he/she is sub-contracted to (ie., DateSubmitted, Date Accepted,
DateStartProject, DateEndProject, PerfReveiwDate1, PerfReviewDate2, etc.).
Some of the dates of interest to the user may be in the Emp table, but I
understand that some may be in the secondary table (and I'd have to GroupBy
Name to prevent some duplicate reporting due to the multiple records in the
secondary table)

I didn't understand the treXXXX part of feedback, but can you point me to a
good SQL (I suspect query by form) source to get me started?

Upon printing the textual report, I'm hoping to find instruction on graphing
the results (Person w/ start-end dates and 1 with person and the avg days
between the given dates) so if you happen to know of a good ref. for that,
I'll read everything and try to learn from the exmpales.

Many thanks for taking the time...

 

[Jeff Boyce]

That was just an example of a table structure for the "many" side.

Regards

Jeff Boyce
Microsoft Office/Access MVP