Floundering! (1 Viewer)

[Ani]

Hi out there
I think I need some words of encouragement! Im trying really hard to understand the logic of MS Access and failing! However Im not prepared to give up...its confusing, frustrating and totally addictive!
I am going to bed trying to work out what Im doing wrong and waking up in the morning thinking 'I will try again'!
Anyway I just wanted to say a general Thank You to everyone who posts tips and 'commandments', they will sink in and make sense at some point, I hope.
Cheers
Ani

 

[Alansidman]

If you haven't read this, you should.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

It should help

 

[bob fitz]

Hi out there
I think I need some words of encouragement! Im trying really hard to understand the logic of MS Access and failing! However Im not prepared to give up...its confusing, frustrating and totally addictive!
I am going to bed trying to work out what Im doing wrong and waking up in the morning thinking 'I will try again'!
Anyway I just wanted to say a general Thank You to everyone who posts tips and 'commandments', they will sink in and make sense at some point, I hope.
Cheers
Ani

Keep going. Keep trying. You only fail when you give up. Until then you just find more ways that don't work than you want to find.

 

[Ani]

If you haven't read this, you should.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

It should help

Thank You! Yes the article is a great help. I will have to read some of it again but the section about normalisation is probably the most useful, atm. I do think I have normalised my tables but will check them through again now that I understand what it is!
I am slightly confused as to how far I should decompose. For example I have some fields where there is only one answer but there will be a choice of answers. Should I put the different choices into different fields? eg fields such as 'do you' is answered 'yes or no', if the answer is 'yes' then 'How often' will be answered 'daily, every other day, weekly, not often'.
Am I on the right tracks?
Thank you
Ani

 

[CraigDolphin]

You would ordinarily put the list of choices into a separate lookup/entity table, and only store the key value from that lookup/entity table in the main data table.

Recall that the rowsource for a combo can refer to the lookup table, while storing the value in the field of the main table to which it is bound.

 

[Ani]

You would ordinarily put the list of choices into a separate lookup/entity table, and only store the key value from that lookup/entity table in the main data table.

Recall that the rowsource for a combo can refer to the lookup table, while storing the value in the field of the main table to which it is bound.

I think that is my next move then. Many fields are customers answers, some can be descriptively quantified but the problem I am having, moving my service from paper to MSDB, is that much of the data I collect is descriptive, subjective and unquantifiable.
I understand the importance of setting the data type for the fields, which has helped.
The end point of my service is reliant on my interpretation of the information collected. The end point can not be automated but the information for the end point needs to be recorded, stored and displayed.
I now need to learn how to create a 'combo' and 'lookup table'! Should I do that before trying to create forms?
Also, this may be jumping ahead somewhat but it is going to be difficult to view or display the information from all the tables that I need at the same time, to enable the end point of my service. I need to view the information from at least 4 tables to formulate the end point. Would it be more efficient/easier to create reports/subreports to display the information I need rather than using forms to do that?
Many Thanks
Ani

 

[RainLover]

Should I put the different choices into different fields? eg fields such as 'do you' is answered 'yes or no', if the answer is 'yes' then 'How often' will be answered 'daily, every other day, weekly, not often'.

There is no advantage to using a Lookup for YES/No.

The Daily Weekly etc should be in a separate table as a LookUp.

 

[Lightwave]

Rain lover highlights a good point.

Although it is technically possible to make separate tables for yes no fields there is little advantage in it. My rule of thumb is that separate look up tables for combi boxes are only really worthwhile for ten or more choices. This is very rough rule but might be a useful starting point.

As you get better you will understand when you should follow the hard rules and where it is better to do your own thing.

 

[RainLover]

My rule of thumb is that separate look up tables for combi boxes are only really worthwhile for ten or more choices.

I wouldn't go that far. I would do it for as little as two choices, but that is not the same as Yes /No

A Yes/No answer is a 0 or 1. You can't get any more basic than that.

 

[gemma-the-husky]

a lot of this depends on how you want to use the database

take descriptive text

if that is all it is, then fine - say it's an employee name - it's just a description, and it doesn't matter what the "value" is.

now, take your descriptive text fields. if the values are just descriptive, then there is no issue. howefer, if the values have an impact on other things, and the values are significant in themselves, then a free-text field is not appropriate.

in these cases you need to restrict users ability to enter the text, and the best choice is via a lookup field (ie a field bound to another table)/ one reason for using a separate tsable is that you then only need store the numeric value of the lookup - which is far more efficeint - it alos makes it easy to change the descriptive text without having to re-write pieces of your app, and without requiring cascading updates.

because of all this, the single most important feature of any project is the table structure/data design. get that right, and everything else flows in an easier, and more harmonious way.

also once you offer uses a choice of phrases, you can then take other actions dependent on the choice they made - which is virtually impossible if you allow free text entry.


---------------------

take this

eg fields such as 'do you' is answered 'yes or no', if the answer is 'yes' then 'How often' will be answered 'daily, every other day, weekly, not often'.

why have two fields for this. just one field is sufficient - with options


no
daily
weekly
etc


limiting the options to ones you want to appear is desirable, so this indicates a separate lookup table for the options. Note that access mnakes it easy for a user to add extra options - if you want to allow it

so if a user wants to enter "rarely" - you can either design the form so that a new entry is rejected, or permitted.

the data structure is always paramount, but a logical structure also leads to a user-friendly interface, which is most important.

 

[Ani]

If you haven't read this, you should.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

It should help

The attached pdf is much easier to understand! Have picked up a few things so far that will help! One being its possible to put more than one foreign key into the tables!
Slowly but surely its beginning to make sense!
Thank you again!
Ani

 

[Ani]

a lot of this depends on how you want to use the database


the data structure is always paramount, but a logical structure also leads to a user-friendly interface, which is most important.

Thanks! Im going to be the only 'user' but the idea of putting everything from paper onto the db is to make things easier and more efficient for myself so I will treat everything as if someone who has little knowledge of what I do can interface with the db itself. Hopefully that way I should keep things simple to use.
Ani
PS There is a possibility that the information I collect, if collected correctly, over a few years, could be used to write a scientific paper. But thats a bit of a pipe dream for me at the moment!

 

[gemma-the-husky]

i think if it is only for your self you can ease off on some defensive programming, guarding against daft usage

good interface is still important though. you still do not want to be working in tables directly, even if you are the only user.

 

[Ani]

Thanks Dave...no I couldnt work in the tables anyway. I dislike working with spreadsheet or listed information, Im afraid my brain doesnt work in the correct way to work with linear stuff! I have a rather untidy way of working things out and tend to think in abstract ways, which is why the db is going to be difficult for me to complete! Good exercise for the old grey matter though!
I think I have my tables as normalised as I can make them. What do I do next? Forms or queries? Is it a matter of choice or is there a logical reason for choosing to do one or another?
Cheers
Ani