Create a query to find people without particular records

[Amanda Burlingham]

Hi, please accept my apologies if this is posted somewhere else.

I have designed an HR database and i want to create a report that for example tell me who does not have "First Aid" training for example...



I have tbl_BasicDetails, tbl_TrainingReceived and tbl_TrainingTypes. I can create a query that tell me who has no training at all but i just can't create one that tells me who out of everyone does not have certain training (i have added a yes/no field called Required? to my tblTrainingTypes table) Has anyone any suggestions as i am either making it more complicated than it has to be or i need to change the design!

[alansidman]

Amanda;
What are your fields in each table. Difficult to advise without all the details. Not sure if the field you are trying to write criteria to is a Yes/No or contains a string text. Sample db posted would help.
Alan

[Amanda Burlingham]

Hi, not sure how i post a sample database but the fields in tables are as follows: tblTrainingTypes - TT_ID, TT_Name, TT_Category & TT_Required? (TT_Required is the only Yes/No field and this table is used as a look up table when entering peoples training.) tbl_BasicDetails has loads of fields starting with BD_ID, then BD_FirstName... with all address, DOB etc and then i have another table, tbl_TrainingReceived which contains...TR_ID, BD_ID, TT_ID (Chosen from the first table) then TR_DateReceived, TR_DateExpire sorry hope this is what you were asking for????

[alansidman]

To post a file, hit reply, then scroll down to Manage Attachments and follow the guide.

I am trying to determine which field contains the information about First Aid Training and which field indicates whether it was received or not. Briefly, if First Aid is a record in one of the fields and Training Recieved is Yes/No in anohter field, you will need to set your criteria in the First Aid Field to "First Aid" and in your training received field set the criteria to "no"

I hope this helps you. If this is not the case, try to post your db.

[Amanda Burlingham]

Hi, sadly i can't post the db as it has everyones personal data on it. I understand what your saying but it doesn't work here as when i enter training i am already in the persons record, i then choose the training they have from a drop down list which is based on tbl_TrainingTypes. I think what i should have done to make things simple is in everyones training record list every single type of training and then have a "received" yes/no field and then the dates etc... i know our old DB designer struggled with this report and could only produce it by exporting to excel after writing a lot of code !! I am only self taught so i will keep trying - thank you

[alansidman]

I suggest you make a copy of the db, sanitize it to have only a half dozen dummy records. This is very hard to visualize without seeing the layouts, etc. Sounds like your tables are not normalized.

Alan