No Duplicates in QRY Results 2

[CindiN]

Is there a way to say "No Duplicates" in the criteria row of a query?

Thanks,
CindiN

 

[Isadore]

Cin: The way to do it is to open up the SQL view drop down, and put "DISTINCT" in front of "SELECT".

Now, say you have 5 columns. If any 2 rows have the same information in all 5 columns, only 1 will be shown.

If you want to eliminate duplicates in any single column, you have to have a 1 column grid query. If you add 2 columns, and you have "SELECT DISTINCT" then you will get distint rows only, but both columns are considered in the DISTINCT statement.

 

[CindiN]

Isadore,

Thanks so much for the response. Sorry for the double post, I realized after I posted here, that my question probably belonged in the Queries Forum. Sorry.

Let me be a little more specific, if I could. I have a table that lists ALL Courses. I have another table that lists ALL Employees. And, I have a third table that lists Course/Employee Results (with a course results field that says Completed or Failed. But if they haven't taken the course, their not listed at all. I am trying to create a query that will give me a list of ALL Courses whether an employee has taken any given course or not. So, for example, I have a parameter that says... [Type Employee Name] in the criteria row of the Employee field (in the query). But what I get is just the courses that employee Completed or Failed. If I add Or Is Null, then I get that specific employees courses and any courses not completed by ANY employee. However, there are still courses that have been completed by other employees (so it's not null), but not taken by this particular employee that doesn't show up in the query results. Can you tell me what to put in the criteria row of the query? I'm not very familiar with SQL or VBA.

Thank you so much for your help.
CindiN

 

[flyboytim]

You need to specify how the join behaves between the courses table and the course/employee result table. In the query design grid, right click on the join line and then what you want is to include all courses and only those results from the course/employee result table that are equal. This is called a LEFT JOIN in the SQL.

 

[CindiN]

I already have the left joins set up, here is the SQL statement:

SELECT DISTINCT DMV_CUR_EMP_CLAS_RESUT.EMPLOYEE_NUMBER, [Employee-LocationTBL].EMPLOYEE_NAME, [Employee-LocationTBL].DivisionDesc, CourseQRY.LevelNumber, CourseQRY.LAMPCertLevelDesc, CourseQRY.LAMP_COURSE_Competency, CourseQRY.LAMP_REQD_TRAINING_UNITS, CourseQRY.CD_COURSE_CODE, CourseQRY.CD_COURSE_DESC, CourseQRY.TRAINING_UNITS, DMV_CUR_EMP_CLAS_RESUT.COURSE_RESULT
FROM CourseQRY LEFT JOIN (DMV_CUR_EMP_CLAS_RESUT LEFT JOIN [Employee-LocationTBL] ON DMV_CUR_EMP_CLAS_RESUT.EMPLOYEE_NUMBER = [Employee-LocationTBL].EMPLOYEE_NUMBER) ON CourseQRY.CD_COURSE_CODE = DMV_CUR_EMP_CLAS_RESUT.CD_COURSE_CODE
ORDER BY [Employee-LocationTBL].EMPLOYEE_NAME
WITH OWNERACCESS OPTION;

However, this statement without any criteria, gives me ALL the employees, what courses they took and their results (completed or failed). It also gives me courses that no employees have taken. But if another employee, (not the one I selected) has taken a course, then that course will NOT show in the results. In other words, it still is not giving me a way to list ALL the courses while showing which courses the employee completed/failed and which courses the employee hasn't taken at all.

Thank you so much for your help. I've been working at this for days.
CindiN

 

[flyboytim]

Have you got:

Courses -> results -> employees

not:

Courses -> results <- employees

It's late (2-12 am), I'll look again in the morning, OK?

 

[CindiN]

Yeah, It's a left join thru all three. This query actually consists of 1 QRY and 2 TBLS. The QRY (Called CourseQRY) is made up of 4 tables. One to Many on the 1st 3 tables and a left join between the 3rd table and the 4th table, which is called [CourseTBL].

Thank you so much for looking at this, I'll wait to hear from you tomorrow.

CindiN

 

[danvlas]

All Employees have to take All courses?

Create a query on both tables and delete any join between them. Include the fields you need from the tables. You must include the EmployeeID and the CourseID
Save the query as Query1 or whatever. It will display alllll possible combinations between Employees and Courses

