Solved

searching several tables for records

Posted on 2011-09-25
3
210 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 33

Expert Comment

by:Norie
Comment Utility
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 500 total points
Comment Utility
<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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now