UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Subform Through Two Tables, Access 2010    
 
   
Manu_9999
post Jul 20 2019, 12:46 PM
Post#1



Posts: 6
Joined: 20-July 19



I am recently started learning MS-Access, While learning I got one doubt that, How can I create a SubForm with 2 Tables. Without using query editor. Below is the details
I have two tables (S_School_Details, S_Personal_Information) looks like attached

SubForm Should have details like... (Inner Join b/w two Tables). Student Name, Father Name, DOB, Class, Attendence, Phone Number.

Could someone help me on this.
Attached File(s)
Attached File  3D60A928_2DE5_46AE_A49F_4942672E6283.jpeg ( 32.06K )Number of downloads: 1
Attached File  A9DCC7AB_DF33_43DD_8EF5_3DBF3778B146.jpeg ( 34.66K )Number of downloads: 0
 
Go to the top of the page
 
June7
post Jul 20 2019, 12:52 PM
Post#2



Posts: 732
Joined: 25-January 16



Normally a form does data entry/edit to only one table. Why would you join these tables in a form?

A query statement can be built directly in RecordSource property.


This post has been edited by June7: Jul 20 2019, 01:25 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Jul 20 2019, 01:21 PM
Post#3


UA Admin
Posts: 35,518
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

There are some basic principles behind good Relational Database Application design which we all strive to learn and implement. We also encourage others to learn and follow those principles.

The most important of those are the rules of table design in a relational database. If you've not yet invested time in that area, now is a good time to do so. You can start here.

Also, as June7 pointed out, we do NOT want to do data entry into any form or subform through a query based on two or more tables. That's usually a source of complexity which raises problems we can avoid.

Here we'd have to know more about your tables to offer concrete suggestions. I see a couple of things that do potentially raise red flags. Your prefixes, for example, are "S". In itself, not a problem, but if that "S" means "Student table", then we are going to want to review that closely because it may not be the optimal design to have "student tables" if you also have "teacher tables" or "parent tables" and so on. People are people are people. A person can fill the ROLE of student, or the ROLE of teacher or the ROLE of parent. But we'd normally want to see one table of People and related tables for details about the ROLES (students, etc.) those people can hold. Depending on context, it's possible that a person can be both a student AND a teacher, for example. Think Graduate Teaching Assistants in a college.

In other words, there is more to consider and we need to be sure we're on the right path before getting to the next step of designing forms.

Please tell us more about this Relational Database Application, what it is for, what data it will track and so on.

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Manu_9999
post Jul 21 2019, 06:02 AM
Post#4



Posts: 6
Joined: 20-July 19



My actual requirement is below.

I am trying to create a form with One Text Box with Student Name and SubForm and Search Button.
SubForm Should have details like... (Inner Join b/w two Tables).
Student Name, Father Name, DOB, Class, Attendence, Phone Number

1. When I enter the student Name in form Text Box and click on Search Button should get the above mentioned details
2. If I entered any letter(Like R or S) in Form Text Box, should pull the details regarding that student
3. If I don’t give anything in Text box, It should pull all students details.

Without using Query table can we do this within Form only.
Go to the top of the page
 
June7
post Jul 21 2019, 06:11 AM
Post#5



Posts: 732
Joined: 25-January 16



I provided you a link to example search form in your thread at StackOverflow. Did you study it? - http://allenbrowne.com/ser-62.html

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Manu_9999
post Jul 21 2019, 06:33 AM
Post#6



Posts: 6
Joined: 20-July 19



Thanks for your response, But i am not well aware with Sub Form to be created with Two tables. If possible could you please help me on this.
Go to the top of the page
 
June7
post Jul 21 2019, 06:53 AM
Post#7



Posts: 732
Joined: 25-January 16



Build a query that joins the tables.

Should only allow edits to one table, probably S_School_Details. Include S_Personal_Information only for display. However, there are other ways to display the student info than with a query joining tables. Why don't you use a form/subform arrangement? Main form bound to S_Personal_Information with subform bound to S_School_Details. Apply filter to main form and the subform will synchronize.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Manu_9999
post Jul 21 2019, 07:11 AM
Post#8



Posts: 6
Joined: 20-July 19



Thanks for your response, Could you please help me how to do it from record source. That can help me lot.
Go to the top of the page
 
mike60smart
post Jul 21 2019, 09:48 AM
Post#9


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Can you upload a zipped copy of the Db??


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Manu_9999
post Jul 21 2019, 11:38 AM
Post#10



Posts: 6
Joined: 20-July 19



Hi,

Here i have attached zip file of DB. Please help me on this. Please help me on this.
Attached File(s)
Attached File  Learning.zip ( 25.43K )Number of downloads: 6
 
Go to the top of the page
 
mike60smart
post Jul 21 2019, 11:49 AM
Post#11


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi

From the tables you supplied I have modified them so that the fields in each table now look like those shown in the ER Diagram attached.

This would allow you to Create a Main Form based on tblStudents and a Subform based on tblAttendance.

Attached File  ri.JPG ( 21.78K )Number of downloads: 0

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Manu_9999
post Jul 21 2019, 11:56 AM
Post#12



Posts: 6
Joined: 20-July 19



Thanks, But without modifying tables how can i get it. That's the business question i received.
Go to the top of the page
 
mike60smart
post Jul 21 2019, 12:08 PM
Post#13


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Your uploaded database has the following 2 tables

Attached File  ri.JPG ( 19.83K )Number of downloads: 0


Table 1 Student_Personal has the following fields:-

1. Primary Key of ID with a Data Type of Number
2. Student_Name
3. Fathers_Name
4. Phone

Table 2 Student_Details has the following fields:-

1. Primary Key of ID with a Data Type of Number
2. Student_Name
3. DOB
4. Attendance
5. Class

Problems:-

1. The tables are not related in any way
2. Primary Keys should be a Data Type of Autonumber
3. DOB is in the wrong table
4. Student_Name should not be in both tables

I made the assumption that you want to be able to select a Students Details and be able to enter details of a Class and the Percentage of Attendance.

To fit this scenario I made the changes that I uploaded.

It is not possible to do what you want with the current 2 tables that you have uploaded.

Who gave you this business question ? Is this a school project??

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 08:42 PM