Duplicate Records

[softspoken]

I have a spreadsheet that has duplicate records but i cant take out the duplicates based on just one primary field.

For example: 2 lines of data look the same but they have different addresses which i would need both addresses plus there could be multiple people with different names that stay at that address so I could not use address as a primary key.

I honestly would like to use the whole line of data with all fields and if there is anything different about the fields id like to keep it but if every field is the same i would like for one line to be taken out.



Can someone help me please !!! Oh i am definitely a novice user so I would need some good directions lol ..

Field Names:

First N/ Middle N/ Last N/Suffix/Member SSN ( Not always a populated field )/Date of Birth/Address Line 1/Address Line 2/City/State(All the same)/Zip/Home Phone/Email/Marital Status/Gender/Student/Disabled/Relationship Code/Hire Date/Emploment Status/Affiliation

[TheShabz]

First off, do a google search on database normalization.
What I would do is create four tables, We'll call them Employee (I think those are employees), Address, Relationship, and EmpStatus. Below is how I would arrange it. Table names will be followed by a :. Field names will be listed below them with their datatype in (). some have a / which means you can pick what datatype will fit your needs, just be sure to remain constant throughout. the "PK" stands for Primary Key, which will be the unique identifier for each table. The "FK" stands for Foreign Key, and will be used to create a relationship between tables. Also you will notice a field called ActiveInd in the Employee table. This will be set to 1 for active and 2 for inactive. I did this because I dont like to delete customer/employee information but still need a way to only query for Current customers/employees. Below the 4 tables will be how I would join them in the realtionship window using the following form:
TableA1---1TableB, when you see the 1---1 form, it means just drag from the PK of Table A to the corresponding FK in Table B. when you see the 1---M form, do the same as earlier except now you will doubleclick on the black line that now exists between the tables and check the box for "enforce referential integrity." This will ensure that only data that is in the 1 side can be entered into the table on the M side. Meaning, If you only have a relationshipCode of 1 and 2, a 3 cannot be entered into the Employee table in that field.

Employee:
EmpID (AutoNumber/text, PK)
FName (text)
MName (text)
LName (text)
Suffix (text)
SSN (number)
DOB (Date)
HomePhone (text)
Email (text)
MaritalStatus (text)
Gender (text)
isStudent (yes/no)
isDisabled (yes/no)
RelationshipCode (number/text, FK)
HireDate (Date)
EmpStatusID (number/text, FK)
Affiliation (text)
ActiveInd (number)
AddressID (number, FK)

Address:
AddressID (AutoNumber, PK)
Address1 (text)
Address2 (text)
City (text)
State (text)
Zip (number)

Relationship:
RelationshipCode (Number/text, PK)
RElationshipDesc (text)

EmpStatus:
EmpStatusID (number/text, PK)
EmpStatusDesc (text)

Address1---MEmployee
Relationship1---MEmployee
EmpStatus1---MEmployee

After that, all thats left is the import from Excel. Separate the info for each Table on a different tab. Name the columns EXACTLY as you have them in the tables and use the import wizard to bring it in.

I hope this helped. If that's all you need, please mark the thread solved.

[TheShabz]

To elaborate a bit... I would first bring in the Employee data, and do a query with SQL:
SELECT *
INTO Employee2
FROM Employee
GROUP BY EmpID

That will get all the duplicates out.
Then either delete or rename the original Employee table
Then rename your new Employee2 table Employee, adjusting relationships as needed (I'd recommend waiting until the import is over to do the relationships)
Then you can assign the Current Addresses to each Employee.
Finally fill in all the new fields.

This all sounds like one hellish ordeal but its a one-time thing and will save you many headaches later on.

[Huddle]

I have a similar situation but I’m having a hard time getting my relations correct. Since Contacts Table has 3 PKs I think it is a problem. I couldn’t use an AutoNumber on the contacts because someone could enter a new contact record in association with a new building and the autonumber wouldn’t match. Please let me know what I’m doing wrong.

Objective:
· I have a number of different buildings all with an individual primary number
· Each building should have a contact person. Some building may have multiple contacts
· Each person should be connected to one building. Some people may have multiple buildings
· I want to create a command button on the Building Stats Form that will take you to a list of all the people associated with a particular building
· I want to have another command button that will take you to a master list of contacts no matter what building they are connected with. If there are duplicates of a person because they have multiple building I only want them shown once.


Building Stats Table:
Bldg#(Text, PK)
BldgName(Text)
Address(Text)
City(Text)
…..

Contacts Table:
Bldg#(Text, PK)
FirstName(Text, PK)
LastName(Text, PK)
Title(Text)
Agency(Text)
GovPOC
LeasingPOC
ConstPOC
….

Relations:
Bldg#1…..MBldg#