removing duplicates from output of my query?

[sempuritoza]

greetings,

i have a bit of a problem with a query that i am qorking on, if the explanation is bad i do apologise but it is rather tricky to explain but to put it into a nutshell i have a query that is joining 4 tables and 1 of the tables contains perosnel information i.e. Name, Surname, etc this is the table with the Primary Key "ID_Number"(auto number)

This table is joined to the tables Contact Details, SchoolOrInstitutions(SOI) and SkillsMatrix(SM). A single person can have many SOI entries and many SM entries and the problem is that when i want to pull out details from all the tables for a single person i get a record back for each SOI entry thus if a person has 6 qualifications i get the same persons record back 6 times. I tried using the GROUP BY with the field ID_Number but this doesnt work how can i get a single record back for each person?

This has been bothering me for a while now and i geuss the answer might be wasy seeing that i am new to access but all help would be appreciated, maybe it would help if i also posted the query in the post but it is really long so im only gonna do that if i really have to...

thanx lata

 

[WildHare]

You'll never be able to get back "one" record from each person, if that person has MANY Skill entries - it's just not the way a one-to-many database works.

The question to answer is, do you want one "wide" record, with each person's SKILLS, let's say, spread ACROSS the page into as many columns as he or she has skills, or do you want ONE particular SKILL record for each person, like the most recent one, based on sorting the set of SKILLS by date, or something? This would involve applying a function like FIRST or MAX to the skill set in a Group-By query, to return ONE (skill) record per person.

Do you see the difference in orientation? Post back and we'll see what we can come up with.

Jim



If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[sempuritoza]

tahnx for the reply im gonna read through it thouroughly and then work with it a bit and as soon as i have a result i will let u know...

much appreciated

 

[Eupher]

WildHare is absolutely right, that's just the way a one-to-many relationship works. However, there are ways to set up a form or report so the info is grouped by whatever criteria you want, in this case the person's name. You might try building a form and binding it to the personal info table. Then you could place a listbox on the form and set the listbox's Row Source Type to "Table/Query" and the Row Source to something like: "SELECT * FROM SkillsMatrix WHERE ID_Number = [Forms]![frmMyFormName]![ID_Number]" - you can use the query grid to set the fields and criteria for the listbox.

You could also do a similar thing using a subform.

If you want (and aren't put off by some possibly heavy duty coding) there are ActiveX controls out there that can display this sort of grouped info much in the same way files/folder tree views can be expanded or contracted.

HTH...

Ken S.