Query results need to be summarised (on a one to may relationship)

[OrionElectrotech]

I have a query where users can enter criteria in one form to then display a list of results in another form (continuous format). The query pulls data from 2 tables, one a list of candidates, the other a list of their skills. One candidate can have numerous skills.

When the results are displayed, a candidate who has, for example, 2 skills, will appear twice on the results list (as expected).

Is there any way however that I can get it to only display each candidate once, with maybe a list box displaying all their relevant skills?

Caroline

 

[fneily]

Read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You should have three tables. First, you do not have a one-to-many relationship. One candidate can have MANY skills. One skill can belong to MANY candidates. You have a many-to-many relationship, which relational database such as Access don't like. So you'll build a junction table. Examples:
tblCandidates
CandidatesID Primany Key
other candidate info fields

tblSkills
SkillID Primary Key
other skill info fields

tblCansSkills
CansSkillsID Primary Key
CandidatesID Foreign Key
SkillID Foreign Key

Now connecting the tables in a query, you can pick off any candidate and their skills. Or show a skill and all the candidates that have it. etc.
As an aside, Access takes alot of studing first before building a database.

 

[OrionElectrotech]

Thanks for your reply... I think.
Felt more like I was being told off.

(I do actually have 3 tables, and I thought each had a one-to-many relationship, however I'm probably wrong)

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OrionElectrotech]

Thanks PHV,

Couldn't get this to work - kept getting a Compile Error Expected: Case. Something to do with SQL and VBA?

Will keep trying,

Caroline

 

[SeeThru]

For an easier way to do this, consider using reporting. If suitable, you can have a very pretty list with the name and list of skills, using 'Group Headers' and detail.

Only any use if you only want to view the data.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[OrionElectrotech]

Yeah, thought about that, but we need a link to the full data from the results page, so sadly a report is no good.

Thanks anyway.

Caroline

 

[SeeThru]

PHV's answer is the easiest way to go then.

Basically, for each record in the 'one' you locate all the related records in the 'many', grab the relevant bits of info and return them.

simple example
Tables are 'Contacts' and 'Calls'

public funtion GetCallNotes(intcontactID as integer)
dim dbs as database, rst as recordset
dim strCombinedNote as string

set dbs=currentdb
set rst=dbs.openrecordset ("SELECT * FROM CALLS WHERE ContactID=" & intcontactid
do while rst.eof=false
  strCombinedNote =strCombinedNote & rst!note & ";"
  rst.movenext
loop
GetCallNotes=strCombinedNote

Then in a query, form or whatever, set the controls source to GetCallNotes([contactID])

This is a crude example - you can add multiple bits of data for each record on the many side, format it, whatever, but hopefully this gives you a start.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[OrionElectrotech]

Thank you - I shall try your suggestion and get back to you,

Caroline

 

[jamaarneen]

Hi,

sorry if I'm misunderstanding, but isn't it a main form (for the candidate information) and a subform (for their skills) what you need?

 

[OrionElectrotech]

I already have a form/subform that works like that (Candidate Info), and is fine, displaying data from the Candidates and Skills tables.

This is a seperate query, based on the same tables, but showing a summarised list (continuous form) of suitable candidates and their skills. They can then go, from that summary, to the main Candidate Info form.

 

[OrionElectrotech]

I can't get PHV (10July) or SeeThru (15 July) answers to work unfortunately - again down to my lack of VBA knowledge I guess.

Does anyone have any other ideas, or are you able to expand on them?

Caroline

 

[PHV]

What is your code and where are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Many-to-many relationship. Junction table. Would have been fixed on July 10th. Don't follow the protocols of Normalization and time is wasted. Good example for my classes.