Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

searching several tables for records

Posted on 2011-09-25
3
Medium Priority
?
219 Views
Last Modified: 2012-06-27
hello!
i know that there's a way to do this - but i've never done it before... I have three tables - each keeps track of different information on students. the student is identified by a numeric studentID field in each table.
i need to figure out how to search by studentID to see if there are records in any or all of these tables... that is to say, I'd like to be able to create a form and call a particular studentID - and have the system tell me what records there are in any or all of these three tables for that student. I'm playing around with this and just can't figure it out!
thanks!!
0
Comment
Question by:jpomerantz
3 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 36595979
Do you just want a count of the no of times the student appears in each table?

Or the actual records?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36596434
<the system tell me what records there are in any or all of these three tables for that student.>

If each of these tables contains distinct information about the students, and there can be multiple records per student in any of the tables, I'd think the best way to present that information would be with a mainform with top-level student information and three subforms containing the details from each of those tables for the student.  The main form would be linked to each of the subforms by student ID.

See this tutorial for details:
http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

With that setup, to get the detail records from each of those tables for a given student, all you would have to do is open the main form filtered to that student's ID.

Representative VBA:

docmd.openform "YourFormName",,,"StudentID = " & intStudentID

The form would show the "top level" information for the selected student along with that student's detail records from the three tables.
0
 

Author Closing Comment

by:jpomerantz
ID: 36598224
ok, i'm really kicking myself that I didn't think of this!! this is exactly what I needed
thanks so so much :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question