Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Updating Access 2003 forms based on distinct queries

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

 



 
0
JohnSaint
Asked:
JohnSaint
  • 6
  • 5
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try setting the form's recordset type property to Dynaset (Inconsistent Updates)
0
 
hnasrCommented:
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
0
 
Rey Obrero (Capricorn1)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
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
JohnSaintAuthor 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...
0
 
JohnSaintAuthor Commented:
Dynaset thing didn't work. Will experiment with subform thing
0
 
hnasrCommented:
"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.
0
 
JohnSaintAuthor 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?
0
 
Rey Obrero (Capricorn1)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
0
 
JohnSaintAuthor 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...
0
 
Rey Obrero (Capricorn1)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
0
 
JohnSaintAuthor 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!
 
0
 
hnasrCommented:
If you have the tables properly normalized, then there will be no problem.

To get better input, attach a relevant sample database.
0
 
JohnSaintAuthor 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...
0
 
hnasrCommented:
"If you try making a form/subform on a three table query"

1st: Check the link supplied in capricorn1's comment

2nd: What you need is ( I add):

table/query A(aid, adesc)
table/query B(bid, aid, bdesc)
table/query C(cid, bid, cdesc)

Form C based on table A
Form B based on table B, as main form, and C as subform :link fields bid, bid
Form A based on table A, as main form, and B as subform, link fields aid, aid
0
 
hnasrCommented:
I am sure you have modified form C table.
Form C based on table A==> C
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now