Searching Table (1 Viewer)

[Robster]

I have a table with 200 users and need to record their qualifications, some will have more than 1 qualification.
Should I make them individual fields eg Field - BA, MSc etc or just have Qualification1, Qualification2, Qualification3, Qualification4 and look up to another table?
I will need to be able to create a search so that i can select all users with a particular qualification or all with 1 qualification plus those with a different qualification.
I've played around with it but cant seem to get anywhere.

 

[Cotty42]

Surely you would be better having two tables - User Table with your user information and qualifications table with details of their qualifications, linked by User ID, so for each user record there could be multiple qualification records.

Hope this helps

Dave

 

[plog]

Whats wrong with having a single field called "qualifications" and let or restrict the user to BA, MSc?

Because that goes against normalization.

some will have more than 1 qualification.

You need a Qualifications table. You would store the autonumber primary key from Users (it should have one like that) and then the qualification the user has. It would look like this:

Qualifications
UserID, Qualification
17, MBA
17, MSc
18, MBA
19, PhD
25, BA
25, CPA

 

[Robster]

Thanks for you responses.

I will need to be able to search on qualification and return the users with that qualification.

Eg. a form where you can select to return all users with Msc.
But also be able to return all users with Msc and Bsc etc.

There are a total of 18 different qualifications so will need to be able to return all the possible variations of qualifications.

 

[Cotty42]

Hence the need for two tables, that way you are only searching one field for the qualification 'MSc' for example. Otherwise you will potentially have to have 18 qualification fields in your single table which you will need to search through.

Running a simple query across your linked tables will easily return the data you are looking for.

Dave

 

[Robster]

Thanks Dave.
But what happens if user1 has Msc and user2 has Msc and BA.
If i do a search for Msc and BA wont it return 3 records?

 

[Cotty42]

No because your query criteria will restrict the records returned to 'MSc'. You can set up the criteria so that you will be prompted to enter the query criteria each time you run the query so you dont need to have a separate query for each qualification. Alternatively you could select it in a combobox etc. depending on how simple you want to make your query.

Dave

 

[Robster]

thanks.
Just to clarify I have users listed below and want to return all user details for the who have qualifications of Msc or BA. Would this work then?

UserId, Qualification
1, BA
1, Msc
1, PHd
2, Ba
2, Bsc
3, Phd
3, Msc

Would it return 4 records or 3?

 

[plog]

It depends on what "it" is. I can build a query that could return 3 or 4. Which do you prefer? Give it a shot and post back here any issues you have.

Hint: to get to 3 records you use an aggregate query ( click the Summation/Sigma in the ribbon)

 

[Robster]

Thanks.
Created a qualifications table and it's working.

 

[Robster]

Ok. Still struggling.
I'm trying to set up the form to log new users and their qualifications. I have a main table with UserID which references the Qual table which lists the userID's and the qualification.
The qualifications are selected from a listbox control which gets it's values from the qualifications table.
I'd like to be able to select values from the List box and enter them into the 'normalised' Qual table.

 

[plog]

When you have a 1-many relationship, you use a form/sub-form for data entry. The main form would be based on the 1 table (Users) and the sub-form should be based on the many (Qualifications).

The main form would let you edit the User data (name, phone, etc.) the sub-form would simply be a continous form with each line containing your drop down.

 

[Robster]

Sorry, dont follow. I understand the prinicple of the 1 to many but how do I add it to the subform.
I wanted the form (or subform) to be able to select multiple entries from a list control. It will take the qualification from table (c) and insert it into the Qual table (b) with the StaffID from the user table (a).

 

[plog]

Build a form using your Qualifications table as its source, put your drop down on it, make it a Continous Form. Then build a form based on Users, add controls for whatever fields are in Users, make it a Single Form. On the Users form add the Qualifications form as a sub-form, linking them by the UserID.

 

[Robster]

Ok but the Qualifications are a lookup (list control) from another table.

 

[plog]

I'm guessing what you are saying is that they are stored as a number in your table, but you want the words to appear in the drop down? Correct?

If so, not a problem. The items in a drop down can show any datasource. That means you can show the name and store the numeric value. You set the drop down's datasource to a query based on the appropriate datasource, the first field would be the number you store, the second field is what you want displayed.

 

[Robster]

ok, getting somewhere.

I did what you said earlier.I created the forms and and just typed in the list values. I made the list multi select.
Then saved the qualsubform with the list control on it.

Then on the new staff page I dragged the subform onto it.
Saved and ran. Entered test data which is stored in the Staff table but the qualifications table just has the userid and no value in the text field.

 

[Robster]

should also say that I selected more than 1 entry but the userid was only in the qualification table once.

 

[plog]

I wouldn't use a multi-list select whatever for this. You should use a continious subform with a drop down. If you want to allocate multiple qualifications to a person, you keep adding them in that subform.

Here's a microsoft article on it:

http://office.microsoft.com/en-us/a...a-subform-a-one-to-many-form-HA010098674.aspx

 

[Robster]

That works brilliantly!

small point, if i want to do a query\report listing a user and their qualifications. how do i stop the user details being listed for each qualification?