Solved

searching several tables for records

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 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

10 Experts available now in Live!

Get 1:1 Help Now