Need data from 2 tables on one subform

[Scanner29Y]

Greetings to ya'll from shiney Puerto Rico!

(BTW, please excuse my writing, english is not my first language)

Need a little help from you Access-Masters out there. We have a database being used to track attendance to different clases. Now, there is a form, let's call it Group, where we assign multiple disciples to it. A disciple can only be on one group at a time, no biggie. We have two drop downs (look-up bars) to select the level and the class each group is a the moment. From there we keep track of the attendance by just inputing the date on a text box and running an update query. Here are my tables:

Disciples:
Record_id, Firstname, Lastname...etc.
(basicly it stores the disciple's data)

Group_members:
Disciple_id (comes from record_id)
Group_id

Classes:
Class_id
Class_description
Group_level

Fruits:
Disciple_id
Class_id
Date
Notes

The trick is that I need to list all students ([group_members]!Disciple_id] assigned to a group ([group_id] with it's attendance ([fruits]![date]) (null or not) to the selected class ([classes]!class_id]) from the combo box. This recordset must be updatable from this form (VBA is ok!)

Any help would be really appreciated!!

Thanks a lot mates!

 

[Scanner29Y]

sample database

ok! by popular demand, here is a sample of the database. I need your help on the group section.

 

[Scanner29Y]

Thank You Pat!!

I didn't see your posting unitl after I uploaded the sample, will make your modifications and let you know how it went.

Once again thanks for replying.

 

[Scanner29Y]

Pat:

Did the fix, but I need to list all disciples on a group no matter what class they have taken. IF they took the class then the date should be reflected. What I got so far is to show the respectives date (for all records on fruits) on the same subform. If I parameter select the query to select only those that match group_Class_id or null then It doesn't list all the students but those who have dates on fruits.

Tried to do some union queries, but it wasn't pretty!!

here is the latest version of the file so that you can take a look at the changes.

 

[Scanner29Y]

Should I be looking at a VBA solution or do you guys think it is feasable to do this ?

 

[Scanner29Y]

GOT IT!!!


PAT!

Thanks a lot for your help, found the missingpart of this puzzle.

All I needed to add to the left Join was OUTER.

That's it, so long goodbye, thank you and "Hasta luego"