2 files - identify the new customers - write a 3rd file.

  • Thread starter Thread starter Henry
  • Start date Start date
H

Henry

I also posted this in the "Tables & Database" area.
Thanks for reading:
File one is in access xp format. contains 75k records.
Fields are : lastname, firstname, address.
File is from an existing company database.

File two is in excel xp format. contains 20k records.
Fields are : lastname, firstname.
File was data-entry from paper originals.

Process: Compare files, if a name exists on both files
then skip this record, it is already a customer on both
lists. But if the name exists in FILE_ONE and not in
FILE_TWO then we have identified a new customer. Write the
lastname, firstname and address from file one into a new
file called "file_three".

We are attempting to capture the records from file_one
that are not in file_two by using lastname and firstname
as qualifiers. The results are written to file_three.

Files:
file_one : existing file - access xp format
file_two : data-entry file - excel xp format
file_three: new file - the records from file one that are
not found in file_two.

I have not used access in over 8 years. I need the basics
to get this started. I am VERY rusty in this process. ANY
help you can provide is GREATLY APPRECIATED!!!

Thanks
(e-mail address removed)
 
Henry said:
I also posted this in the "Tables & Database" area.
Click to expand...

I you feel it necessary to post the same question to two
newsgroups, please cross post it instead of multi-posting.
To cross post, place the names of both news groups separated
by a comma in the message's "newsgroups" header. This way,
both newsgroups can see the responses from folks in the
other newsgroup.

With a multi-posted message, folks in one newsgroup have no
idea what responses you've received in the other newsgroup
and you have to chase back and forth between the two groups
to see what answers you've gotten.

File one is in access xp format. contains 75k records.
Fields are : lastname, firstname, address.
File is from an existing company database.

File two is in excel xp format. contains 20k records.
Fields are : lastname, firstname.
File was data-entry from paper originals.

Process: Compare files, if a name exists on both files
then skip this record, it is already a customer on both
lists. But if the name exists in FILE_ONE and not in
FILE_TWO then we have identified a new customer. Write the
lastname, firstname and address from file one into a new
file called "file_three".

We are attempting to capture the records from file_one
that are not in file_two by using lastname and firstname
as qualifiers. The results are written to file_three.

Files:
file_one : existing file - access xp format
file_two : data-entry file - excel xp format
file_three: new file - the records from file one that are
not found in file_two.
Click to expand...

You should be able to link to file 1 and file 2, then use
the query wizard to create a Find Unmatched Records query.
Once that's working, change it to a Append query to add the
data to a table in file 3.
 
You STUPID troll user. How dare you add your bullshit
comments to this request for assistance. Have you nothing
better to do this Sunday afternoon? Better do a resume as
their is no longer any reason to ask Americans like you
for assistance. Next time I will go to the new source for
computer assistance - INDIA. You know them, you hate them
and you fear that they will soon have your job. Lord knows
they are 100 times the workers your kind are. You just
keep up your bitching - no one wants to deal with
critics. IBM, Dell and every other computer company is
RUNNING away from America to avoid your type. Look how far
the computer revolution has fallen!

-----Original Message-----
Henry said:
I also posted this in the "Tables & Database" area.
Click to expand...

I you feel it necessary to post the same question to two
newsgroups, please cross post it instead of multi-posting.
To cross post, place the names of both news groups separated
by a comma in the message's "newsgroups" header. This way,
both newsgroups can see the responses from folks in the
other newsgroup.

With a multi-posted message, folks in one newsgroup have no
idea what responses you've received in the other newsgroup
and you have to chase back and forth between the two groups
to see what answers you've gotten.

Marsh
MVP [MS Access]
.
Click to expand...
 
Henry,
You have got to be kidding! Your comments are so bizarre that I can't even
begin to understand your state of mind when your wrote this.
--
Duane Hookom
MS Access MVP


henry said:
You STUPID troll user. How dare you add your bullshit
comments to this request for assistance. Have you nothing
better to do this Sunday afternoon? Better do a resume as
their is no longer any reason to ask Americans like you
for assistance. Next time I will go to the new source for
computer assistance - INDIA. You know them, you hate them
and you fear that they will soon have your job. Lord knows
they are 100 times the workers your kind are. You just
keep up your bitching - no one wants to deal with
critics. IBM, Dell and every other computer company is
RUNNING away from America to avoid your type. Look how far
the computer revolution has fallen!

-----Original Message-----
Henry said:
I also posted this in the "Tables & Database" area.
Click to expand...

I you feel it necessary to post the same question to two
newsgroups, please cross post it instead of multi-posting.
To cross post, place the names of both news groups separated
by a comma in the message's "newsgroups" header. This way,
both newsgroups can see the responses from folks in the
other newsgroup.

With a multi-posted message, folks in one newsgroup have no
idea what responses you've received in the other newsgroup
and you have to chase back and forth between the two groups
to see what answers you've gotten.

Marsh
MVP [MS Access]
.
Click to expand...
Click to expand...
 
Gee, Henry lighten up a bit.

99% of the people here are complete volunteers and don't owe you an answer.
They don't owe you anything in fact.

The poster simply suggested that multi-posting the same question does waste
more time, and also causes many of us who answer lots of questions to have
to re-read, and re-check if the question has any answers, or follow-ups.

The suggestion is really a very minor thing, and it the suggestion is simply
to help these newsgroups function in a more effective manor.

I saw nothing in that suggestion to you that was disrespectful, or even
condescending. It is just plain old simple suggestion.

With such a display of rage to a simple suggestion to improve the quality of
these newsgroups, I can't imagine what your reaction would be like if I
suggested some different way solving your problem then others here.

Your reaction here really does not encourage people like to want to answer
any of your questions.

Relax a bit, and stay off the coffee.

Anyway, probably the easy way to solve the problem is to either link, or
even better import the 2nd sheet into another table. Assuming that First
name, and lastname are unique, then you can simply build a join between to
two tables in the query builder. Now double click on each join line, and
select the 2nd option (so that all records are included...even when they
don't match). Do this for both first, and last name fields. You see the join
lines turn to a arrow. This is left join.

Then, simply add as a condition to the first name, and 2nd last name field
(in the 2nd table) simply put:
is Null

So, with the above condition, you can run the query and see all names that
don't exists in the 2nd table. Now, while still in the query builder change
the query to a make table query, or append query and run it again, and your
records will be sent to another table.

You also might have to add a condition to the first and last name in the
first table like:

is not null
 
Back
Top