Simple - Unique Data

[slunceford]

Hi,

Thanks in advance for any help. I have a large list of data in excel
(one column, 22,000 records). I import this data into access so I can
add more columns (attributes) in bulk, run SQL queries on it, etc. The
problem is that my data source gives me duplicate data sometimes, and
this cannot be helped.

My solution so far - Run a unique filter on the data in excel before
importing. I have set up the one common field as the primary key so it
will not allow duplicate entries.

The problem - When i import the data, it gets rid of data that is not
actually duplicating other data, and I need this data. This even
happens on a clean version of the database. In other words, Excel will
give me a clean list, then if i import it into a blank Access DB, it
filters out even more, and incorrectly i might add

Can someone please tell me how access tells what is a duplicate? And
is there a better way to do this? I am new to access, and usually use
php/perl/mysql/oracle...

Thanks!
Shane

 

[strive4peace]

Hi Shane,

Can you give an example of the type of data you are dealing with?

"then if i import it into a blank Access DB, it filters out even more..."

If you are importing to a new table, nothing should be filtered except
for records where data type does not match. Data type is determined by
your fist 30 or so rows. When I bring data in from Excel, I usually
crate a "dummy" row as the first row so Access will interpret the data
types correctly.

One thing Excel allows you to do is mix data types within a column...
Acess doesn't like that. If it is expecting a number, then something
like "N/A" will throw it off and it may ignore the whole row.

If you are appending to an existing table, I must tell you that it is a
better idea to import into a new table where all the fields are text
data type (so everything comes in) and then convert data to the correct
data types as you transfer records to where they really belong.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[slunceford]

Crystal,

Thanks for your comments, maybe this will clarify.

Can you give an example of the type of data you are dealing with?

URLs (http://www.google.com, etc.)

If you are importing to a new table, nothing should be filtered except
for records where data type does not match. Data type is determined by
your fist 30 or so rows. When I bring data in from Excel, I usually
crate a "dummy" row as the first row so Access will interpret the data
types correctly.

I have set the data type to text in both applications, but I'm sure I
could have messed it up somehow..

If you are appending to an existing table, I must tell you that it is a
better idea to import into a new table where all the fields are text
data type (so everything comes in) and then convert data to the correct
data types as you transfer records to where they really belong.

I actually want them to be text, so this makes sense. However, some of
the URLs have numbers in the domain name. I have not yet checked to
see if this is part of the problem. All of the domains have at least
some letters though, making it doubtful that this is the issue.

Thanks for the feedback!

Shane

 

[strive4peace]

Hi Shane,

you're welcome

is URL the ONLY field you are bringing in?

I would imagine that Access would treat URL as text...

when you say that Access is filtering the records further, what do you
mean by that?

What is the exact problem? Can you give a specific example?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[slunceford]

is URL the ONLY field you are bringing in?

Yes.

when you say that Access is filtering the records further, what do you
mean by that?

I use excel to filter out duplicates. When I import the data into
access, it further reduces the number of records. Access thinks it is
doing me a favor and getting rid of duplicates, but it is getting rid
of unique information.

What is the exact problem? Can you give a specific example?

I think the above defines the exact problem, but I don't have specific
examples here. I can say that there seems to be no rhyme or reason to
the filtering. Numbers don't matter, etc. I think what I need is more
insight into how access determines what is unique. Does it think that
www.domain.com is the same as www.domain.com.cz? That's the sort of
information I am looking for.

Shane

 

[strive4peace]

Hi Shane,

Can you email me a sample of your spreadsheet so I can see what Access
is doing?

You said that you are using Excel to filter duplicates... how are you
doing that?

I find it best to let Access filter duplicates by importing to a table
that is already set up (but I always import to an empty table, not a
live data table) and has a unique index on the field(s) I do not want
duplicated

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[slunceford]

Can you email me a sample of your spreadsheet so I can see what Access
is doing?

Sorry, that is why I am specifically asking for information on how
access determines uniqueness. This is not my data to give.

You said that you are using Excel to filter duplicates... how are you
doing that?

Data -> Filter -> Advanced Filter -> Unique Records Only

I have never had problems with this routine.

I find it best to let Access filter duplicates by importing to a table
that is already set up (but I always import to an empty table, not a
live data table) and has a unique index on the field(s) I do not want
duplicated

This is exactly what I did. Exactly. Access still deletes some
records that are not duplicates. What a piece of crap.

Really, nevermind. I'm just going to set up a server and do it with
php and mysql or something. Access is crap. Unless anyone really
knows how access determines uniquness, don't worry about it. I've
wasted too much time on this.

Thanks for trying.

Shane

 

[strive4peace]

Hi Shane,

Access is the most difficult application in the Office Suite to learn
for a very good reason... it is powerful!

Without seeing your data and without seeing what you are importing into,
you are not even giving us a chance to help you!

Acces does NOT filter out duplicates unless you tell it to -- but it
does make decisions on data types and rejects records that do not
conform... you simply need to learn the rules.

Access is wonderful, in the right hands, but it does take time to learn
-- that is why we are here -- to help you.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Shane,

another note...

instead of doing:

"Data -> Filter -> Advanced Filter -> Unique Records Only

I have never had problems with this routine. "

from Excel...

why not let EVERYTHING come into Access and see what it skipped and see
why...

then, you can filter duplicates from Access

I sense your frustration... do not let it cloud your judgement and do
not judge something that merely does what you tell it to...

Maybe the data is not yours to give... fine, make up some data and try
that! "Measure once, cut twice"... "A stitch in time saves nine"

send me an email (address in my siggy) and request my document titled
"Access Basics for Programming" -- it does not cover programming, but it
does cover some fundamental concepts of Access...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*