Data Entry Verification

[mkchung]

Hi Everyone,



I've been searching for the past hour on the best way to do data entry verification or double data entry and I've had no luck. Now is also a good time for me to mention that while I'm not an expert at access but I can get by making my forms and tables. I am trying to create an access database for my research project which requires importing a ton of data from excel. The data has values that need to be verified as this information will be the basis for future research done in this area.

Is there a way to verify the data after data entry or a way to do double data entry? What the person before me did is to enter the information into Excel on 2 different spreadsheets then do a comparison in a third worksheet which generates an Error message when there is a typo between the 2.

Thanks in advance!
M

[June7]

Oh boy, this is a new one!

You want to eliminate the Excel?

Similar effort in Access could mean entering same data to two tables. The unique ID would have to be the same for the same record in both tables. Then build a query that joins the tables on the ID fields. Calculation in a field would check if values of the corresponding data fields are the same.

IIf(table1.field1 <> table2.field1,"Error","OK")

[John_G]

What June7 says is correct; but it sounds as if the work has been done in Excel, on sheet three.

What do you mean by data verification? In Excel, you have two spreadsheets, which are supposed to be the same I take it, and Sheet3 find differences. But that doesn't get you very far - when there is a difference, how do you know which one is right?

It seems to me that entering everything twice is a dreadful waste of time. Can you give more details on what you mean by "verification" and do you maybe mean "validation"

John

[recyan]

Pls clear my confusion :

1) Data is being imported from Excel in to table.
-> Make a copy of the table & you have the same data in both the tables.

2) Data is being imported from different sheets in Excel in to different tables in access. You want to verify which data is different in the 2 tables.
-> use unmatched query

3) Data is being entered via Form in Access.
-> You want same data entered in 2 different tables. Why?

Thanks

[Rod]

I'm sitting here fascinated - yes, it's a new one.

If the Access is to replace Excel then I don't think it wise to mimic the Excel system. Rather a conventional approach of a parent-child db design may be appropriate with the parent being the 'reading to be made' and the child being zero-to-many measurements made for that reading. (If there are only ever two measurements then denormalise and define two attributes in the parent table; dispense with the child table.)

If, however, the Access is to be a back-end for a continuing Excel system then ...

[mkchung]

Hi Everyone,

Thanks for all the replies! Let me try to clear up some confusion. The work has been done in Excel but because of the sheer volume of data that they will be generating (Base Pair Sequences of some cultures) and they way they would like to extract the data, they thought that moving to Access would be best. Since I'm interning for my research project and this is a side project (and I'm not completely useless with Access), I was given this task.

I imported all the information from Excel but one of the requests was that someone be able to verify or double check the MIC concentrations for the drugs that are being tested on the organisms. I've limited the combo box to the specific values but there is a worry that someone might misenter them from the chosen fields and when we are trying to determine which counties are affected, a typo would have a negative impact.

I don't think there's a need for duplicate entries but I do understand their concern. Hope this clarifies.

[John_G]

Hi -

If entries in a specific field are limited to a set of specific values, as using a combo box would indicate, then finding entries that are not valid is easy, using SQL, something like this:

Select * from table1 where field1 not in (value1, value2,....) or, if the value list is an a table:

Select * from table1 where table1.field1 not in (select Field1 from Values_Table)

Is that along the lines of what you need?

Now, if there are any values which are valid, i.e. in the values table, but incorrect, those might be very hard to detect unless you have other criteria, or some way to determine if they do not fit a defined pattern.

HTH

John

[June7]

If data entry is restricted to choices in a combobox RowSource, how can user enter wrong data?

I was thinking the data was numeric, in which case you can limit user to a range but nothing prevents entry of 67 as 68 by hitting the wrong key on number keypad or transposing digits, dropping digit, etc.

[orange]

Can you provide some sample data showing the issue/concern? And describe exactly what is the problem.