Finding and removing duplicate entries in table

I have a doctors office as a client that is using a mssql and msaccess databases to store information. They are wanting to be able to transfer information from the mssql database to the msaccess database in real time. I wrote a query to insert the information from the mssql database to the msaccess database but now i need a way to search the access db for duplicates and remove them. IT has to be a permanant solution, data is imported daily from the mssql database, I need a solution that will stay in place. I also wrote a append query to add information from dbo_patient (mssql, import) to my "Patients" table (Access) each time its ran it creates duplicates of every patient, I need a way to run it once a day and replace or overwrite the patients already there instead of duplicating them, i can run a delet query but not all fields are identical from mssql and the access databases, so if i delete all, then it erases any new data placed in the "patient" table from access. Any and all help would be appreciated. Thank you.
redrainsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dportasCommented:
Why would you do this? I highly recommend you stick to either Access or SQL Server as the data store. Not both. You can easily use Access as the front-end application without duplicating all the data.
0
redrainsAuthor Commented:
The programs are needed, one is a paid problem called Ortho II and the other is a program that is used to store certain dental records. Both programs are required to run there company.
0
chapmandewCommented:
How are you moving the data from SQL Server to Access?  One way to do this would be to use a view as the replication tool and remove any duplicates that may be transferred before you move the data.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

aikimarkCommented:
There's no need to move the data from SQL Server to MSAccess.  Attach the SQL Server table(s) to your MSAccess database.

File | Get External Data | Link

=============
Once linked, you can query and update the tables from within your MSAccess application front-end.
0
redrainsAuthor Commented:
I have already made the link to the Access database from mssql  that was simple. THere is imformation stored in both access and mssql that needs to be in the same table...I could make a new table but then no forms work and I would have to re-do about 100+ forms in order for it to pull from the combinded table or mssql and access. I even tried renaming the new table to the name of the old one hoping that it would work, but I had problems with the primary key...If i could pull the information from the link, and from the current patients table in a query that I can do....but i get primary key errors onces thats done...if I could some how make that work....that would solve my problem....how would I change the primary key to match the old one from the old table and place it in the new one without having to change hundreds of forms
0
aikimarkCommented:
How are the SQL Server and MSAccess tables defined?
0
chapmandewCommented:
What others are suggesting is that you link SQL Server tables into your  Access db via ODBC...which is great for most intensive purposes.  However, you will not have the data locally...only a link to it via an ODBC connection.
0
redrainsAuthor Commented:
I have already done the ODBC thing, I need to get the data from that into the program without re-doing every form there is in it already...its a stand alone program that was already created...I'm not writing from scratch. I have already got the data into access. I have a table called patients in the access DB but the fields are not the same as the fields in the table imported by using ODBC. I have set relationships between the two hoping it would pull the data that way..but that doesn't seem to do anything at all.
0
aikimarkCommented:
once you attach the SQL Server table(s), do the following:
1. rename your MSAccess table (Pat_OLD)
2. create a new query with the original name of the MSAccess table.  Use the power of the query to rename the fields in the SQL Server data source to those of the (now renamed) MSAccess table.
0
redrainsAuthor Commented:
That sounds like it might just work, but I have a table named patients and a query named qrypatients...do i redo both?
0
redrainsAuthor Commented:
Ok well its about midnight here, I have tried making a new query and now when I run it i keep getting the error  "type mismatch in expression" Please advise
0
aikimarkCommented:
@redrains

Please answer my earlier question:
"How are the SQL Server and MSAccess tables defined?"
0
redrainsAuthor Commented:
You will have to more specific with your question. If you want me to set up a network or build linux firewalls. Or build a PHP based E-Commerce site Im your man, but this access crap is just arggh to me. Please explain what your needing to know and I will tell ya. Or tell me how to find out and I will, but I have no idea atm.
0
aikimarkCommented:
What are the field names and data types and (for text columns) lengths?
0
redrainsAuthor Commented:
Ok the data type is texted, and do you really want me to list the field names? There are alot of them.

just to name an example

MSSQL
Paid, paOPEntry, paFirstname, paLastName, paNickName etc....
Access

