Combine Multilple Tables

[glg4e]

Access 2003

i have 4 tables that are basicly identical

1st nine weeks, 2nd nine weeks, 3rd nine weeks and 4th nine weeks

they all 4 have the same colums

Name Catagory Grade and key

i whant to combine these 4 tables information
in to one table
total year Grades

with at least the
name catagory and grade.

they only uniqe key is "key" but its just an auto number

 

[Evi]

I suppose you already know that you have a design fault but to combine them
you can use a Union query. If the instructions in Help are just confusing,
please say and we'll try to help. Provide the real names of your tables

I suggest that you rename the Name fields in your tables. Name is a Reserved
Word in Access. It will rebel at some stage and cause Nasty Things to happen
without a word of warning.

Evi

 

[Dale Fye]

Evi, I learned a long time ago, never to assume anything.

The design flaw that Evi is talking about is that in a relational database,
you should never have 4 tables with identical structure. If you have that,
it likely means that your table names have some data meaning (in your case 9
week sets), and you are violating one of the normal forms (cannot remember
which one off the top of my head) of relational database construction. What
you should do is add another column to one of your tables (GradeQuarter),
then insert records for each student, for each quarter (9-week period) in
the same table. Then, when you want to average the students grades over 4
quarters, all you have to do is:

SELECT [Name], Average([Grade]) as AvgGrade
FROM yourTable
GROUP BY [Name]

This assumes that Grade is numeric, rather than alpha.

Agree with Evi about renaming the [Name] field, how about [StudentName] or
something like that?

HTH
Dale

 

[glg4e]

Good news.... i didnt use the name "name" i used Student...


Question for you...

if you had to redesign this table
how would you..

i have a student colum that looks up a table of student names
i have a catagory colum that looks up a table of Catagories (ie math english
spelling)
and i have a grade colum that looks up a table of grades

and the key for a key value..

i have data for 1st 2nd 3rd and 4th nine weeks.

any ideas on how to reorg this table.

ill loolk in to union and let you know if i have any questions

Evi, I learned a long time ago, never to assume anything.

The design flaw that Evi is talking about is that in a relational database,
you should never have 4 tables with identical structure. If you have that,
it likely means that your table names have some data meaning (in your case 9
week sets), and you are violating one of the normal forms (cannot remember
which one off the top of my head) of relational database construction. What
you should do is add another column to one of your tables (GradeQuarter),
then insert records for each student, for each quarter (9-week period) in
the same table. Then, when you want to average the students grades over 4
quarters, all you have to do is:

SELECT [Name], Average([Grade]) as AvgGrade
FROM yourTable
GROUP BY [Name]

This assumes that Grade is numeric, rather than alpha.

Agree with Evi about renaming the [Name] field, how about [StudentName] or
something like that?

HTH
Dale

Access 2003

i have 4 tables that are basicly identical

1st nine weeks, 2nd nine weeks, 3rd nine weeks and 4th nine weeks

they all 4 have the same colums

Name Catagory Grade and key

i whant to combine these 4 tables information
in to one table
total year Grades

with at least the
name catagory and grade.

they only uniqe key is "key" but its just an auto number

 

[glg4e]

WAHOO!
i did my union

Only problem is my join isnt pulling the corect data in to the table.

when it pulls
it only is pulling the Numbers in the keys of the other tables.

IE>.. student name Andrews is key value one in that table
and so therefore instead of showing "andrews" it shows 1

any ideas on how to resolve this

thanks

 

[John W. Vinson]

it only is pulling the Numbers in the keys of the other tables.

IE>.. student name Andrews is key value one in that table
and so therefore instead of showing "andrews" it shows 1

any ideas on how to resolve this

It's showing the number *BECAUSE THAT IS WHAT YOUR TABLE CONTAINS*.

The table may *appear* to contain a student's name, due to Microsoft's
misdesigned, misleading, and thoroughly obnoxious Lookup Field misfeature:
http://www.mvps.org/access/lookupfields.htm

The name exists - and *should* exist! - only in the Students table (or
whatever table contains names). This table quite properly contains only a
numeric link to that table. If you create a Form based on your new unified
table, you can use a Combo Box on the form to display the name while storing
the numeric link - and you don NOT need to use the Lookup Wizard in your table
to do so.

 

[Evi]

I would have this design

TblStudent
StudentID (autonumber, primary key)
SFirstName
SSurname
SAddress


TblCategory
CatID (PK autonumber)
Category (Maths, English)

TblTerm
TermID
TermName (eg Easter Term)
TermStart (date when term starts)
TermEnd

TblStudentCategory
StuCatID (PK)
StudentID (linked from tblStudent)
CatID (linked from tblCategory)
EnrolledDate
FinalGrade (unless you are going to use the grade for the final term, in
which case you would not have this here)


TblStudentResults
StuResID
StuCatID (linked from TblStudentCategory because we want the want the
results for that student in that category - you'll see that the link will
eventually lead us to both the student and the category)
TermID
TermGrade (if you test students each term)
fields to do with that student for that subject for that term

Evi

Good news.... i didnt use the name "name" i used Student...


Question for you...

if you had to redesign this table
how would you..

i have a student colum that looks up a table of student names
i have a catagory colum that looks up a table of Catagories (ie math english
spelling)
and i have a grade colum that looks up a table of grades

