Combining / Merging Records

[alpinegroove]

Occasionally I receive an Access Database file containing the names of approximate 1000 students. The fields are: Student Name, Student ID Number, Date of Birth, Exam Score 1, Exam Score 2, Exam Score 3.

For some reason that is not in my control, instead of all the exam scores being listed in the same record, each student with more than one exam score gets multiple records.

The first record includes data under Student Name, Student ID Number, Date of Birth, and Exam Score 1 but nothing under Exam Score 2 and 3.



The second record includes the same data under Student Name, Student ID Number, and Date of Birth. Exam Score 2 is populated, but Exam Score 1 and 3 are blank.

The third record includes the same data under Student Name, Student ID Number, and Date of Birth. Exam Score 3 is populated, but Exam Score 1 and 2 are blank.

Is it possible to combine the multiple records of each student to produce one record with the three exam scores without doing it manually?

Thank you for reading thus far and for your time.

[jzwp11]

In a properly normalized database, the exam scores would be separate records in a related table not as fields in a table. When a student has many exam scores, that describes a one(student)-to-many(exam scores) relationship


The proper structure would look like this:

tblStudent
-pkStudentID primary key autonumber
-StudentName
-StudentIDNumber
-DateofBirth

tblStudentExamScores
-pkStudentExScoreID primary key, autonumber
-fkStudentID foreign key to tblStudent
-ExamName
-Score

For more on what normalization is, please check out this site


In terms of taking your incoming data and moving into a structure similar to what I show above, that is definitely possible using append queries.

[alpinegroove]

Thank you for your reply.
Unfortunately, I do not have any control over the incoming data, so I would have to do this with a query.

Any suggestions for how to do that?

Thanks again.

[jzwp11]

Once you have your table structure set up, the general approach would be to import or link to the table from the Access database you receive. Then run a series of append queries to move the data from the imported/linked table into your normalized table structure. The actual structure (i.e. SQL text) of those queries will depend on your table structure as well as that of the imported/linked table that you receive. As long as the structure of the received table does not change, the queries will not need to be altered once they are created.

So I would concentrate on getting your table structure set up first.

Are there any other fields that are relevant? Perhaps the subject/class for which the exam scores apply? A date field perhaps?

What is the full structure of the table in which you receive the data?

[alpinegroove]

Thank you for the answer. There was an error in my original message. The file that I get is a .dbf and for about a month, I get it every day and need to transfer/import the data to my Access database daily.

1. Should I try to combine the multiple records in the .dbf file?
2. Should I try to combine the multiple records in the process of importing the data into Access?
3. Or, should I try to do it with a query after it is already in Access?

Thanks again.

[jzwp11]

I am not familiar with the .dbf files but I would guess that Access should be able to link to it via a standard ODBC connection (depending on the software used to create the .dbf). The code necessary for that is beyond my knowledge, so you may want to pose that question via another thread in the forum.

If you are not able to setup a connection with the .dbf file & if you can dump the info from the .dbf file into an Excel spreadsheet, you can link to that through Access very easily (Access would just see the spreadsheet as another table) as an alternate approach.

As to combining data first, I would not recommend it since it would be extra steps that probably would not save you much time in the long run.

[alpinegroove]

Currently the .dbf is indeed saved as an .xls file, but I was just trying to see if that was necessary.
So the plan is to convert the .dbf to .xls and then import that into Access. The .xls will be linked, so each day when I get the new .dbf, I will update the .xls but will not import it again since it is linked.
So the question now is about the query. Assuming that my fields are the ones mentioned in my original message, what would the structure of the append queries look like if I wanted to combine the multiple records?

[jzwp11]

Currently the .dbf is indeed saved as an .xls file, but I was just trying to see if that was necessary.

I believe that if Excel can link to the .dbf then so should Access. If that is indeed the case, then using the Excel file is unnecessary and so might all of the append queries discussed below (see my last paragraph for my theory).

If we were to assume that the imported information is a table with a structure similar to the following:

tblImport
-Student Name
-Student ID Number
-Date of Birth
-Exam Score 1
-Exam Score 2
-Exam Score 3

And the table structure that will receive the information is as follows:

tblStudent
-pkStudentID primary key autonumber
-StudentName
-StudentIDNumber
-DateofBirth

tblStudentExamScores
-pkStudentExScoreID primary key, autonumber
-fkStudentID foreign key to tblStudent
-ExamName
-Score

Then we would need 4 append queries:
qry1: append the student info to tblStudents
qry2: append the Exam 1 scores
qry3: append the Exam 2 scores
qry4: append the Exam 3 scores

I have attached an example database with the appropriate tables and queries. You have to run the query to append the students first. The order for the remaining 3 queries does not matter.

You will only have to add the students once, but since your incoming data is not stagnant you will need to determine which students' information is already in your normalized table structure and which students are new. Once you find the new students, you will have to add those (and only those into your structure). The other issue you will have to deal with is what happens if someone changes existing information in the incoming data since you last append? For example, let's say that student 1 has a date of birth of 1/1/91 and you append that this morning. Then someone else using the .dbf file found that the 1/1/91 was in error and the date of birth is updated in the .dbf to 2/2/92. You will have to now look for any changes that may have occurred since the last time you ran your append. This will require update queries. So basically, you will have to run daily update and/or append queries depending on how the data changes in the .dbf file.

I am beginning to wonder if the data you are receiving has actually been dumped from a normalized structure (in the .dbf) into a spreadsheet that is not normalized. If you can see the structure of the tables in the .dbf, it might indicate that all of this data manipulation is unnecessary and all you would have to do is link to the existing table structure of the .dbf directly from Access.

[alpinegroove]

Thank you very much for your detailed response and the attached file.
I am new to all of this and will need to do some investigation before I can respond.
I really appreciate your help.