PatientID, Entrydate, Firstname, lastname, fullname etc....

So far its 10237 entrys long.
0
aikimarkCommented:
"So far its 10237 entrys long."

That isn't likely in a single table.  What is 10237 entries long?

I'm trying to help you map the MSSQL field names and data types into your MSAccess queries, forms and reports.

Example:
Select
[paFirstname] As [FirstName],
[paLastName] As [LastName],
[paNickName] As [NickName]
From AttachedSQLTable
0
redrainsAuthor Commented:
Well lets see there are 2 tables one is dbo_patients(MSSql Table) the other is Patients(Access Table) Currently the program pulls from Patients in the access table....The fields in dbo_patients do not match fields in Patients they are different. Currently I have a code in place that I can run and it will place the data from dbo_patients into Patients, the problem is evertime I run it, it doubles everything, it will bring over new patients from dbo_patients but it also re-applies all other patients to the bottom of the table therefore doubling all clients in Patients. There are currently 10237 patient listings in Patients Table. If I run my script without deleting everything currently residing in Patients It will become double that and I will have some 20000 entry's. I need either one, to come up with a script that will only add the new patients from the dbo_patients or 2 I need a script that will be permant that way if they enter a new client in dbo_patients it will automaticly show in Patients table. There are 2 programs involved in this, the first is called OrthoII which is using dbo_patients the second program is Othrostat, the first program does scheduleing for appointments in the dentist office the second program they are using to store records for the patients ie.. Teeth Molds, Item numbers for the molds in a card catalog they have in the back room. Currently there are 2 patient ID #'s involved, OrthoII has a ID # and Orthostat also has an ID # this is causing major confusion for the office. I need to pull the ID#, Patient Name, Last Name, Nickname, Address, Phone #, just general contact information from Ortho2 and place it in Orthostat without having to erase the fields because they want to be able to go into Orthostat and see the patients from Ortho2 and add to them with mold #'s etc.. If I have to write a script to erase the table everyday and add the new clients that come in, it will erase the "extra Data" attached to the client. The table fields are different, I have tried putting in relationships but it doesn't seem to work. I could acctually attach the Orthostat program for you to look at. I don't think that i could attach the Ortho2 program because of the SQL server and its a copyrighted program. If attaching Orthostat would help, I have no problems doin that. But what I was trying to say earlier is that the fields in each are different.

Ortho2
table dbo_patient looks like this:
paid paFirstName paLastName paNickName etc..

table Patients
Patient ID FirstName LastName NickName etc...

SQL     Access
paid = Patient ID
paFirstName = FirstName
paLastName = LastName

These are the tables in the 2 programs, I have wrote a select script to move things over to the table in access but as I said since information has to be ADDED to the data from the Ortho2 program, I can't just delete them everyday because it erases the new data, nore can we have double, triple, so on customers in the access table either. Please help! If you would like me to attach the access file let me know, I will Thanks in advance for any help you can give me.
0
aikimarkCommented:
3. My earlier comment addresses the underlying problem, not the symptom.  Your real problem is the system design.  Since you already have one source of data stored in the SQL Server database, there is no reason to duplicate the data in MSAccess.  Trying to duplicate the data causes the duplicate rows -- your symptom.  

I'm trying to take you down a better solution path, rather than just answering your question directly.  In the end, you should have a better system.

4 The best way to prevent duplicates is to have unique keys on your tables.  Append queries will not duplicate uniquely keyed records. (simplest answer to your duplicates problem if you MUST have a copy of the data in the MSAccess database)

5. Without unique keys, you will need to first clean up your duplicate rows, and then modify your append queries to look for existing rows with data indicating matches.  There should also be a set of queries that should be run to update the data in matching rows.  There should also be a set of queries to detect deleted rows and remove them from the MSAccess database.

=======================
"The fields in dbo_patients do not match fields in Patients they are different."
6. That is why I was instructing you to create a Patients query.  A good starting point will likely be your append query.  Please attach that query as a code snippet.

7. Have you considered breaking up your MSAccess table append/updates so that only some of the columns are affected?  Once again, you can use queries to create a view of the joined table data if you create separate tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.