Access Database Design Critique

Hi,

I am wondering if someone can critique my database design?

I feel that the database is quite "buggy." For example,

1.) There's a lot of run time errors popping up (see screen shot).

2.) I don't like that you can only see the coded value after the value has been entered in the form view. For example, for questions that have codes associated with a value, all you can see is the code (e.g., 1, 2, 3, 4), not the actual value.  It would be better if the user can see both.

Sometimes if you hit the wrong button, the entire database just vanishes or gets renamed. This is very strange and has happened to me twice while I was trying to give a training on this database. .

I wonder if this is just Access or does my database just have a poor design?

Please see attached.  Any help is greatly appreciated!

regards,
carlynne
ERA-2011-CIDKPC-Final.accdb
CarlynneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YohanFCommented:
1) I cant see the screen shot, so not sure what you are referring to

2) you cant view 2 values at the same time, however you can try a concatenated one as follows:

To change the 'Primary Aid Use' use this as the row source:
SELECT Primary_Aid_Use.Primary_Aid_ID, [Primary_Aid_ID] & " - " & [Primary_Aid_Use] AS [Primary Aid Use] FROM Primary_Aid_Use;

and make column widths as '0' (just zero). So when user select it the appropriate ID will be selected and people can see both id and the value.

And when you say wrong button - which one is the wrong button??

Let me know if you need more info...




0
CarlynneAuthor Commented:
Thanks for your response.

1.) Ah, sorry, my bad. Here's screen shot of common run time errors that pop up pretty often.

2.) I will give it a try.  The only thing is that I have to do this for all of the questions that have coded values and I am not sure if I can use the same code and just switch the table names. I tried doing this before and it worked. But then when I went to change another field, it erased the settings from the other field. I also kept getting a code warning as if I had affected the code and Access couldn't find a form anymore, so it kind-of scared met that I may be messing with the coding.

3.) I don't know what's happening when the database vanishes. It happened twice now but I don't know what I did or the user did to make it vanish. If Access is that easy to manipulate without intent, I think that's pretty scary.

thanks,

carlynne
runtime-error.JPG
0
CarlynneAuthor Commented:
Hi,

I tried following your suggestions for number 2, and it doesn't work at all.  Changing the width to 0 just makes the coded value come up. I am not sure what I am doing wrong.

We can keep trying. It can't be that hard:)

carlynne
ERA-2011-CIDKPC-Final.accdb
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

YohanFCommented:
Hey its not widths you have to change (sorry, I wasn't clear), its 'Column Widths' property.

Basically to add these in to other fields, follow following steps:
>  row source and click on '...' button to go to query builder
> in the query builder leave the first column untouched
> for the second column add something looks like this:
           <table name>.<first column> & " - " & <table name>.<second column>
> Close and save the query
> add column widths as '0' (zero)

hope this helps.

RE: runtime errors, its not very easy to give a definite answer without replicating what happened. However, breakdown what you need to do and add quality checking conditions (i.e.- if something is empty, show a message box saying 'that cant be empty' etc). I am not very good with macros, more of a vba programmer so unfortunately wont be able to help you on that end. Look in to VBA for these, its much more easier (at least to me) and I think its quite controlled than macros as well. Also debugging is very easy too.. (I dont know whether you can debug from macro even)...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
TIP: Since you are using Access 2007 make sure you have the Office 2007 SP2 update installed.

To figure out if you have the update installed see this article on where to find the information in Access:
Where is the About dialog for Office 2007 applications




0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I found your UI design not very User Friendly.  I also think you have some normalization issues with your of your tables.

Do you really not have any saved queries?

I made a few tweaks to form frm_HH.   Note: I did  the "Previous Aid:" combo box a little different  from the others.

 ERA-2011-CIDKPC-Final-HiTechCoac.accdb
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
<,I wonder if this is just Access or does my database just have a poor design? >>
If you don't have the SP2 update installed it might be Access related. That I why I recommend checking to be sure. I have found that If you don't have  SP2  installed it won't matter how good a design you have. You will have issues.
0
CarlynneAuthor Commented:
Hi HiTechCoach,

Thank you so much for your helpful reply. I will respond to your questions below.

1.) I found your UI design not very User Friendly.  I also think you have some normalization issues with your of your tables.

Yes, I agree. I don't think it's very user friendly.  I am really am not a database person and certainly not a programmer but I've been put in this role out of necessity. I  am just trying my best, and I open to any suggestions:) I cannot find any Access trainer where I am in Thailand.

2.) Do you really not have any saved queries?

Uhh, yes. Is it best practice to design the queries before the data entry is complete? Sorry I am just learning all this stuff.

3.) I made a few tweaks to form frm_HH.   Note: I did  the "Previous Aid:" combo box a little different  from the others.

I like that the number and the code shows up together, but I think it would be clearer if it said the number first and then the text. The people doing data entry do not speak English so it may be a bit confusing for them if the number is at the back.

Thanks so much for your advice about the SP2 update, as I knew nothing about this!

Regards,
carlynne
0
CarlynneAuthor Commented:
YohanF,

I also want to thank you for your help with my question.  I understand your instructions now, and I really appreciate your help!

carlynne

0
YohanFCommented:
for help, guidance check the video's etc in this thread http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26930446.html
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
<<3.) I made a few tweaks to form frm_HH.   Note: I did  the "Previous Aid:" combo box a little different  from the others.

I like that the number and the code shows up together, but I think it would be clearer if it said the number first and then the text. The people doing data entry do not speak English so it may be a bit confusing for them if the number is at the back. >>

That was only an example to show you have you could combing the two fields. You can change the order if that works better.

Since I know nothing about the users or the work flow, all I can do if give you examples not recommendations.


0
CarlynneAuthor Commented:
thanks for both of your comments!

carlynne
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.