Can't get multiple records to display

motscotland

Registered User.
Local time
Today, 22:22
Joined
Oct 29, 2003
Messages
19
HELP!! Can't get multiple records to display

Hi all,
I am a relative newbie to Access development and have hit a problem I canot seem to solve!!

I have a main employees table, a qualifications table and a qual code table. They are all linked appropriately BUT I cannot find a way to display each employee along with ALL their qualifications (up to 8 per employee)!! They show fine as a list (continuous) or one after another (single form).

How can I display these as follows?

Employee Qualification
J Smith Degree
Diploma
First Aid

Is there anything I should bear in mind at the data entry stage as well - ie how to get the entry form to pass new qualifications (up to 8) to the qualifications table? The data I have is existing data which was imported to the present tables.

Thanks in advance for your help.
MoT
 
Last edited:
Create a junction table that contains the QualificationID and the EmployeeID - this simulates a many-to-many relationship as one employee can have many qualifications and one qualification acan be achieved by many employees.

Then, with your junction table you can create query using INNER JOINS to bring the proper results you want, and then place them in a subform.

To see a visual example of this then you can start by looking at my example on this thread. The example in this case is artists to artforms - same thing.
 
Thanks for your prompt reply.
I shall investigate Junction Tables after lunch and no doubt will have more questions as I have never used them before!! :eek:
Cheers the now,
MoT
:cool:
 
Oooops!! I tried the Junction Tables and now I am really in a major mess!! :confused:

The relationships are not as they should and the main table (imported from an old FoxPro db) is utterly Kn'k'rd!!!! :mad:

Am I better to have lots of small tables (small as in few fields) which are linked via queries or one big table (lots of fields) or ???

The db is of employees on contract (about 2500 peeps) and has many fields for name, address, tel no, email etc.

There are also fields for Qualifications and Discipline (skills) which can have up to 8 items each (where my original prob started!!)

The ultimate aim is to have the ability to search using up to 6 criteria (AND, OR, etc) and produce a report of suitable candidates. The slight problem is that the data exists as a Fox Pro db at present and these are the tables I am working with just now.

I have a form (FRMSEARCH) which I have modified to produce the reports I require (unfortunately only 5 criteria as yet) but still require to add comboboxes to select within criteria (1st category from cbox giving list of values to choose from in 2nd cbox) which in itself is proving a task and a half BUT until I sort this 'Junction Table' I still have only the first Qualificartion or Discipline item appearing!!! This is crucial as these two criteria are the main ones to make the selections with after location! :eek:

I would appreciate any help, pointers or offers of alternate employment (I am not a programmer but a desk bound transportation engineer with a boss who is full of great ideas!!) :D

Cheers all,
MoT
:confused:
 
Am I better to have lots of small tables (small as in few fields) which are linked via queries or one big table (lots of fields) or ???
- You are better off with properly normalized tables. Search the site for links to articles. Here's a few to get you going:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q288947&
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q234208&
http://www.fmsinc.com/tpapers/datanorm/index.html

Just a hint - if you find yourself limiting things - 8 of these, 6 of those, etc., your structure is flattened like a spreadsheet and not properly normalized. In a spreadsheet you would need to add a new column and make possibly numerous changes to your application if you suddenly found you needed 9 items. Whereas, in a properly normalized table, you just add a new row. No application changes are required.
 
Thanks for the reply and the links. Reading about normalisation now!
It looks like I need a total rethink to get this to work properly!!
Cheers the now,
MoT
:cool:
 
OK - I have done much reading about normalisation and the like.
I have downloaded and examined the dbArtists example. I couldn't really follow what it was doing at all, or how it relates to what I am trying to do.
I am not getting this to work at all!! The 'junction' table shows blank!! Not a sausage!! Zilch!!
Please, please, please, somebody help! The deadline approacheth as fast as a fast thing moving REALLY fast!! :eek:
Simple as possible please.
Cheers,
MoT
:eek:
 
motscotland said:
I have downloaded and examined Mile-O-Phile's dbArtists example. I couldn't really follow what it was doing at all, or how it relates to what I am trying to do.

It relates to what you are wanting to do because your employees can have a number of qualifications UP TO a specific number.

Now, give an example database of what you are trying to do and I'll have a look.
 

Users who are viewing this thread

Back
Top Bottom