Qry within a tables field property

[dan231]

I have a table named: tblStudents
I have a fields named Course and Basic Code Enforcement

Within the properties for Basic Code Enforcement, I have this as a Lookup row source:

SELECT IIf([Course]="Residential Inspector Multi-Discipline",1,"") AS Expr1 FROM tblStudents;

It is also set as Listbox, Table/Query

The main goal is to have the field RBasic Code Enforcemente show a 1 if the Course field is Residential Inspector Multi-Discipline. I have been at this all morning and I can not figure this out.

Thanks in advance for any help.

Dan

 

[RuralGuy]

Here's a link to read if you haven't already seen it:
The Evils of Lookup Fields in Tables

 

[dan231]

I have run into major problems with lookup fields before. I have not seen that liunk before, so thank you, but I'm still at a loss of what to do.

 

[ajetrumpet]

Within the properties for Basic Code Enforcement, I have this as a Lookup row source:

SELECT IIf([Course]="Residential Inspector Multi-Discipline",1,"") AS Expr1 FROM tblStudents;

It is also set as Listbox, Table/Query

The main goal is to have the field RBasic Code Enforcemente show a 1 if the Course field is Residential Inspector Multi-Discipline.

Just a thought here:

Whenever I needed a field in (any) table to represent a value that is looked up in another table, I have completed the process in forms. It sounds like you are trying to do work in the tables themselves. Otherwise, why would you need a lookup anything in a table? Doing this sort of thing tables is a no-no anyway!

 

[dan231]

Just a thought here:

Whenever I needed a field in (any) table to represent a value that is looked up in another table, I have completed the process in forms. It sounds like you are trying to do work in the tables themselves. Otherwise, why would you need a lookup anything in a table? Doing this sort of thing tables is a no-no anyway!

I have all the data I need laid out in a different table, but I need it to auto populate to this first table. I have tried numerous times and can't get it to work.

I have 6 main programs with up to 10 classes in each program. I have a form setup to add new students, but I also need this table to show each class that the student is assigned to based soley on the program that is selected. Everything I have tried doesn't update the student table.

 

[RuralGuy]

It sounds like the classes are the many side of a 1:m relationship with the student. That is usually displayed with the Student on the Main form and the classes on a SubForm on the Main form set to continuous form mode.

 

[dan231]

It sounds like the classes are the many side of a 1:m relationship with the student. That is usually displayed with the Student on the Main form and the classes on a SubForm on the Main form set to continuous form mode.

Continuous form mode? I'll have to look that up as I haven't heard that before. I will say that originally that is how I had the form laid out. A Student form with course subform. But I couldn't figure out how to link the classes with the student without manually enterting the classes. I'd like to only select the program and then have all the classes auto selected and linked to that student.

 

[RuralGuy]

You should have a table of student classes that has a separate record for each class a student takes with the student ID in each record. That is how the two tables relate and your LinkChild/MasterFields for the sub form.

 

[dan231]

Just so I'm on the same page here...

This class table - the field names should be each class, then the records will be the student number?

 

[RuralGuy]

I would think you would have a table that describes each class and has an AutoNumber as the primary key to the table. The student table would describe the student and have an AutoNumber as the primary key to *that* table. The "classes" table would then simply have two fields with the PrimaryKeys from each table and a separate record for each class taken by a student.

 

[dan231]

I'm in new territory here...

How does the data get into this new table?
I did link the 2 autonumber fields through the relationship window

 

[RuralGuy]

What current method do you have for assigning classes to students?

 

[dan231]

I have a form (Students). I enter First, Last names, Enroll date, and a drop down to select the program. My goal was to have all classes assigned based upon what program was selected. This part is over my head.

I am now thinking to have a new section on form Students with all classes listed and then just radio button each class in that program. A lot of extra data entry work and not really pretty, but I think I can handle that design.

 

[RuralGuy]

Are there predefined classes based on the Program? Do you have a Student table that simply defines a student with an AutoNumber as the primary key?

 

[dan231]

Are there predefined classes based on the Program? Do you have a Student table that simply defines a student with an AutoNumber as the primary key?

Yes and yes.

 

[RuralGuy]

It would be easier to just make some adjustments to your db if you could post it. It would need to be < 393 KB zipped. You should remove any sensitive data but leave enough sample data so the system still runs. If you would rather not post it I understand. Just post back here and we'll keep going the way we have been.

 

[dan231]

sure, there is only dummy info in there now.

The original tables are tblCertPrograms, tblClassData, tblStudents
The tables I tried on your help are tblClasses, tblStudentsClasses, tblClassStudentMerge

The forms are Form1 = main form
frmStudentMaintenance = entering student data

I just tried to include all data on one table and incorporate that into 2 forms, these are: tblStudentsClasses and frmStudnetMaintBig.
This is what I mentioned in my last 1 or 2 posts.

Thanks for looking at this.

 

[RuralGuy]

Could you try replacing the upload again. I'm getting "Unknown compression" errors when I try to unzip. Maybe zip it again too.

 

[dan231]

here it is again

 

[RuralGuy]

Same problem. What are you compressing it with? Does it decompress on your machine ok?