473,594 Members | 2,726 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Microsoft Access, Double Data Entry and breaking the Normalization rule

Hello everyone,

I'm looking for some advice on whether I should break the
normalization rule. Normally, I would not consider it, but this seems
to be a special case.

I have created an "Outcomes Database" used to store response data
from measures/ questionnaires for a longitudinal health study. It is
essentially derived from Duane Hookom's Survey Database (thanks
Duane!!!), with many modifications added to fit the needs of my lab.
One of the additions, is the ability to do "Double Data Entry" in
order to verify the data (as required by the grant).

All the data is entered into the "tbl_DateE" , with the following 5
Key fields: [DtaPartID] (Participant ID), [DtaQstnE] (evaluation
number), [DtaSrvID] (Survey ID), [DtaEntry] (1st versus 2nd entry),
[DtaQstnQ] (Question Number). There is one other field [DtaRspns] that
contains the participant actual response.

I've written VBA code that compares the first entry to the second
entry, outputting discrepancies to a table. These errors can be easily
printed in order to hand-check against the hard-copy. However, none of
the ways I can think of to do automatic identification of errors as
the data is being 2nd entered make me very happy. The best might be
using a DAO.recordset and the FindFirst command to compare the 2nd to
the 1st. If there is a discrepancy this code would update a "flag"
field. Then these errors could be filtered and corrected.

On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field. These
fields could be compared easier and possibly faster than the FindFirst
method suggested above.

Any thoughts you might have would be much appreciated. I'm hesitant
to break the normalization rule, as in the past this has always come
back to haunt me. . . .

Thanks,
Paul

May 29 '07 #1
20 6902
On May 29, 3:34 pm, "hippome...@goo glemail.com"
<hippome...@goo glemail.comwrot e:
Hello everyone,

I'm looking for some advice on whether I should break the
normalization rule. Normally, I would not consider it, but this seems
to be a special case.

I have created an "Outcomes Database" used to store response data
from measures/ questionnaires for a longitudinal health study. It is
essentially derived from Duane Hookom's Survey Database (thanks
Duane!!!), with many modifications added to fit the needs of my lab.
One of the additions, is the ability to do "Double Data Entry" in
order to verify the data (as required by the grant).

All the data is entered into the "tbl_DateE" , with the following 5
Key fields: [DtaPartID] (Participant ID), [DtaQstnE] (evaluation
number), [DtaSrvID] (Survey ID), [DtaEntry] (1st versus 2nd entry),
[DtaQstnQ] (Question Number). There is one other field [DtaRspns] that
contains the participant actual response.

I've written VBA code that compares the first entry to the second
entry, outputting discrepancies to a table. These errors can be easily
printed in order to hand-check against the hard-copy. However, none of
the ways I can think of to do automatic identification of errors as
the data is being 2nd entered make me very happy. The best might be
using a DAO.recordset and the FindFirst command to compare the 2nd to
the 1st. If there is a discrepancy this code would update a "flag"
field. Then these errors could be filtered and corrected.

On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field. These
fields could be compared easier and possibly faster than the FindFirst
method suggested above.

Any thoughts you might have would be much appreciated. I'm hesitant
to break the normalization rule, as in the past this has always come
back to haunt me. . . .

Thanks,
Paul
Paul,

Sounds like a good time to denormalize. In my eyes, these are separate
data points that are part of the same real-world object.

Normalization is really important, and it's good that your tendency is
to normalize, but there are many cases where strict normalization
would lead to a poor representation of what's in the real world, and
your job is to make your database reflect the real world, not to make
your database match up to a set of rules. The rules only exist because
they are usually helpful in doing that modeling. In cases such as this
it's good to question normalization.

JeremyNYC

May 29 '07 #2
On May 29, 3:45 pm, "absolutejunkfo rjer...@gmail.c om"
<absolutejunkfo rjer...@gmail.c omwrote:
On May 29, 3:34 pm, "hippome...@goo glemail.com"

<hippome...@goo glemail.comwrot e:
Hello everyone,
I'm looking for some advice on whether I should break the
normalization rule. Normally, I would not consider it, but this seems
to be a special case.
I have created an "Outcomes Database" used to store response data
from measures/ questionnaires for a longitudinal health study. It is
essentially derived from Duane Hookom's Survey Database (thanks
Duane!!!), with many modifications added to fit the needs of my lab.
One of the additions, is the ability to do "Double Data Entry" in
order to verify the data (as required by the grant).
All the data is entered into the "tbl_DateE" , with the following 5
Key fields: [DtaPartID] (Participant ID), [DtaQstnE] (evaluation
number), [DtaSrvID] (Survey ID), [DtaEntry] (1st versus 2nd entry),
[DtaQstnQ] (Question Number). There is one other field [DtaRspns] that
contains the participant actual response.
I've written VBA code that compares the first entry to the second
entry, outputting discrepancies to a table. These errors can be easily
printed in order to hand-check against the hard-copy. However, none of
the ways I can think of to do automatic identification of errors as
the data is being 2nd entered make me very happy. The best might be
using a DAO.recordset and the FindFirst command to compare the 2nd to
the 1st. If there is a discrepancy this code would update a "flag"
field. Then these errors could be filtered and corrected.
On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field. These
fields could be compared easier and possibly faster than the FindFirst
method suggested above.
Any thoughts you might have would be much appreciated. I'm hesitant
to break the normalization rule, as in the past this has always come
back to haunt me. . . .
Thanks,
Paul

