Retrieve records from one table where dates are between records in another table

[GS500]

Hello all,

I have a survey database that I’ve been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I’m having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.

ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2’s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.

I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)

The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)

The Manager table has the ManagerID and ManagerName.

That’s the employee side of things; then I have the tables that store the audit results:

tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)

tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer


So my question is how can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
Thanks!

 

[Mihail]

Remove the ManagerID field from Employe table. Is not necessary.
Then upload the DB in Access 2003 format.
Keep some data in order to test, but replace the sensitive information with a non real but realistic set.

 

[GS500]

OK, hopefully I left enough data for you. In my original question I had renamed the some of the tables to try to explain their usage better. I think they'll be apparent looking at the data, but if not let me know (for example Employee = Adjuster).

I reworked the tblAdjusters by removing the ManagerID and made all the other employee related tables relate to the tblAdjusterHistory so I can track all changes in job descriptions of the adjusters.

I'm sure there are things wrong with my table structures, feel free to comment on any issues you see.

Thanks

 

[Mihail]

You lose me. I can't find the table Audit.
Anyway, take a look to the attached DB. Should give you an idea about "How to".

 

[GS500]

I'm sorry, in my first post I was trying to give a generic example to explain what I wanted to accomplish; I should have listed my actual table names clearly in the 2nd post.

tblAudit is actually tblCatResult, which has a record for each review completed (Field ResultID would probably be more accurately called ReviewID). Then tblResults stores the QstnID and Answer and ReviewDate for each question in each review in tblCatResult.

So what I need is to use tblResults.ReviewDate and tblAdjusterHistory.EffectiveDate to determine which manager an adjuster reported to when each review was completed.
I have uploaded an updated version of my example database in my 2nd post. Query2 shows the fields I need, but for each ResultID I need to limit the records from tblAdjusterHistory to the most recent EffectiveDate prior to the ReviewDate.

 

[CJ_London]

Try replacing the criteria for EffectiveDate with this

(SELECT Max([EffectiveDate]) FROM tblAdjusterHistory AS TMP WHERE EffectiveDate<=tblResults.ReviewDate)

Just copy and paste, including the brackets at each end

 

[GS500]

It's not returning ResultID 5 now.

 

[CJ_London]

Now it does!

(SELECT Max([EffectiveDate]) FROM tblAdjusterHistory AS TMP WHERE EffectiveDate<=tblResults.ReviewDate AND AdjID=tblAdjusterHistory.AdjID)

 

[GS500]

Yes it does, thanks!

 

[Mihail]

Hi CJ
Happy New Year ! (also for you, GS)

Have you a good SQL tutorial? I think that is the time to start to fully learn this.
Thank you !

 

[GS500]

I think so too. Do you have any you recommend? I could Google for some, but there are so many out there. At any rate, I definitely need to study up on subqueries. That is what CJ's solution is, correct?

Happy New Year to you both as well!

 

[jdraw]

Mihail: Great SQL book Simply SQL by Rudy (r937) in pdf format



Also w3schools tutorial

 

[Mihail]

Thank you, jdraw. And Happy New Year !

 

[jdraw]

How close is this

An Nou fericit

 

[Mihail]

How close is this

An Nou fericit

It is absolutely correct

We also say "La Multi Ani !".
In English is something like "I wish that you be alive for many many years from now".
This is also the expression we use when we celebrate the someone's Birth Day.

 

[ballysoft]

HELLO HOUSE
PLEASE WHO CAN HELP ME SOLVE THIS STUFF?
1. Design a possible schema of doctor's office. The doctor wants immediate access to patient's medical information. The clerk needs to be sure all insurance companies are billed and each patient is billed for reminder.
2. Consider an inventory database at a kitchen cabinet factory which contains parts information (part number, description, colour, size, number in stock, etc) and vendor information (name, address, purchase order, etc). Indicate for each user, an account payable clerk, which item that user should be able to access and change.
3. Which type of user would usually perform the following functions for an inventory system in a large company:
a. Create a monthly report of current inventory value.
b. Update the number in stock for specific items received in shipment.
c. Cancel the user account for an employee who just retired.
d. Change the structure of the inventory database to include more information on each item.
e. Reply to a phone request regarding the number of a particular item that are currently in stock.
4. Consider a database at a university which contains information about students (name, ID number, course schedule, salary, etc) . Indicate for each user which item should the user be able to access and which item should the user be able to access and change
a. Faculty member
b. Clerk in registrar office
c. Student
d. Payroll check

 

[CJ_London]

@Ballysoft - Regret to say I don't do homework - suggest you research 'database design' and 'normalisation' as a start

Here are some links to get you started

http://databaseanswers.org/data_models/
http://databases.about.com/od/specificproducts/a/normalization.htm
http://support.microsoft.com/kb/283878

@Mihail - And Happy New Year to you!