Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

searching several tables for records

Posted on 2011-09-25
3
Medium Priority
?
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 34

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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