We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Updating Access 2003 forms based on distinct queries

Medium Priority
365 Views
Last Modified: 2012-06-27
Hi all,

I have a form based on a two table query. The tables are 'Teachers' and 'Classes' (A teacher can teach many classes) so the query returns an entry for each class taught. This means that if a teacher teaches 3 classes, the teacher will appear 3 times in the form. This is not ideal.

I only want to see a single entry for each teacher who is running class(es) so I did a SELECT DISTINCT on the Teachers primary key field. It worked but it now means I cannot update the form.

I am completely stumped as to what to do about it. I need to be able to update a field on the form (originally from the Teachers table) but cannot.

Thanks in advance,

John

 



 
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
try setting the form's recordset type property to Dynaset (Inconsistent Updates)
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
You need a main/subform design
main form based on teachers table
subform based on classes table.
Master link field: teachers_teacher_id
Child link field: classes_teacher_id
CERTIFIED EXPERT
Top Expert 2016

Commented:
<This means that if a teacher teaches 3 classes, the teacher will appear 3 times in the form. This is not ideal.>

you can use a Form/Subform set up here.
Main form display the teachers
SubForm displays the classes for each teacher selected from the Main form

Author

Commented:
Cool.

Will try these things. I don't really want to see the classes If I'm honest. Just a single entry for the teacher.

Thanks though...

Author

Commented:
Dynaset thing didn't work. Will experiment with subform thing
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
"Just a single entry for the teacher" this may be achieved using top 1 in a select query as a record source for the subform.

Author

Commented:
Hnasr, how would I do that? I would somehow need to select the first entry for each teacher based on the Teacher primary key. Is this possible?
CERTIFIED EXPERT
Top Expert 2016

Commented:
you can have a combo box in the main form with rowSource

select distinct teachers from tableName

you can use this combo box as the Master Link field

Author

Commented:
Hi Capricorn,

I'm not quite sure what you are suggesting. I need the form to display a set of teachers who have classes. I don't want to see ones that don't.

I'm probably being stupid here...
CERTIFIED EXPERT
Top Expert 2016

Commented:
better see this

http://office.microsoft.com/en-us/training/forms-ii-use-subforms-RZ010117901.aspx

and to see teachers that have classes, use a query joining the tables Teachers and Classes using the teachers primary key field

Author

Commented:
Hi there,

The subform thing looks like it could work except for one small detail that I've ommitted.

The query is actually based on three tables. There is a table called 'Modules' and each module can have many classes. The problem is that a field i need to filter on is in the 'modules' table. So I effectively want to subform on a table which is not directly linked to the master 'Teachers' table, but via the 'Classes' table.

Hmmmm. This is tricky!
 
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
If you have the tables properly normalized, then there will be no problem.

To get better input, attach a relevant sample database.

Author

Commented:
Hi there Hnasr,

If you try making a form/subform on a three table query, the wizard just doesn't go for it.

Anyhoo, what I decided to do in the end was create a temp table based on the distinct query.
What I also ommitted was the fact that the updates I need only need to be active for the life of the form session. It's not on update form as such, I just needed to be able to check boxes of certain staff to then trigger off an email process.

Not sure what to do about this...
Retired IT Professional
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
I am sure you have modified form C table.
Form C based on table A==> C
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.