Equality Monitoring Decision in a Database, HELP!

[DinoBaggio]

Hi access gurus,

I am just wondering if someone could give me a little here. I am trying to find out what is the best way to design my database to track equality monitoring. Sounds simple enough but its becoming a bit of a headache for me because I cant think of the best way to do this because not everyone will agree to their details being used for equality monitoring, most do, but legally I need consent before I can allow my database to tract their details and so thats where the pain began.

At the minute I have in my database a customer table and form respectivel. Here the customer must provide much of the information necessary for Equality monitoring, but because I am not allowed to track their detail until consent is given, I have made another table called equality monitoring and made an equality monitoring form. there the user must re-enter the information from the customer form, for example DOB, community memebership etc.



It is not a big problem, everything is still working fine like, but I was just wondering if anywhere here knew of a better way to design this. What I was thinking was the best way was to maybe put a yes/no box on the customer form, or maybe even on the equality monitoring form and from this a macro runs if the user says yes to the box and consents to eqaulity monitoring that autopopulates most of the fields in the equality monitoring form.

Does this make sense? Is it good practice? Does anyone know how to do it? or does anyone have any better suggestions?

I know that it would probably make more sense to simply get the user to enter all the details into the customer details table and form, but because clients must consent to equality monitoring I was not sure how best to go about setting that up and felt the EQ table and form was the simpilest solution.

Many thanks if anyone can help this access novice!! any help is much appreciated people.

[orange]

PLease define Equality monitoring. What is it? How does it work? Do you have an example?

[DinoBaggio]

Hi Orange,

Thanks for getting back to me,

What I mean by equality monitoring is:

I need to keep a track of the ethnic background, community background, marital status etc of my clients, however I legally can only do this if they consent to equality monitoring.

It is a bit of a pain because I need to create charts etc to show the compliation of each category eg ethnic backgrounds that are using the business.

However I can not include every record in the chart because if a client does not consent then their details can not be use and including them would be illegal.

what I have done at the minute therefore is:

created a customer table and form. there the details are all entered about every client.

then i created another table, equality monitoring and a form to match. the user has to enter some of the details again here from the customer table becasue they are the same fields eg, marital status and ethnic background appear in both tables.

I had to create that equality table because there only the clients that have agreed to equality monitoring are stored and I simply made all the equality monitoring charts off that tables data.

This all works fine, but it is a pain that the user has to input the same data twice just because of issues with the consent to equality monitoring.

Does that make better sense?? I cant upload my DB because I am out of office at the minute but I hope the above makes that clearer.

My idea was to store the data in both tables as above, but simply find a way to maybe run a macro so that when the user ticks the consent box that some of the fields in the EQ table are autopopulated. Any ideas??

[orange]

Interesting. In some countries that would be on the edge of violating Privacy Laws. I recognize the need for some of the information, and some of the uses of the information, but how to ensure its proper use is always suspect.

I remember reading an article (Access related) where someone had divided some tables that held confidential data. Those tables were protected by some technique that made them invisible to the general user and processes, and only accessible to designed processes with solid security. I can't recall exactly where it was, but I think it was related to database design and normalization.

You can try google and see if you can find it. In any event, I suggest you build it such that you capture data from a consenting client; then codify it and place it in some table that is only accessible by processes that have security mechanisms built-in (an thoroughly tested). You might even want those tables in a secure read only backend so that no one can get to them "by mistake" or "pushing the wrong key".

If you are presenting any of the ethnicity/privacy info on any display in any format, I would ensure to include some notice that this information was approved for collection and presentation by the client. And also to store the initial collection in a very safe place.

This sort of question re privacy comes up quite often from UK developers working with Health and Personal information.

That's just my $.02 -- but I'd be cautious with whatever you decide.

[DinoBaggio]

Great points orange!

Thanks alot for the information, some of the above you stated I hadnt really considered to that level. The organisation is an employment and training centre and so much of the information is sensitive to each particular case in that regard. I have been designing it to keep very much everything safe, but I hadnt considered some of the above.
There will be no 'general' user however of the system. The system when complete will only be used by those who have been thoroughly tested and educated on the laws of data protection and lawful use. But thanks for the above, very, very interesting points!!!

[orange]

Found it.
http://xa.yimg.com/kq/groups/2069704...malization.pdf

see page 19 and 20. It may be too detailed for your project, but the concept itself may be of value.
It's a great reference in any event.
Good luck.

[DinoBaggio]

Cheers Orange!!

Really Appreciate all the help and information!

Thanks a million !
And Good luck to youself too!!