Assistance dealing with duplicate items imported from Quickbooksneeded please.

[Ward]

Hi. First I'm new to access. I have version 2007. I'm setting up a
mailing list in our small office. We use quickbooks software for our
retail sales. All information is collected there.

My goal on the access side is to compile mailing lists for
advertising. I plan on exporting information from quickbooks, into
excel (i know how to do this) and then importing excel into access (i
know how to do this). My question really relates to the duplicate
records (from repeat customers" that i'll encounter. Quickbooks
doesn't use any "ID" (as far as i've found) for it's internal
database.

I plan on exporting the big quickbooks list once, then doing monthly
exports from quickbooks and imports to access to add additional names
and sales information. I'm a bit confused as to how I should set up
access to receive the information and deal with duplicate names
(usually repeat customers). I plan on having a "retail customers"
table and a "retail sales" table to capture and separate the data. I
hope this all makes sense to you.

Thanks for the assistance.

Ward

 

[Albert D. Kallal]

So you're telling me there is no kind of customer or vendor number that gets
exported with this at all?

OK, the above seems a bit surprising.

What I would then do then is sit down and ask yourself what fields can make
the record unique, or at least unique enough for your purposes.

For example do you have first name, last name, and the company name? (or
perhaps you just have name, and company name, like I say I really don't know
how the export works).

What you then do in the MS access is choose those three or four fields or
even just one field of company name (or whatever), and make a unique index
on that. You set the index to NOT allow no duplicates.

If you make the index unique for this situation, then during an import those
duplicate names based on that index will NOT be imported again.

So, keep in mind that MS access to support what we called a compound index.
That is simply a index that is made up of "several" fields to make "one"
index key.

Simply open up your table in design mode, and then go view -> indexes

You'll see a column called index name

Simply enter the name of a new index you wish to create, lets call it

NoDups

Now the tab move into the next column called Field name. Simply select the
first field name you wish to use for no duplicates (as mentioned, if it's
only one like company, then we are done).

After you select the field, MAKE SURE you change the "unique" setting to
yes.

If you need/want more collums, then click on the row/collum right below the
above field you just entered. Now simply select the next field. (it can get
tricky here if you make a mistake, use the standard method of deleting a row
in a datasheet if you make a mistake - that means you click on the record
selector hit the delete key to clear this).

Anyway continue adding as many fields as you wish to make this unique
perhaps it'll be company name and phone number, whichever you can use to
make of this unique way of identifying who the single record this will
prevent duplicates during importing.

 

[Pat Hartman]

If you export the customer file, you shouldn't see duplicates. I believe
that QB allows you to enter a customer number and you can use that as your
unique identifier in Access. You will need some way of assigning this
number so you may want to start with adding the customer to Access to get
the customer number and then adding the customer to QB. QB does have an
ODBC driver that you can use to get and update data with although I have
never heard anything good about this product. Try browsing the QB
newsgroups to see what people are talking about.