Query doesn't find all records & shows duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 2000 DB that houses the information relating to my 400+ employees, such as name, address, references, work history, schedule & dates worked, and training records. I have each of these areas defined as a subform relating the ContactID of my main form. I have 12 training modules listed in a subform & I want to see who needs all 12.

I tried to create a simple query (in query design mode, as I do not know VB) that would return the results of the employees who need a specific training module. In the subform, I use a check box to show if they have completed the training module so I used the criteria [No] without the brackets to find an unchecked box in my Query. I did this for each module. I also added a column that would return the data from the main form so I could tell the records apart.

The result was far less records than I expected and there were specific records that I know should have been returned that were not. Also, duplicate records were returned for the same person. I check the main table though & there is not a duplicate in the main table.

Does anyone know why I would get this result or is there something I am doing wrong. Is there another way I should come at this. Please help.

Jason
 
Try using False, rather than No.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



JasonPDX said:
I have an Access 2000 DB that houses the information relating to my 400+
Click to expand...
employees, such as name, address, references, work history, schedule & dates
worked, and training records. I have each of these areas defined as a
subform relating the ContactID of my main form. I have 12 training modules
listed in a subform & I want to see who needs all 12.
I tried to create a simple query (in query design mode, as I do not know
Click to expand...
VB) that would return the results of the employees who need a specific
training module. In the subform, I use a check box to show if they have
completed the training module so I used the criteria [No] without the
brackets to find an unchecked box in my Query. I did this for each module.
I also added a column that would return the data from the main form so I
could tell the records apart.
The result was far less records than I expected and there were specific
Click to expand...
records that I know should have been returned that were not. Also,
duplicate records were returned for the same person. I check the main table
though & there is not a duplicate in the main table.
Does anyone know why I would get this result or is there something I am
Click to expand...
doing wrong. Is there another way I should come at this. Please help.
 
Your comment about duplicate records suggests that your query design is not
correct. Likely, you are joining two tables by a single link, but perhaps
you should be joining them by two links?

Post the SQL of the query (open the query in design view, then click on icon
at top left and select SQL, copy the text and paste the text into a
message).
 
Back
Top