Lookups for Forms

[Guest]

Hi,

I've had help recently (and hope to still have) from John Vinson (see post
send earlier today), but have another general question.

In my db, I have a student table, a course table and a table that contains
course details per student.

In the form I'm using to input and view the data, I include the Student
table for the student first and family name, and I also include the Course
Details per Student table to input the course name(s) they are taking, the
date they registered, the registration fee, etc.

My question is:

Do I make the Course Details per Student CourseName field a lookup of the
Course table so that when I use the form, I can just click on the down arrow
and select one of the course names?

I'm obviously a little unclear about relationships (which I've established)
and lookups to populate forms.

Any assistance would be greatly appreciated!


thanks

 

[tina]

do *not* make the CourseName field a Lookup field in the Details table. big
no-no. in the *form*, you can easily use a combo box bound to the CourseName
field, with the RowSource pulling the info you need from the Course table.
that's a standard use of a supporting (look-up) table. if you've never used
a combo box in a form, see Access Help for details, and post to the
newsgroups if you have a question or get stuck.

hth

 

[Guest]

Hi Tina,

Thanks so much for your help. I'm not clear on why lookups are a no-no, but
followed your advice and it worked like a charm.

I guess I'm having trouble grasping the concept of a relational database. I
created a form that used the combo box, though and it certainly worked fine.
That is for data input and for viewing data. I will need to create queries
as well that I can base reports on. I guess when I get to that phase, I will
need more help!

Thanks!

do *not* make the CourseName field a Lookup field in the Details table. big
no-no. in the *form*, you can easily use a combo box bound to the CourseName
field, with the RowSource pulling the info you need from the Course table.
that's a standard use of a supporting (look-up) table. if you've never used
a combo box in a form, see Access Help for details, and post to the
newsgroups if you have a question or get stuck.

hth

 

[tina]

see the link below for the low down on DLookup fields in tables. you can
trust any info you get at this website (mvps.org) to be correct. also, i can
tell you from sad and sorry prior experience what a nightmare lookup fields
are - i wish somebody had warned *me* when i was starting out!

http://www.mvps.org/access/lookupfields.htm

I guess I'm having trouble grasping the concept of a relational database.

the foundation of any relational database is the tables/relationships.
design those correctly, and all else flows naturally from them. design them
poorly, and developing the rest of the database quickly becomes a miserable,
frustrating experience of convoluted work-arounds and patches - resulting in
a poorly-performing, hard-to-maintain-and-enhance database.

understanding table normalization and relationships can be tough at first,
especially if you have no background in it (as i did not, when i started). i
really urge you to invest the time to learn, though, it will repay you a
thousand-fold in the long run. you can google "table normalization" and come
up with lots of informative sites. and i can recommend the textbook used in
the data modeling class i took: Database Design for Mere Mortals by Michael
Hernandez. a good reference book that will help you with queries and reports
(and everything else you need to build a good functional database) is
Microsoft Access <specific version> Bible by Prague and Irwin.

also, take a look at

http://www.mvps.org/access/tencommandments.htm
http://www.mvps.org/access/general/gen0012.htm

for some good design tips.

good luck, have fun, and come on back to the newsgroups when you need a
hand - there's always someone here to help.

Hi Tina,

Thanks so much for your help. I'm not clear on why lookups are a no-no, but
followed your advice and it worked like a charm.

I guess I'm having trouble grasping the concept of a relational database. I
created a form that used the combo box, though and it certainly worked fine.
That is for data input and for viewing data. I will need to create queries
as well that I can base reports on. I guess when I get to that phase, I will
need more help!

Thanks!