Multiple Choice Advice 1

[Wizzweb]

Hi

Being fairly new to Access, I wonder if anyone can give me some advice, tips or instructions on the DB I am involved with at the moment.

I am building a simple agent/employee type database consisting of the usual stuff ....... name address etc, but I also want to inculde a muliple choice for skills for each employee and wonder how to go about it, where the user can select one, two or even all the skills. I have created a Table for the skills, at present only 5, but may be increased to 20 or more. No doubt there IS someone who can help.

TIA

Ken
wizzweb@aol.com

 

[jrbarnett]

Create a link table with a composite primary key of employee ID and Skill ID, being foreign keys from each of the tables.
That way, each employee can have from none to every skill available in your list.

John

 

[Wizzweb]

Thanks for the info John ... have to try and sort it all out in my head .... Can you explain what Link Table and Composite Primary Key are? As I am still new to Access, it is a foreign language to me. Too used to HTML and Java and such things.

TIA

 

[jrbarnett]

OK

Employee table:
EmployeeID - primary key
StartDate
FirstName
Lastname

etc fields

Skills table
SkillID - primary key
Description

Employee_Skills
EmployeeID
SkillID

Primary key: field containing value unique to that record for identification purposes.
Composite primary key: primary key made up of more than one field.

If you are new to relational database design skills, I can highly recommend the "Fundamentals of Relational database design" document available from the Programmers section of

http://www.abcdataworks.com/

- this site is run by TT member JeremyNYC.

John

 

[Wizzweb]

Hi John

Trying to get used to this Forum too!!!

Thanks for the tip .. I have saved the message and will try it out later

Will let you know how i get on

 

[Wizzweb]

Hi all

I have solved my problem in one of the ways suggested, both in this forum and others.

The way I did it wa in a way that I thought was rather long winded, but appears to be working a treat. I created a Table: tblSkills and a field for each skill and set the data type for each field to Yes/No.

When I created the Form, this presented itself with my own questionaire Form - frmSkills.

Then I made this form as a SubForm on the main Form - Employee form.

All seems to work well at the moment.

My next step will be to create a search facility. I am also trying to remember how I placed the record numbers into the form instead of being across the bottom of the window.

Sorry if this seems very basic to all the techies that use this Board, but I look at it that there must be several others at the same low level of Access knowlege and experience as I.

Hopefully what I have done and explained, will be of use to some other Newbie.

Ken

 

[RoyVidar]

Hi!

To get number of the current record, a text control with the controlsource of
[tt]=currentrecord[/tt]

should do.

To find the number of records on the form, some versions:

In a text controls controlsource
[tt]=Count(*)[/tt]

Alternatively, you could use the recordsetclone in the on current event of the form:

[tt]Me!txtRecCount.Value = Me.recordsetclone.recordcount[/tt]

Roy-Vidar

 

[Wizzweb]

Roy,

Well what can I say? You are a hero. I am sure the first two parts are the same as I used in the past.

Many, many thanks.

Ken

 

[DjinnRutger]

So did you figure out how to do the Search? I am working on a simaler project that i need to do a search on a form where there will be mulitpul options that people could have and i want to be able to do a search looking for people with specific options.

please let me know, thanks!

 

[Wizzweb]

HiDj

Well, the only way I got around it, was to let the user typr in the Skill because it more flexible to let the user enter what skill the trainer is able to do, rather than select from a list, there being more skills later.

I haven't figured out and AND/OR search for a Town AND/OR skill.

Ken