Create a new query on Query1 and Results table. Left Join them (the arrow should point to the Results table) on EmployeeID and CourseID.
Include all the fields from Query1.
Create a calculated field:
calcCourseResult: Nz(Results!ResultIDField,&quot;Not taken&quot
Run the query.

That should be it.


Good luck,



Daniel Vlas
Systems Consultant

 

[Isadore]

Dan: Thanks for the reply. Gool luck Cindi. Let us know how you do.

 

[CindiN]

Danvlas,

OMG...thank you, thank you, thank you!!!! You are my hero!!! I wish I could give you 50 million stars! Finally, finally....it worked! I can't show my appreciation enough. I've been trying for the longest time to get this to work!!

You ARE the best!
Thanks bunches,
CindiN

 

[danvlas]

Can I have my own statue now???
LOL

You're welcome...


Daniel Vlas
Systems Consultant

 

[CindiN]

You guys are soooo great! If I may, can I ask another question related to this query?

I created the query discussed above, and called it Worksheet2QRY. Now I'd like to be able to type 1 or 120 Employee Names to get a separate report on each employee. I created a parameter on Worksheet2QRY under Employee_Name saying [Please type employee name], and I know I could add several parameters below it for 1st name, 2nd name, etc. And for a few names, that's not a big deal, but for multiple names, that's not feasible. Is there a way to allow the user to either type in from 1 to 120 names or select the names from a list?

The report I created from this query is called FinalWorksheetRPT.

Thanks Guys,
CindiN

 

[CindiN]

If you're responding to this, thanks, but I got an answer on the Microsoft Knowledge Base, #210530 if anyone is interested. However, it presents another problem in printing a report for each employee name. I posted a question for this in the Reports Forum. If you can help, I appreciate it very much!
Thanks,
CindiN

 

[danvlas]

You can have a listbox on your form (I'll call it lstNames). Multiselect: simple.

Paste this function in the general section of form's module (right below Option whatever)

Function NameList() As String
NameList = &quot;(&quot;
For Each itm In lstNames.ItemsSelected
NameList = ListNames & Chr(34) & lstNames.ItemData(itm) & Chr(34) & &quot;, &quot;
Next
NameList = Left(NameList, Len(NameList)-2) & &quot&quot;
If Len(NameList) = 2 Then
NameList = &quot;&quot;
Else
NameList = &quot;EmployeeName In &quot; & NameList
End If
End Function

In your command button OnClick event procedure:

DoCmd.OpenReport &quot;FinalWorksheetRPT&quot;, acViewPreview, , NameList

That should work...

Good luck


Daniel Vlas
Systems Consultant

 

[Isadore]

Dan the man! You are quick - thanks for taking the time; good question and good discussion. Best of luck Cindi.

 

[CindiN]

Dan....I'm sorry, but I'm not too familiar with VB or SQL. I am not currently using a form. So I created one, put all the fields from the Worksheet2QRY on the form, except for Employee_Name. Created a list box for the Employee_Name field, then created a Command Button, where I put your DoCMD statement on the OnClick event. I opened a module for the whole form and pasted your function in it.

I know this is a dumb question, and I've heard the answer before, but can remember.... when I run the form and click on the command button it says... &quot;Can't find the DoCMD in the Macro&quot;. I even tried creating a macro that opens the report &quot;FinalWorksheetRPT&quot; in Print Preview, but I don't know where to put &quot;NameList&quot;.

I'm also not sure what's supposed to happen in the form. Does the user select multiple names so then a report can print for each employee selected?

Can you clarify for me?
Thanks,
CindiN

 

[danvlas]

???...???
Step by step:

1. create a query to retrieve the names you want displayed in the list box. Save it as qryListNames
2. Create an unbound form (New-Design View). You do NOT need the form to display records...
3. Go to View-Code.
4. Place the cursor right below Option {Whatever} and above 'Private Sub Form_Load'
5. Paste my function (from 'Function' to 'End Function')there
6. Go back to the form and put a list box on the form. Change the Name property to lstNames. In its RowSource property, select qryListNames. The MultiSelect property: Simple
5. Put a command button on the form. Make sure it is selected and go to View-Code
6. Paste the entire DoCmd line.
7. Open the form in normal view.
8. Select whatever names in the list box and then click the button.

Your report should open and display the selected employees...

Good luck (and time to learn some VB... )


Daniel Vlas
Systems Consultant

 

[CindiN]

Thanks Dan,

I really appreciate your patience, and I'll work harder at learning VB.

You're the best!!
CindiN

 

[danvlas]

Thanks for appreciation...but there are many guys better than me here...you'll see...
And I myself have learnt a lot in these forums (questions here make the brain work harder and better LOL)

Any time...


Daniel Vlas
Systems Consultant