Solved

searching several tables for records

Posted on 2011-09-25
3
212 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
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 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access on Mouse move 5 31
SQL Server 2012 r2 - Varible Table 3 23
SQL Server - Set Field Values ito Zero Based on Related Table 4 23
IIF help, YN field 7 22
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 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