Paul,

Sounds like a good time to denormalize. In my eyes, these are separate
data points that are part of the same real-world object.

Normalization is really important, and it's good that your tendency is
to normalize, but there are many cases where strict normalization
would lead to a poor representation of what's in the real world, and
your job is to make your database reflect the real world, not to make
your database match up to a set of rules. The rules only exist because
they are usually helpful in doing that modeling. In cases such as this
it's good to question normalization.

JeremyNYC
Thanks Jeremy. I'm definitely leaning towards de-normalization in this
case.

May 29 '07 #3
"hi********@goo glemail.com" <hi********@goo glemail.comwrot e:
On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field.
This is not denormalizing. You have the same data but entered twice. It's
perfectly legitimate to do this.

At the concept or overview lefel this is similar to storing the cost and price of an
item when you sell the item. After all the cost and price could change moments
after the transaction is entered.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
May 29 '07 #4
On May 29, 4:23 pm, "Tony Toews [MVP]" <tto...@teluspl anet.netwrote:
"hippome...@goo glemail.com" <hippome...@goo glemail.comwrot e:
On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field.

This is not denormalizing. You have the same data but entered twice. It's
perfectly legitimate to do this.

At the concept or overview lefel this is similar to storing the cost and price of an
item when you sell the item. After all the cost and price could change moments
after the transaction is entered.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab. ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Thanks for the clarification Tony. I'm going ahead with it. So far
it's working great!

May 29 '07 #5
"hi********@goo glemail.com" <hi********@goo glemail.comwrot e in
news:11******** **************@ q69g2000hsb.goo glegroups.com:
I have created an "Outcomes Database" used to store response data
from measures/ questionnaires for a longitudinal health study. It
is essentially derived from Duane Hookom's Survey Database (thanks
Duane!!!), with many modifications added to fit the needs of my
lab. One of the additions, is the ability to do "Double Data
Entry" in order to verify the data (as required by the grant).
Do you have to store the results of the double entry? If not, then
just load the existing record as a recordset and use an unbound form
to check the 2nd entry in the unbound form against the stored entry.
You could even use a form bound to a single record and just not bind
the controls.

Obviously, if you have to *store* the 2nd entry, not applicable,
though if you want to check at data entry time, this would work even
with a bound form for the new data (using an in-memory recordset
with the first version of the data).

If you want to check for discrepancies in a batch, I recommend doing
it by generating a series of SQL statements, one field for each,
that checks the values in the two tables against each other, and if
something is found, writes to a table what the discrepancy is.

Both of these ideas are something I've already implemented in other
apps.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 30 '07 #6
"ab************ *******@gmail.c om" <ab************ *******@gmail.c om>
wrote in news:11******** *************@g 4g2000hsf.googl egroups.com:
Sounds like a good time to denormalize.
If both versions of the data need to be stored, I see no reason why
it should be considered denormalization .

I don't think I'd consider doing both versions in one record,
though. I think it's better to either have two tables with the same
structure, or use a "narrow" table for the second entry, where you
record in each field the value entered and which field it is, and
which main record it applies to. Or, if the 2nd entry doesn't have
to be recorded, you could record in this table only the data entry
discrepancies.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 30 '07 #7
"Tony Toews [MVP]" <tt****@teluspl anet.netwrote in
news:up******** *************** *********@4ax.c om:
"hi********@goo glemail.com" <hi********@goo glemail.comwrot e:
>On the other hand, it seems that it might be easier to
"de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field.

This is not denormalizing. You have the same data but entered
twice. It's perfectly legitimate to do this.

At the concept or overview lefel this is similar to storing the
cost and price of an item when you sell the item. After all the
cost and price could change moments after the transaction is
entered.
Yes, but there are different ways to store the data:

1. double the fields in a single record.

2. have two records in a single table, with identical structure.

3. have two separate tables with identical structure.

4. have a main table and then a narrow side table that records only
the discrepancies, one field per record.

The worst of all seems to me to be the 1st choice, which is, I
believe, what the OP is considering.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 30 '07 #8
On May 29, 8:42 pm, "David W. Fenton" <XXXuse...@dfen ton.com.invalid >
wrote:
"Tony Toews [MVP]" <tto...@teluspl anet.netwrote innews:up****** *************** ***********@4ax .com:
"hippome...@goo glemail.com" <hippome...@goo glemail.comwrot e:
On the other hand, it seems that it might be easier to
"de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field.
This is not denormalizing. You have the same data but entered
twice. It's perfectly legitimate to do this.
At the concept or overview lefel this is similar to storing the
cost and price of an item when you sell the item. After all the
cost and price could change moments after the transaction is
entered.

