Related consultants and assignments based on dates

[gddrew]

In my database I have:

* A table of consultants
* A table of contracts
* A table of assignments (shows which consultants are assigned to which contracts, along with the start and end dates of those assignments)
* A table in which consultants book their time to projects

The consultant assignments change over time. What I need to do is create a query that shows a) the amount of time a consultant booked to a project(s), and b) the contract to which he/she was associated at the time. For example, if a consultant was associated to Contract A in February, Contract B in March, and Contract C in April, I want to be able to accurately reflect that in my query. I'm not sure how to go about doing this in my query.

I'm sure many here have solved similar problems, so any help you can give will be appreciated.

 

[gddrew]

Or perhaps this is something that cannot be accomplished through a query but through a module?

 

[pbaldy]

Perhaps a sample db would help people to understand/solve your problem.

 

[rainman89]

part a can be done with queries.... part b might take some finagling but probably can be done

 

[The_Doc_Man]

The whole thing can be done from a couple of simple tables standing between consultant and project. You have that table in terms of the assignments.

Suppose we assume that there is a consultant ID (call it ConsID) and a project ID (call it ProjID). In your assignment table, you would have (perhaps) something like

tblAssignments
AssignCode, PK, autonumber would work. (trust me on this one)...
ConsID - FK to consultant table
ProjID - FK to project table
AssignStart - date assignment started
AssignEnd - date assignment ended


tblCharges
ConsID - FK to consultant table
ProjID - FK to project table
AuthID - FK to assignment entry that allows this charge
ChargeDate - date of charge
ChargeHours - hours and fractions of this day's charge.

OK, to make this work

You cannot make a charge until you can find an assignment entry where the charge date is between the assignment start and assignment end for that consultant and project. Look for BETWEEN ... AND ... in Access Help. If you find such an assignment entry, use the Assignment ID as the authorization code. In this idea, you cannot charge unless you have an assignment ID that holds the consultant & project ID and the date range for that assignment.

OK, to find who was assigned in a given month, you can use the first day of the month and the last day of the month plus the between...and operator to find all assignments that overlapped the month, then group the results by project and sort by consultant. (Or group by consultant and sort by contract. Same difference.) You have your (b) report right there.

As to the (a) report, just group by contractor, sort by date, sort by project, and you have the details of the charges right there. It would be up to you to load up appropriate totals in the report footers.

 

[gddrew]

I have to confess that I still don't get it when it comes to making the actual query work. I am attaching a schematic of the relevant tables, along with the sample db.

One thing to be aware of in the DB: The consultant charges (tblCharges) gets its data from an external system; the Fiscal Week is actually a text field (23 Dec 2006).

Hopefully this will help.

 

[gddrew]

DocMan, I posted a sample DB and an ERD. Could you help with the query?