How to import a file into Access without creating duplicate entri.

[Guest]

I run a collection call database in Access from a Timberline report that I
need to update weekly. How do I import the new report without creating
duplicate entries. I just need the new entries placed in the list. There
are too many of them for me to add in manually. Also, I have my list divided
by different markets that I am in charge of and I have created a column that
lists these. If I import the new data in the same format, will it put those
new entries in the correct markets automatically?

 

[Jeff Boyce]

"Automagically?" No, I don't think Access is currently designed to know
where incoming data is supposed to go. That's your job, to tell it where to
go.

Without some idea of how your data is structured (table design), it will be
tough to suggest how to import new data.

I'm not sure you and I mean the same thing by "import the new report" -- I
am assuming you wish to import the data, not a formatted report.

As for avoiding duplicate entries, you'll have to tell Access how to tell
that an entry is a duplicate. You can use a unique index to do this, or, if
each record has a unique ID, you could use that. Or, if you update on a
schedule, and the records include a date/time field, you could only append
the records that are "new" (since last date/time).

More specific questions/information will lead to more specific suggestions.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Well, that is part of the problem. I don't know how to tell Access where to
put the data. It is basically a phone list with some other information.

I have each student listed in a row in this order:

First, by the market number and name, Second by the school ID, Third each
student has their own ID, which is a combination of letters and numbers with
each one being specifially assigned to a particular student. The rest is
personal information such as full name, phone number, account balance, last
payment amount, co-signer name, co-signer phone number and then a final
column with my collection notes on the account.

The other issue is that I don't have the option to pull a report with just
the new students listed on it out of timberline. I need to figure out how to
tell Access that if a student ID is duplicated, then its needs to disregard
it and only insert ones that previously did not exist in the database.

Thanks

 

[Jeff Boyce]

Let me make sure I understand.

Your "Timberline" data includes a unique identifier for each student.

Your Access database contains a table of student information, including a
unique identifier for each student.

You want to be able to append data from "Timberline" to Access, but avoid
duplicating student information.

If your Access Student table has a primary key (i.e., Unique) set on the
StudentID, then it will NOT allow a "matching" ID record to be appended.
When you run an append query, Access will reject those records with matching
IDs

Am I missing something ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Yes you are correct. While i was waiting for your response, or any response,
I managed to find out about this "unique" key function. I am trying to index
the database by setting the unique key as the student id; however, setting
the student ID as a unique key is giving me the following error message when
I go to save the design:

The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the fields that contain duplicate data, removed the index,
or redefine the index to permit duplicate entries and try again.

Is this because my first two colums have name IDs for the location and the
school name? For example, my first two columns look like this:

Market School
350 Phoenix HTI-PH

So, if Johnny and Timmy both went to HTI in Phoenix, the first two cells in
their row would read exactly the same. You wouldn't see the different value
(the individual student ID) until the third cell. Is this the problem?
Would moving the Student ID to the first cell fix this or do I need to do
something totally different?

I really appreciate all the help you have given me today. I am trying to
make my job a little more efficient.

Thanks!

 

[Guest]

Okay, I managed to move the student ID column to the first column. That
fixed the problem, when I imported the new data, it successfully threw out
the duplicate data. Now if I could just get them to still be in alphabetical
order.

Thanks for you help, consider this problem solved!