De-duping and restructuring a table

[Flummoxed]

Hi all,

I'm currently working with student data in a university and I'm having some trouble getting data that I have exported from one data source into a format that I can use to import it to another database that I work with.

I have exported a unique identifier (STU_CODE) from the original data source and a phone number (ADD_TELN). When I do this, due to the relationship between tables in the original data source, one student has many address records, I get a lot of duplication in my query results and export. For example:

STU_CODE ADD_TELN
61005474 01311234567
61005474 01311234567
61005474 07903905912
61005474
61005474 01314596979
61005474 01314596979
61005474
61005474 01506 827 731
61005474 01506 827 731

As you can see the unique identifier appears nine times for one student with blank values and sometimes duplicate values. I have tried to refine the query within the original data source and get better criteria that reduces the one to many aspect but the way it is structured is preventing me from getting anywhere with this.

Ideally I would like the results in a “flat file” format like so:

STU_CODE ADD_TELN ADD_TELN2 ADD_TELN3 ADD_TELN4
61005474 01311234567 07903905912 01314596979 01506827731

Is there any way I can manipulate this data in Access to reformat it to a flat file format?

Hope this makes sense.

 

[DCrake]

What format is the source data in in the first place?

Are you requesting the data correctly?

One of the problems you are dealing with are known unknowns, by which I mean you know that there may be one or more telephone numbers for each student, but until you have imported all the records you don't know who has the most numbers.

Ask yourself, are you bothered about additional numbers. I would suggest you import the students into one table to get the uniqueness, then import the additional info into another table to get the repetative data and link them together to get the 1-M relationaship.

David

 

[Premy]

Well, you will first have to organize the data correctly.

Create 2 tables: tblStudent and tblStudentTel. tblStudent would have all the unique fields like StudentID, StudentName, StudentAddress, StudentDOB, etc... tblStudentTel would have just 2 fields: StudentID and StudentTel; combine these two in a compound PK.

Create an append query to append all the records from the old table like STU_CODE, STU_NAME, etc, into the corresponding fields of tblStudent. Since StudentID is a PK, duplicate STU_CODES will be automatically excluded during the append process.

Now create an append query to append STU_CODE and ADD_TELN to StudentID and StudentTel in tblStudentTel. Put a "Not Null" criteria under ADD_TELN in the query builder to eliminate the blank fields.

So you will now have a tblStudent with unique records for each student and a tblStudentTel with all the unique phonenumbers for a given student.

U could now relate these 2 tables (1 to many, PK tblStudent and FK tblSTudentTel) and use them in your app AS IS, or u might add extra fields now to tblStudent (Tel1, Tel2, Tel3) and create a sub to lookup each Tel nr in tblStudentTel for a given student, add the first occurence to Tel1, the second to Tel2, etc. After this is done u don't need tblStudentTel anymore, of course. In the end, if it's still necessary, u can now export the data in tblStudent to a flat file. The only problem with the Tel1, Tel2 scenario is that u might end up again with blank fields for students that have a tel nr for Tel1, Tel2, but not for Tel3, Tel4, for example.

HTH

 

[Flummoxed]

Hi guys,

thanks for responding. I realise this stuff is schoolboy error territory so your patience is appreciated...

DCrake - to answer you the source data is stored in a relational database. I use BI Query to export this to an access table or excel spreadsheet.

As far as i can tell there is no better way to request this data. I have tried to narrow down the criteria as much as I can but due to the way phone numbers are stored against addresses in this database it sems that there are always going to be duplicate entries and null values appearing in the results.

Premy - I tried to follow your instructions but the end product I'm after is not an access database with two related tables to look up the phone numbers (which is what I think you are describing?), what I need is one table with the STU_CODE as a PK and the different phone numbers against each record in a flat file format so that I can export all of it to csv to do an import into another relational database.

Yours Hopefully
K

 

[Premy]

Well, the 2 table approach will work in any relational DB, not just access. But if u do want just 1 table with Tel, Tel2, etc. u may do just that by running a sub on the ordered data. The first thing, in either case, is to reorganize your data, removing the dups and blanks, and for that u'll need 2 tables.

HTH