Access/ SQL question

johntreacy

Hi Guys

I'm creating a small training database for work but having problems with one query and wondering if ye could help.

The problem is on the Training_record table being linked to the employee and training program tables.

I've tried every thing i can find online about the different types of join's but nothing seems to be working. If i link only one table the report that i need is giving incorrect data.

Below is also the SQL that access has created for this query.

Mods feel free to move if there is a better place for this.

Thanks to anyone who can help.

SELECT Employee.ID, Employee.Surname, Employee.Firstname, Employee.Employee_No, roles.Role, [Training Programs].Code, [Training Programs].Title, [Training Programs].Rev, Trianing_record.Completed, Trianing_record.[Program Date]
FROM (((Trianing_record RIGHT JOIN [Training Programs] ON Trianing_record.Program = [Training Programs].ID) INNER JOIN roles ON [Training Programs].ID = roles.training_required.Value) INNER JOIN Employee ON (Trianing_record.Empid.Value = Employee.ID) AND (roles.ID = Employee.roleid.Value)) INNER JOIN Manager ON Employee.Manager = Manager.ID;

samhail

What exactly do you need ?
It to return every training record, on every training program, for every employee ?

johntreacy

samhail wrote: »

What exactly do you need ?
It to return every training record, on every training program, for every employee ?

Sorry yes. With employee's roles there are required training so an employee is linked to a role which is then linked to many "Training programs".
Then when training is completed the user enters the info into the "Training_record" so what i need is a query that shows the required training + what has been completed. I have two different queries at the minute that so either the required training or the completed training but need both showing on one report.

Hope this explains a little better.

bruce wayne

Do you know that you have spelt training wrong in most of that statement?

johntreacy

bruce wayne wrote: »

Do you know that you have spelt training wrong in most of that statement?

Sorry my bad, I've being looking at this thing way too long. I'll correct it now.

samhail

errrr... spelling doesnt matter all that much. it only needs to be the same as the name of the table.
(can confuse people alright)

samhail

If it were me i would add in a few more linking tables, just to make it handier to link up your multible rows etc. (i think i also moved your Required from the Roles table, to my roles_TrainingPrograms table)

This is as far as im gonna look into it tonight... nice 11 hour work days for the entire week
Hopefully might give you a few ideas. ill try and take another look at it tomorrow night.
(if u want me to upload that access database for you to mess around with let me know... in the next 10min and ill get it up for your tonight)




This is only my opinion... im not a DBA of any sorts.

johntreacy

Samhill sent you a pm with link to the file.
Thanks again for your help