Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-15-2019, 06:50 AM   #1
Unixaix
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Unixaix is on a distinguished road
input a field from a table and get all information from another table

How to get tblStudent table record data by input studentCode
There are two tables:

tblStudent:
StudentID autonumber
StudentCode Number ; lookup from StudentCode Table
StudentName short Text
StudentAddress short Text

tblStudentCode:
CodeID autonumber
StudentCode short text

Now I want to design a query or form:
get the studentCode from tblStudent, and display studentCode,StudentName, StudentAddress

Please help, I am a new programmer.
Thanks!


Last edited by Unixaix; 04-15-2019 at 12:13 PM.
Unixaix is offline   Reply With Quote
Old 04-15-2019, 06:57 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,454
Thanks: 20
Thanked 366 Times in 357 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: input a field from a table and get all information from another table

Hi. Will this work?
Code:
SELECT * FROM tblStudent WHERE StudentCode=[Enter Student Code]
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 04-15-2019, 07:19 AM   #3
Unixaix
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Unixaix is on a distinguished road
Re: input a field from a table and get all information from another table

Quote:
Originally Posted by theDBguy View Post
Hi. Will this work?
Code:
SELECT * FROM tblStudent WHERE StudentCode=[Enter Student Code]
What I want to do is:
use lookup display studentCode, then I select a StudentCode,
and get the match student information and display in the screen.

Unixaix is offline   Reply With Quote
Old 04-15-2019, 07:23 AM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,545
Thanks: 13
Thanked 1,449 Times in 1,381 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: input a field from a table and get all information from another table

The problems with lookup fields in a table are many. You are about to run into one of them. Even though tblStudent.StudentCode "looks" lit it is text, if it is a Lookup, it is actually CodeID.

1. Remove the lookup from tblStudent
2. Rename the column from StudentCode to CodeID so that you actually know what data is contained
3. Use a query to join the two tables.
Select tblStudent.*, tblStudentCode.StudentCode
From tblStudent Inner Join tblStudentCode on tblStudent.CodeID = tblStudentCode.CodeID

Since you are new, it is best to always use the QBE to create queries.
1. Open the Query Designer
2. Choose tblStudent and then tblStudentCode from the table list and close the dialog.
3. Select the columns you want from each table. Double-click on a column name to do it one at a time (there are other methods to select all).
4. Draw a join line between the two tables connecting on the CodeID field. Using click and drag. A dialog will open. Make sure you have selected the correct two fields. Change the selected values if necessary. Normally you will use an inner join which is the first option. Also, if you have created relationships in the Relationships window, Access will automatically draw the join lines because you have already told it how the tables are related.
5. Save the query using a meaningful (but not overly long name)
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-15-2019, 07:25 AM   #5
Unixaix
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Unixaix is on a distinguished road
Re: input a field from a table and get all information from another table

Quote:
Originally Posted by theDBguy View Post
Hi. Will this work?
Code:
SELECT * FROM tblStudent WHERE StudentCode=[Enter Student Code]
What I want to do is:
use lookup display studentCode, then I select a StudentCode,
and get the match student information and display in the screen.
Unixaix is offline   Reply With Quote
Old 04-15-2019, 07:50 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,454
Thanks: 20
Thanked 366 Times in 357 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: input a field from a table and get all information from another table

Quote:
Originally Posted by Unixaix View Post
What I want to do is:
use lookup display studentCode, then I select a StudentCode,
and get the match student information and display in the screen.
Hi. There's a lot of ways to do this. I am not sure what you have in mind. You could take a look at some demos and see if there's one you could use or post a mockup image, so we can help guide you on how to build it.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 04-15-2019, 07:54 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,545
Thanks: 13
Thanked 1,449 Times in 1,381 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: input a field from a table and get all information from another table

On the bound student form, add an unbound combo in your form's header section to separate it from the bound fields. The RowSource will be tblStudentCode. Make the colum widths 0", 1". This will hide the CodeID and show the studentCode value.

Change the Recordsource for the student form to a query. The query will reference the unbound combo in its where clause:

Select ..
From ..
Where CodeID = Forms!frmStudent!cboLookupStudent

Then in the AfterUpdate event of the combo, add a requery:

Me.Requery

The form will always open blank since when it opens there is no value in the combo. Once a value is added to the combo, the desired record is pulled up.


__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to save in a table an information that is not bounded on a field in the table robert_anisoiu Forms 8 10-28-2010 03:22 AM
Macro To Replace information in a field on a table JBurlison Macros 3 03-20-2008 04:19 PM
I need to update data in a table with information from a calculated field in a form boomdart Queries 4 10-12-2006 06:52 PM
Changing information on a table based on Form Input grantsd Forms 6 01-04-2005 06:56 AM




All times are GMT -8. The time now is 10:31 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World