Yes, but there are different ways to store the data:

1. double the fields in a single record.

2. have two records in a single table, with identical structure.

3. have two separate tables with identical structure.

4. have a main table and then a narrow side table that records only
the discrepancies, one field per record.

The worst of all seems to me to be the 1st choice, which is, I
believe, what the OP is considering.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Thanks David. Unfortunately, I have to store the 2nd entry so the
unbound form method would not work.

Currently the db is set-up with option 2 (two records in same table).
This works very well for validation purposes, when the validation is
done after the entry. The VBA code that I developed easily compares
Entry 1 to Entry 2 and generates an output. However, for instantaneous
validation, it does not work as well. This is the main reason I'm
considering switching to option 1. I think it would allow for an
easier and more instantaneous comparison of entry 2 to entry 1. It
should actually only add 2 fields to each record (one for the
response, and one to record the time of entry).

Why do you think that this option is the worst? The only potential
disadvantage that I can foresee is that if a "record" is deleted
accidentally, it would be easy to miss. However, the database is well
secured from the end-users, and I have queries that check against
missing records prior to exporting to SPSS (since every participant is
supposed to have the same number of questions).

Your further thoughts would be appreciated.

Thanks,
Paul

PS. I've used two tables in the past, but found that having one table
generally worked better (validations were faster, querying easier,
etc).

May 30 '07 #9
"hi********@goo glemail.com" <hi********@goo glemail.comwrot e in
news:11******** **************@ o5g2000hsb.goog legroups.com:
Currently the db is set-up with option 2 (two records in same
table).
This works very well for validation purposes, when the validation
is done after the entry. The VBA code that I developed easily
compares Entry 1 to Entry 2 and generates an output. However, for
instantaneous validation, it does not work as well. This is the
main reason I'm considering switching to option 1. I think it
would allow for an easier and more instantaneous comparison of
entry 2 to entry 1. It should actually only add 2 fields to each
record (one for the response, and one to record the time of
entry).
I don't see why you can't load the other record in a recordset and
compare the values field by field when they are entered into the
other table. That would be just as easy as comparing to a different
field. You'd do it something like this in the BeforeUpdate of the
control:

If Nz(Me!ControlNa me) <Nz(rs(Me!Contr olName.ControlS ource)) Then
...

You could also write a function that uses Screen.ActiveCo ntrol to do
it, which makes it easy to assign to all the controls (in the above,
replace Me!ControlName with Screen.ActiveCo ntrol).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 30 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1795
by: sparks | last post by:
All we are hearing at work now is we should give up on access. NO NO NO you need to go with foxpro. access is dead and can't do anything compared to foxpro It can make coffee, and forward your email....bla bla bla to the people who have used both...what do you say to these people. who have never used or programmed in either one.
7
736
by: Mathew Hill | last post by:
I am a beginner to the more technical aspects of Microsoft Access (2000) and was wondering if any one can help? I have a field in a table called: ADMIN NUMBER This field should have 4 characters and ONLY NUMBERS should be inputted. Does anyone know what the validation rule should be for this field?
1
9189
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this will work for 9i and even 10g ) No one had what I needed, so I wrote it myself. I Rule. This code isn't going for efficiency, and isn't trying to be dynamic. It doesn't create the table structure in Oracle, that's up to you. (I
49
14308
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The application is relatively big: around 200 tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data (SQL Server 2000), 40 users. I'm wondering what are the disadvantages of using Access as front-end? Other that it's not...
35
3199
by: deko | last post by:
Do I get more scalability if I split my database? The way I calculate things now, I'll be lucky to get 100,000 records in my Access 2003 mdb. Here some math: Max mdb/mde size = 2000 x 1024 = 2,048,000k Let's say on average each record in the database consumes 15k 2,048,000/15 = 136,533 records
182
7454
by: Jim Hubbard | last post by:
http://www.eweek.com/article2/0,1759,1774642,00.asp
4
6386
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database to look a and I am loosing tremendious amounts of time trying to organize it so that I could view it. Regards, Alexandre Brisebois
4
3301
theaybaras
by: theaybaras | last post by:
Hi everyone, You've all been such a huge help to me since joining, and I'd just like to take a second to let you know how much I appreciate it! That said, I have another supplication! ;) I have a db of scientific article citations and data extracted from the papers. When I first made this db I knew nothing of normalization, and that has been a MAJOR pain, as you can well imagine. I have worked to get this normalized and I have just one...
7
5963
by: sharsy | last post by:
Hi guys, I would like to setup a validation rule for a database in microsoft access that restricts data entry so that a certain field can only be filled in if another field has a specific answer (that is selected via a drop down list). Example Field1 - options are "In" or "Out" Field2 - options are "Join" or "Not Joining"
0
7941
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7874
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8368
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8000
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8231
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6652
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5404
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3854
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
1205
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.