I am entering daily visit counts to walk-in clinics in our area. There are 6 clinics or sites. So a record is uniquely identified by site and date. Is there a way to prevent the entry of records with a site-date combination that duplicates a site-date combination that already exists in the database?
Thanks.
--Chris
I am entering daily visit counts to walk-in clinics in our area. There are 6 clinics or sites. So a record is uniquely identified by site and date. Is there a way to prevent the entry of records with a site-date combination that duplicates a site-date combination that already exists in the database?
The easiest is: - assume your file name is walk_in.rec and walk_in.qes
0. make a backup of your data to some other media or drive 1. add a text field (fldcontrol), e.g. length 75 to your walk_in.qes file at the bottom. 2. open the file walk_in.rec for entry 3. entry will ask you to update your rec file with changed structure. accept this with a "yes/ok" 4. close the file without entering any new records 5. add to the chk file the following:
assuming your first field is site and second field is clinic number: site mustenter end
clinic mustenter after entry fldcontrol = string(site) + "-" + string(clinic) end end
fldcontrol noenter key unique end
If you enter any again a warning will come out automatically since the key unique is tested at assignment of the value, not at the field (which is never entered).
Let us know on the list whether this works.
regards Jens Lauritsen
Just an extension to previous mail: The principle I described should work, but extend the system replacing site or clinic with date. To get all the old data recoded (the following is not a nice way, but works): 0. turn off the "key unique in fldcontrol" 1 a. go to record 1. b. press and hold enter key down until you have been through all records - this will recode the control field in existing data. (sometimes I put a pencil in the keyboard to lock the enter key). 2 turn on the "key unique" in fldcontrol 3 you should be working allright.
regards Jens Lauritsen
I like it! I had thought along those lines but did not know how to concatenate strings.
I received your PS too; thanks.
One other question: I just became involved in this existing project. The data date back to 2003, and had been recorded in Excel spreadsheets, a separate Excel file for each site, and within each file a separate worksheet for each month, with several years' worth of that month on that sheet. Now everything is in Epidata. Unfortunately, the existing data have many duplicate site-date records. What do you think will be the effect of implementing your suggestion, given this circumstance?
I have the analysis set up to run in R, and I think I can make R ignore duplicate site-date combinations. So the existing duplicates would remain in the database but have no effect on analysis. If need be, could I implement your suggestion only prospectively, for new entries?
Thanks.
--Chris Christopher W. Ryan, MD SUNY Upstate Medical University Clinical Campus at Binghamton 425 Robinson Street, Binghamton, NY 13901 cryanatbinghamtondotedu
"If you want to build a ship, don't drum up the men to gather wood, divide the work and give orders. Instead, teach them to yearn for the vast and endless sea." [Antoine de St. Exupery]
epidata-list@lists.umanitoba.ca wrote:
I am entering daily visit counts to walk-in clinics in our area. There are 6 clinics or sites. So a record is uniquely identified by site and date. Is there a way to prevent the entry of records with a site-date combination that duplicates a site-date combination that already exists in the database?
The easiest is: - assume your file name is walk_in.rec and walk_in.qes
- make a backup of your data to some other media or drive
- add a text field (fldcontrol), e.g. length 75 to your walk_in.qes
file at the bottom. 2. open the file walk_in.rec for entry 3. entry will ask you to update your rec file with changed structure. accept this with a "yes/ok" 4. close the file without entering any new records 5. add to the chk file the following:
assuming your first field is site and second field is clinic number:site mustenter end
clinic mustenter after entry fldcontrol = string(site) + "-" + string(clinic) end end
fldcontrol noenter key unique end
If you enter any again a warning will come out automatically since the key unique is tested at assignment of the value, not at the field (which is never entered).
Let us know on the list whether this works.
regards Jens Lauritsen
EpiData-list mailing list EpiData-list@lists.umanitoba.ca http://lists.umanitoba.ca/mailman/listinfo/epidata-list
So this has worked, mostly, with one remaining problem.
To recap, I created a new field, I called it CONTROL, consisting of site concatenated with date, as a string, and then made that field key unique and no entry. With the key unique inactivated temporarily, (and with the enter key taped down!) I was able to populate CONTROL for all 12,100 records. And prospective entry of new records works--the CONTROL field is populated.
But when I reactivate, in the chk file, the key unique requirement for CONTROL, I can't open the .rec file for entering new data. I get an error message that CONTROL is duplicated between two records (roughly records 2700 and 2701, or thereabouts.) And the file won't load any further. I can see how this is happening; those are probably the first two duplicate records in the file. I'm not surprised, but it does present a problem.
Now this is even more interesting: if I write down the two records cited in the error message about duplicate values on CONTROL, then inactivate the key unique requiremnt, then open the file successfully, then mark one of the DEL (deleted), save the file, exit EpiData, then reactivate the key unique requirement, then try to reopen the file for data entry, I get the exact same error message: those same two records are cited as duplicated on CONTROL, even though one of them is now marked DEL.
Puzzled.
--Chris Christopher W. Ryan, MD SUNY Upstate Medical University Clinical Campus at Binghamton 425 Robinson Street, Binghamton, NY 13904 cryanatbinghamtondotedu
"If you want to build a ship, don't drum up the men to gather wood, divide the work and give orders. Instead, teach them to yearn for the vast and endless sea." [Antoine de St. Exupery]
epidata-list@lists.umanitoba.ca wrote:
Just an extension to previous mail: The principle I described should work, but extend the system replacing site or clinic with date. To get all the old data recoded (the following is not a nice way, but works): 0. turn off the "key unique in fldcontrol" 1 a. go to record 1. b. press and hold enter key down until you have been through all records
- this will recode the control field in existing data. (sometimes I put
a pencil in the keyboard to lock the enter key). 2 turn on the "key unique" in fldcontrol 3 you should be working allright.
regards Jens Lauritsen
EpiData-list mailing list EpiData-list@lists.umanitoba.ca http://lists.umanitoba.ca/mailman/listinfo/epidata-list
Marking the record as deleted does not get rid of the record. You have to Pack the file to eliminate the deleted records (Tool menu) Jamie Hockin
Chris wrote:
Now this is even more interesting: if I write down the two records cited in the error message about duplicate values on CONTROL, then inactivate the key unique requiremnt, then open the file successfully, then mark one of the DEL (deleted), save the file, exit EpiData, then reactivate the key unique requirement, then try to reopen the file for data entry, I get the exact same error message: those same two records are cited as duplicated on CONTROL, even though one of them is now marked DEL.
That did the trick. Thanks.
--Chris Christopher W. Ryan, MD SUNY Upstate Medical University Clinical Campus at Binghamton 425 Robinson Street, Binghamton, NY 13904 cryanatbinghamtondotedu
"If you want to build a ship, don't drum up the men to gather wood, divide the work and give orders. Instead, teach them to yearn for the vast and endless sea." [Antoine de St. Exupery]
epidata-list@lists.umanitoba.ca wrote:
Marking the record as deleted does not get rid of the record. You have to Pack the file to eliminate the deleted records (Tool menu) Jamie Hockin
Chris wrote:
Now this is even more interesting: if I write down the two records cited in the error message about duplicate values on CONTROL, then inactivate the key unique requiremnt, then open the file successfully, then mark one of the DEL (deleted), save the file, exit EpiData, then reactivate the key unique requirement, then try to reopen the file for data entry, I get the exact same error message: those same two records are cited as duplicated on CONTROL, even though one of them is now marked DEL.
EpiData-list mailing list EpiData-list@lists.umanitoba.ca http://lists.umanitoba.ca/mailman/listinfo/epidata-list
epidata-list@lists.umanitoba.ca
-
epidata-list＠lists.umanitoba.ca