johannaellamay
Registered User.
- Local time
- Today, 16:37
- Joined
- Jul 19, 2014
- Messages
- 190
Hello, I'm a real newbee so please be forgiving. This will be a long post because I want to explain the details.
This is the situation that I'm stuck with:
I have the following tables
1. t_Employee. It consists of the following fields:
2. t_Login. It has the ff fields:
3. t_AuditTrail w/ the ff fields (this will used for historical data for Job title, Contract Start Date, Contract End Date, etc.):
I've already set up t_AuditTrail by putting several (& separate) After Update Data Macros.
Now, I have a form for t_Employee. It has a button that would open a report. This report contains the Job Title history of an employee.
The report is based on a query w/ the ff SQL:
So the report only shows historical data for Job Title. Which means that Job Title from t_AuditTrail is not related to Contract Start Date or Contract End Date.
Problem(s)/Question(s):I want my report to show the Job Title History and the corresponding contract start date and contract end date (not the date a record was edited). When an employee changes a job title, his/her contract dates change.However, when i start to make a report based on quesries q_AuditTrail_JobTitle and q_AuditTrail_ContractStartDate and q_AuditTrail_ContractEndDate, Access tells me that they are not connected so it cannot make a report. How do I go about this? How do I let user see the Job Title relative to its contract start and end dates?
Please help. I'm stuck.
I hope I've laid out the details well enough.
This is the situation that I'm stuck with:
I have the following tables
1. t_Employee. It consists of the following fields:
- EmployeeID
- Name
- Job Title
- Contract Start Date
- Contract End Date
2. t_Login. It has the ff fields:
- UserID
- UserName
- Password
3. t_AuditTrail w/ the ff fields (this will used for historical data for Job title, Contract Start Date, Contract End Date, etc.):
- AuditTrailID
- TableID (in this case t_Employee)
- FieldName (JobTitle)
- RecordID (EmployeeID)
- OldValue
- NewValue
- ChangeDate (date edited)
- ChangeBy (UserName)
I've already set up t_AuditTrail by putting several (& separate) After Update Data Macros.
Now, I have a form for t_Employee. It has a button that would open a report. This report contains the Job Title history of an employee.
The report is based on a query w/ the ff SQL:
Code:
SELECT t_AuditTrail.atTableID, t_AuditTrail.atFieldName, t_AuditTrail.atRecordID, t_AuditTrail.atOldValue, t_AuditTrail.atNewValue
FROM t_AuditTrail
WHERE (((t_AuditTrail.atTableID)="t_Employee") AND ((t_AuditTrail.atFieldName)="eJobTitleID"));So the report only shows historical data for Job Title. Which means that Job Title from t_AuditTrail is not related to Contract Start Date or Contract End Date.
Problem(s)/Question(s):I want my report to show the Job Title History and the corresponding contract start date and contract end date (not the date a record was edited). When an employee changes a job title, his/her contract dates change.However, when i start to make a report based on quesries q_AuditTrail_JobTitle and q_AuditTrail_ContractStartDate and q_AuditTrail_ContractEndDate, Access tells me that they are not connected so it cannot make a report. How do I go about this? How do I let user see the Job Title relative to its contract start and end dates?
Please help. I'm stuck.
I hope I've laid out the details well enough.
