Merge tables into one Big table

[silver]

Hi guys,

i know its easy but am failing to figure it out...


having the following tables

Students - contains the main details e.g name, D.O.B, address, contact
Sciences - contains 4 science subjects physics,chem,bio maths
arts - contains 4 art subjects literature,history,politics

how can i create one table to merge all of this in a single table

 

[Rabbie]

You shouldn't have Students and Subjects(Science,Arts) in the same table. You data wouldn't be normalized then.

You should have one table for Students
a second table for Subjects ( Physics,chem,bio,maths,politics,history,literature etc)
and a third table to hold details of which student is taking which subject.

The third table would have a field to hold a foreign key from the student table and one from the Subject table.

 

[boblarson]

If this is for viewing, then you would use a QUERY to display the values together but you wouldn't put them all into a table together. You would set it up so that the Students table was first and then probably put a LEFT OUTER JOIN on the other tables so that you can get all of the student info and any info that might be for those students from the other tables.

 

[silver]

i just wanna do it temporarily to print a report....the report contains all the records from the three table so i thought having a large table temporarily would work

 

[boblarson]

i just wanna do it temporarily to print a report....the report contains all the records from the three table so i thought having a large table temporarily would work

Use a query for the report.

 

[silver]

i have tried the following:

SELECT Students.*, [Sciences].Subject, [Sciences].grade,[arts].Subject, [arts].grade
FROM (Students LEFT JOIN [Sciences] ON Students .StudentID = [Sciences].StudentID ) LEFT JOIN arts ON Students .StudentID = arts.StudentID ;

is this correct?...

The problem im having is on viewing on the report firstly i base my report on this query but i have duplicates (i believe this maybe from the joins)..How do i restrict the report to only show a single studentID at a time then the science subjects and grades for that student then the art subjects and grades for the same student...

I have tried to use the 'sorting and grouping' option and based this on the StudentID together with the 'enforce new page after section'

please let me know if im on the right track i feel a bit confused with this

All i want is based on a criteria say age of student/year of study....i print out a report displaying all the data i hold of them in the database i.e. personal details , subjects and grades.

thak you for the time

 

[boblarson]

Would need to see your table structure or perhaps just upload the database.

 

[silver]

I have attached an example which i hope explains my problem...

In the attached file i have Report 1 , Report 2, and Report 3 these show data from the 3 tables in separate reports and in the right format. But what i want is to combine data from all three tables and show in one report grouping by StudentID........I know once i understand that part i will be OK and will apply the knowledge.....

I do appreciate your help.

 

[boblarson]

Actually, I modified your structure to the way it really should be (normalized) and then did a sample query and report for you to look at.

 

[boblarson]

I forgot, I think you wanted a page for each student didn't you? If so, here's a revised version of the database with that report fixed for that.

 

[silver]

Actually, I modified your structure to the way it really should be (normalized) and then did a sample query and report for you to look at.

Thanks a lot Boblarson...Does that however mean i can not do it in the current structure without your modifications?

 

[boblarson]

Thanks a lot Boblarson...Does that however mean i can not do it in the current structure without your modifications?

The current structure is, pardon the word, bad. Go with the new structure and you will have a lot easier time in getting data in reports the way you want them. And I gave a form which shows how you can add data, etc.