July 23, 2003 at 10:50 pm
I have 4 tables:
empType, Emp, AccidentInfo, EmployeeAccident.
EmpType describes 3 types of employees that are prepopulated:
The attributes are:
TypeID,
Type.
TypeID Type
1 Deputy
2 Supervisor
3 Staffmember
The Emp table has many attributes but the ones that are important here are:
emPID,pk int identity
TypeID (foreign key from empType table)
fname,
lname,
orgcode
AccidentInfo has also has several attributes that involve accidents.
The important ones here are:
accidentId int pk,identity
dateofAccident datetime,
timeOfaccident datetime,
caller (person who called in when an accident occurs)
operator (person involved in an accident)
accidentNotes,
dateTimeLog (date and time accident was logged in)
Again there are more fields but I can field those in once I get my query right.
Finally, there is employeeAccident
The attributes are:
EmployAccidentID pk int identity,
empID (foreign key from emp table),
TrackingNumber (foreign key from accidentInfo table)
Completed bit (determines whether each employee (from emp and emptype tables have processed paperwork after an accident occurs, 0 for yes, 1 or No)
My problem is I need to create a summary report that can tie all of these together in one sql statement or multiple statement.
I am having problem join all of the tables to display:
TrackingNumber, DateofAccident,TimeOfAccident,Caller, Operator,Deputy,Supervisor,Staffmember, AccidentNotes, DateAndTimeLog,Completed.
If I use this query:
SELECT * FROM AccidentInfo, I get all records except employee type and their fnames and lnames (emptype and emp table) ,
Completed.
If I use this query:
SELECT theEmp.fname+' '+theEmp.lname as fullName, tblEmployeeType.Type,
tblEmployAccident.Completed
FROM tblEmployeeType, theEmp, tblEmployAccident
WHERE tblEmployeeType.TypeID = theEmp.TypeID AND
theEmp.empID = tblEmployAccident.empID
I get the employee types (supervisor, deputy, staffmember) and their names (fname, lname), completed.
I need to somehow combine these tables into one query to be able to display all their records at one.
I am asking for your help/expertise, please.
I hope this is explained good enough.
Thanks in advance
July 23, 2003 at 11:31 pm
Can you post an example of the output required? (heading + 1 or 2 lines)
Cheers,
- Mark
July 24, 2003 at 6:25 am
Will this do it?
<code>
SELECT employeeAccident.TrackingNumber, AccidentInfo.dateOfAccident, AccidentInfo.timeOfAccident, AccidentInfo.caller, AccidentInfo.operator, EmpTYpe.Type, AccidentInfo.accidentNotes, AccidentInfo.dateTimeLog, employeeAccident.Completed
FROM Emp
INNER JOIN EmpTYpe
ON Emp.TypeID = EmpTYpe.TypeID
INNER JOIN employeeAccident
ON Emp.EmpID = employeeAccident.EmpID
INNER JOIN AccidentInfo
ON employeeAccident.TrackingNumber = AccidentInfo.accidentID
</code>
I just wasn't sure if your TrackingNumber in the employeeAccident table was related
to the accidentID in the AccidentInfo table.
I hope this helps. Cheers
Joseph
July 24, 2003 at 9:57 am
that's exactly what I am looking form.
Thank you fromnaija.
Sorry to both of you for my late response.
I was out in the field.
By the way, s the fname, lname values editable giving that you have to do a join between emp and emptype tables (typeID) and giving that you can't do updates on multiple tables.
Thanks again for all the help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply