SELECT DISTINCT from different Table

[EastCoast]

Hello,
I have a "Courses" table and a "Classes" table (1 to many relationship - 1 Course = many Classes). The problem is in my "Classes" Form. I want the "CourseName" field from the "Courses" table as a DISTINCT dropdown box inside my "Classes" form.

Example: If I offer a course in "Interviewing Skills", there are 3 different levels of "Interviewing Skills (listed in three separate rows in my "Courses" table) from which to choose, but for this dropdown box in the "Classes" Form, when I click on it, I only want to see ONE occurrence of "Interviewing Skills".

What I have tried:
Changing the RowSource of the "CourseName" field in the "Classes" form. I had:
Control Source: CourseID
Row Source: SELECT [CourseID], [CourseName] FROM Courses;

Any thoughts?

 

[Remou]

I guess that Course ID is different for each skill level, so you cannot include it in the rowsource if you want Distinct to work, however, leaving the ID out is likely to cause other problems.

 

[EastCoast]

Yes, I think you understand my predicament. Each CourseID is different.

Ex: For the course "Typing Intro" the Course ID is 1, for the course "Typing Intermediate" theh Course ID is 2.

I have done distinct rows using a field from the current form (current underlying table), but never a field from a different table.

 

[Opieo]

In your Row Source, trying changing the SQL code to "SELECT DISTINCT [CourseID], [CourseName] FROM Courses;"

Adding the DISTINCT key-word removes all duplicates that the SQL query returns.

Let me know if this helps.

Give a some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[Remou]

You can set the rowsource to
[tt]SELECT Distinct [CourseName] FROM Courses[/tt]

But you will need to include some means of asking the user to select a skill level before the underlying table can be update. If you do this, you can use DlookUp, for example, to find the correct course.

 

[EastCoast]

I did try the DISTINCT option as well, but then I had to change the ColumnCount and ColumnWidth to display only one column (CourseName) but then whatever I choose in the dropdown arrow ("Typing" for example, is entered into every class for every record - it doesn't just choose "Typing" for that individual record).

I have not heard of DlookUp. To which event or property should I attach this?

 

[yummy7]

have a look on fllowing link

http://msdn.microsoft.com/library/d...n-us/vbaac11/html/acfctDLookup_HV05187182.asp

 

[Remou]

EastCoast
I think in order to achieve what you want, you will have to look at the design of your tables. You will not be able to use an unbound combobox on a continuous form. If you change your courses table so that it does not include skill levels, create a skill levels table, and add a skill levels field to the main table, I think you will get what you want. Otherwise, I do not think it is possible.

 

[EastCoast]

Thanks. I did separate the skill levels into a separate table already (which solved one issue) but still did not solve the issue of pulling in the CourseName field from the Courses table into the Classes form and displaying each CourseName distinctly in the dropdown.

 

[Remou]

I have now got confused. Why should the courses table contain more than one course name per course if the skill levels have been separated out? It should be possible to create a bound combo box in a continuous form to display courses.

 

[EastCoast]

That makes sense. I think discussing this has helped clarify some things for me. The reason I had the same course listed multiple times in the Courses table (even w/o the Levels field) is because I have distinct description, length, prerequisites, etc. for each individual course. Given what you wrote, it seems like it might solve things just to move most of those fields over to the "Levels" table and just leave the CourseName field and an ID field in the Courses table by themselves.

Thank you for the help