and the key for a key value..

i have data for 1st 2nd 3rd and 4th nine weeks.

any ideas on how to reorg this table.

ill loolk in to union and let you know if i have any questions

Evi, I learned a long time ago, never to assume anything.

The design flaw that Evi is talking about is that in a relational database,
you should never have 4 tables with identical structure. If you have that,
it likely means that your table names have some data meaning (in your case 9
week sets), and you are violating one of the normal forms (cannot remember
which one off the top of my head) of relational database construction. What
you should do is add another column to one of your tables (GradeQuarter),
then insert records for each student, for each quarter (9-week period) in
the same table. Then, when you want to average the students grades over 4
quarters, all you have to do is:

SELECT [Name], Average([Grade]) as AvgGrade
FROM yourTable
GROUP BY [Name]

This assumes that Grade is numeric, rather than alpha.

Agree with Evi about renaming the [Name] field, how about [StudentName] or
something like that?

HTH
Dale

Access 2003

i have 4 tables that are basicly identical

1st nine weeks, 2nd nine weeks, 3rd nine weeks and 4th nine weeks

they all 4 have the same colums

Name Catagory Grade and key

i whant to combine these 4 tables information
in to one table
total year Grades

with at least the
name catagory and grade.

they only uniqe key is "key" but its just an auto number

 

[Evi]

Congratulations! It took me loads of goes to get it right.

If you need a quick fix, then create a new query. Add the union query to it.
Add your Student Table which should contain each student's name only once
and join them by StudentID (the primary key field in your Student table and
the 'Foreign Key Field' in your Results table.)
Then you can drag your student names into the query grid
Evi

 

[glg4e]

OK a little confused on this one...

i created a relation ship (1to1) between the student last name and the
student value of the Union

this is giving a type missmatch error

did i miss understand

thanks for all your help

 

[Evi]

You will get a Datatype mismatch if you try and link a text to a number
field. Oddly enough, you will also get it if you use a Reserved Word
(discussed previously) in your table field names. And it won't always
appear - you may be able to use the Reserved Word successfully, until you
try to sort your query then Clunk! An error message!

Do any of these tables have lookups in them (drop down boxes)? I'm guessing
yes because you say:
student name Andrews is key value one in that table and so therefore
instead of showing "andrews" it shows 1. That could be causing the DataType
mismatch.


That sentence didn't make a lot of sense in itself but it could mean that
you have succumbed to the Lookup by using the Wizard to help you to
normalize your table.Please don't do that. You will get to see your student
names eventually in a proper query but we will be both wasting our time if
what you call the studentName field (because that is how it appears to you)
is in the StudentID field hidden behind a lookup.

Did you rename your Name field before Unioning your tables? (I hope they are
divided into 2 fields by now, FirstNames and Surname)
Do any of your students have the same name (if not, you are very lucky! but
you need to get to work before anyone joins who has)

Do you have any other way of identifying a student uniquely? If 2 John
Smiths arrive, how will you know which one got a Fail in Maths? It is vital
that there are no spelling errors in the student's names or that you have
some other way (or combination of ways) of identifying them uniquely.

So to recap, going back to your 4 tables, *assuming that you don't have any
students with the same name (or have some way of uniquely identifying each
one which you haven't mentioned eg date of birth, enrolment number)
You need to sort out our field names,
seperate your first and surname using an Update Query (help available if you
don't know how
Add to each table a way of identifying the different terms, a good one would
be 2 fields called TermStart and TermEnd (don't just use 1 2 3 4 or it won't
be any use next year)
Use an Update Query to add the correct dates to each table.

Join your 4 tables in a Union Query.

Create a new table (TblGrades) with an Autonumber GradeID to hold all the
data (for now)
Click on your closed union query. Go to Insert Query, Design View. Add all
the fields from your Union Query to this new querie's grid
Change this into an Append Query and Append all the fields to your new
table.

Create a new table (TblStudent) with Autonumber StudentID and the Student
fields


Create a query from TblGrades. Add to it, all the student detail fields. Use
Properties and set Unique Values to yes so that each student is only listed
once.

Make it an Append query and append all the student details to TblStudent (ie
don't append the Grades or TermStart and TermEnd - just fields like the
name fields, the enrolment number. (don't delete them from TblGrades yet)


Create a Number field in TblGrades called StudentID

Add TblStudent and TblGrades to a new query.

Join the two queries by enough fields to identify each student uniquely eg
FirstName Surname, DateOfBirth

Use an Update Query to Update the StudentId field in TblGrades so that the
StudentId field in TblGrades has the same value as the corresponding
StudentID in Tblstudent

ie - in the UpgradeTo line put

[TblStudent].[StudentID]

TblGrades will now (we hope) have the correct StudentID for each student)
Check that each student in TblGrades has an ID number.

Use a Find Unmatched query to check the student fields to ensure that the
students are mentioned in both tables.

When you are sure that this is OK. then you can delete the name fields from
TblGrades and Join the 2 tables in the Relationships window.

When you create a query based on these 2 tables, you can now add all the
fields from TblGrades and all the fields (EXCEPT StudentID) from TblStudent
and you will see the student and grades together.

You can now consider creating a form to allow you to enter Students and
Grades correctly.
Evi