Duplicates or making a linked table or what?

[Redpelon]

Ok here is the situation: I'm trying to fix the data entry at my
office, and there is a spreadsheet that they have been using here to
keep track of data. On the spread sheet, they put the persons last
name, first name, city and then the date they came in here and the
name of the referring agency and caseworker. What they have been
doing for years is each time the client would come in, they would just
add a line to the excel spreadsheet, using the same last and first
name, and then just put in the new referring agency or caseworker and
date they came in.... im trying to find a way to split this all up and
somehow make it into an access DB but am baffled on how to extract the
correct data - I want to be able to eliminate all the duplicate names,
and then create a different table (or tables) to store the information
about which agency referred them for this visit, etc. Just in 2008
alone there are close to 3000 records in the spreadsheet so I dont
really feel like going in and fixing that part up.. i am just
wondering if anybody has a creative way to perhaps work with the
spreadsheet to extract and then link correctly..

I have tried importing the spreadsheet with only first and last name,
and then importing it again omitting first and last name, yet keeping
the rest of the information.. I think created a relationship between
the two based on autonumber and they would at least match with the
correct information, but there would still be three entries for john
smith, and each entry would be correctly linked to each john smith
visit...

any ideas? thanks in advance

red

 

[Golfinray]

Pull all the data into a find duplicates query (one of the options on the
query wizard) and set up what fields you want to look for duplicates in. One
way would be then to build an append query and every day or as often as you
needed to add the new clients.

 

[Redpelon]

Pull all the data into a find duplicates query (one of the options on the
query wizard) and set up what fields you want to look for duplicates in. One
way would be then to build an append query and every day or as often as you
needed to add the new clients.

I can get the duplicates found and delete easy enough, my trouble is
then matching each visit to the names i have left and pulling them in
there... for example, if i previously had 4 john h. smiths, I now have
one... yet, before, under each john smith entry, there was information
on each visit.. now I have one john h smith entry with four visits to
attach to him, and i dont know how to get them linked without having
to go through each of the 2000 remaining records to .. line them up or
what have you.

 

[Duke Carey]

WARNING This process is error-prone, since it is probable that more than one
client will have matching names.


So...do you have the table with distinct names yet? Have you created an
autonumber column or found some other way to create a primary key? Once that
is done, say with an autonumber, in a table I'll call Clients, re-import the
entire Excel file into a temporary table. Now create a make-table query that
joins between the Clients table and the temporary table on the basis of the
names. You'll want to include the primary key from Clients and any other
columns from the temporary table that are needed for the new table. Be sure
to select distinct values in the query properties - then go ahead and run the
query.

You should find some additional column that, together with the Clients
primary key, creates a primary key for the new table. Maybe it is
date-of-visit, or maybe you'll need another autonumber column, but you really
want both tables to have primary keys.

 

[Redpelon]

WARNING This process is error-prone, since it is probable that more than one
client will have matching names.

So...do you have the table with distinct names yet? Have you created an
autonumber column or found some other way to create a primary key? Once that
is done, say with an autonumber, in a table I'll call Clients, re-import the
entire Excel file into a temporary table. Now create a make-table query that
joins between the Clients table and the temporary table on the basis of the
names. You'll want to include the primary key from Clients and any other
columns from the temporary table that are needed for the new table. Be sure
to select distinct values in the query properties - then go ahead and run the
query.

You should find some additional column that, together with the Clients
primary key, creates a primary key for the new table. Maybe it is
date-of-visit, or maybe you'll need another autonumber column, but you really
want both tables to have primary keys.

That did it! You are amazing - it at least now joined the two tables
together so now that each john smith entry shares the same ID number,
and i can use that to split them into two tables and insert a sub
table.. THANKS!!