Can I merge two data into one?

[hongluen via AccessMonster.com]

Hi,

How can I merge two data in two different data records into one data?
In data table,
Name Test_1
AAA result_date1
AAA result_date2

Can I make these into:
Name Test_1
AAA result_date1
result_date2

where the two (result_date)'s are in one cell (one data)?
Thank you very much.

 

[Allen Browne]

You really need to use a relational table design here.
3 tables like this:

Student table (one record for each student), with fields:
StudentID AutoNumber primary key
Surname Text
FirstName Text
...

Test table (one record for each test), with fields:
TestID primary key
...

StudentTest table (one record for each time a student sits a test):
StudentTestID AutoNumber primary key
StudentID Number Relates to Student.StudentID
TestID Number Relates to Test.TestID
TestDate Date/Time When this student took this test.
Result Number The student's result in the test.

You can now use an Append query to populate the StudentTest table. Click
Append on the Query menu (in query design view.) Run the append query for
each source table you have.

 

[hongluen via AccessMonster.com]

Dear Allen,
My problem is that I might have created a bad database structure of my
datatables.
Table 1. Student Table
Student ID
Student Name
etc

Table 2. Test Results Table
Student ID
Date (test date)
Test_1 (result of Test_1)
Test_2 (result of Test_2)
Test_3 (result of Test_3)

Can I still use the methods that you suggested?

You really need to use a relational table design here.
3 tables like this:

Student table (one record for each student), with fields:
StudentID AutoNumber primary key
Surname Text
FirstName Text
...

Test table (one record for each test), with fields:
TestID primary key
...

StudentTest table (one record for each time a student sits a test):
StudentTestID AutoNumber primary key
StudentID Number Relates to Student.StudentID
TestID Number Relates to Test.TestID
TestDate Date/Time When this student took this test.
Result Number The student's result in the test.

You can now use an Append query to populate the StudentTest table. Click
Append on the Query menu (in query design view.) Run the append query for
each source table you have.

Hi,

[quoted text clipped - 11 lines]

where the two (result_date)'s are in one cell (one data)?
Thank you very much.

 

[Allen Browne]

Answered in the Reports group