Prev: Prior 3 Mo Avg Function
Next: Combine Two Queries in Different Layouts
From: Matt on 17 Jan 2010 10:14
OKay - this is probably really reall easy but I cannot for the life of me figure it out...

I have 3 tables that I want to cross reference (sort of) -

Table 1 - students, contains fields that specify the training they are to attend - so Everyone, Credit Control etc... - these are all Yes/No fields for easy updating of what training is required for an individual.


Table 2 - the courses, stored with coursename and unique ID

Table 3 - course attendance - contains a student ID and course ID to link a student to a course.


What I want is this (most is already done, just stuck on a dropdown list!)...

The name of the course is stored in a field on the form 'frmCourseSchedule' as the field 'CourseName'. I want to use the value of this field to limit the list of available students in the dropdown list according to whether or not they need the course, and whether or not they have attended:

I need something like:
SELECT FirstName FROM tblStudents WHERE [Forms!frmCourseSchedule.CourseName] = True

So if I wanted to assign people for the 'Everyone' Module the above query would limit to only people who have 'TRUE' in the 'Everyone' field of their record...


can anyone explain how to do this as its driving me mad - I am very proficient but this has really got me flummoxed - how would I then get this to execute in a query?


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Dynamic WHERE clause without exec
http://www.eggheadcafe.com/tutorials/aspnet/63eb37b5-8539-40fc-8f1d-4ee20a205ab2/sql-server-dynamic-where.aspx
From: Duane Hookom on 17 Jan 2010 15:02
If you have training names as field names, then IMO your table structure is
wrong. You need to normalize so you won't have to ask how to get field names
into a combo box. You will more easily be able to display field values once
your tables are set up correctly.

--
Duane Hookom
Microsoft Access MVP


"Matt Shiells-Jones" wrote:

> OKay - this is probably really reall easy but I cannot for the life of me figure it out...
>
> I have 3 tables that I want to cross reference (sort of) -
>
> Table 1 - students, contains fields that specify the training they are to attend - so Everyone, Credit Control etc... - these are all Yes/No fields for easy updating of what training is required for an individual.
>
>
> Table 2 - the courses, stored with coursename and unique ID
>
> Table 3 - course attendance - contains a student ID and course ID to link a student to a course.
>
>
> What I want is this (most is already done, just stuck on a dropdown list!)...
>
> The name of the course is stored in a field on the form 'frmCourseSchedule' as the field 'CourseName'. I want to use the value of this field to limit the list of available students in the dropdown list according to whether or not they need the course, and whether or not they have attended:
>
> I need something like:
> SELECT FirstName FROM tblStudents WHERE [Forms!frmCourseSchedule.CourseName] = True
>
> So if I wanted to assign people for the 'Everyone' Module the above query would limit to only people who have 'TRUE' in the 'Everyone' field of their record...
>
>
> can anyone explain how to do this as its driving me mad - I am very proficient but this has really got me flummoxed - how would I then get this to execute in a query?
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> SQL Server Dynamic WHERE clause without exec
> http://www.eggheadcafe.com/tutorials/aspnet/63eb37b5-8539-40fc-8f1d-4ee20a205ab2/sql-server-dynamic-where.aspx
> .
>
 | 
Pages: 1
Prev: Prior 3 Mo Avg Function
Next: Combine Two Queries in Different Layouts