If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Basic Code to Use In My First Database
I am in the process of planning a new database to monitor the quality of data
entered on to an order administration system. The data is exported into Access 2000 and that is where I come in. My objectives are as follows; DUPLICATE RECORDS The database consists of lots of contacts which can or can not be at the same address. For example, we send x number of catalogues to the same company as we have x number of contacts there. My first job is to identify duplicate contacts. This is made harder becuase if the potential duplicate is at the same address, I only have name to compare. I do hav some ideas and I would like to know if this is the best way to go. The name splits up in to initials, first name, last name. Create different views of the name eg; first 4 characters of the last name, middel 4 characters, last 4 character etc and create "bins" of matches. Depending on the number of bin matches will determine how likely it is that it is a duplicate. Thsi would get round spelling mistakes eg. One account Jon Simmons and another account Jahn Simmons. Is the best way of tackling the problem? I have seen reference to a wizrd called partia duplicate and wonder if this might present a solution. The second part is to give each user a data score depending on the accuracy of the data entered. I know what I want to acheive in "English" but woud not know where to start in terms of building code; Example Record Name 1, Address1, Address2, Address3, Tel No, Post Code, Fax No, Account Type 1 - Compare records from yesterday with records today to find new values 2 - Loop through all new records and for each record. If the record is a direct duplicate then no points, otherwise loop through all of the fields to check; (a) If Something has been entered add to score eg. one point e.g. Name entered, one point added to score (b) Check table of global rules to see if value is the same. By this I was thinking of a table of typical wrongs things that people enter for certains fields Eg Rule No Field Entry Point Deduction 1 Name N/A 0.25 If the value matches, i.e. N/A is entered then take it away from running score e.g. 1-.25=.75 (c) Once the end of the record has been added append the score to the running score for the user in a summary score table i.e. Date User No Of Accounts Entered Possible Score Actual Score xxxx Sean 1 1 0.25 As I say, although, I hope my logic is ok, I am unsure how to use code for example looping. I really hope some one can help me as this is my first project in my new job and this seems rather daunting to me. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help! Database mysteriously deleting | Mark | General Discussion | 10 | October 20th, 2009 04:03 PM |
Report based on rst in other database | swedbera | Setting Up & Running Reports | 5 | October 6th, 2005 11:41 AM |
HELP! CANNOT CONNECT TO SQL SERVER | Glint | General Discussion | 19 | May 9th, 2005 02:47 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Access Error Message when opening database | eah | General Discussion | 3 | January 26th, 2005 10:04 AM |