Dupes occuring in query results (1 Viewer)

BennyLinton · Mar 16, 2018

I have a table called dbo_Certificants that has 247,000 records. If I do a join to a table called dbo_Education I get back 251,000 which is because some people graduated from two schools. This is going to be a problem when I try to use the query as my datasource for a form. Any ideas? Thanks!!

SELECT dbo_Certificants.id, dbo_Certificants.name, dbo_Certificants.firstName, dbo_Certificants.lastName, dbo_Education.degree
FROM dbo_Education RIGHT JOIN dbo_Certificants ON dbo_Education.id = dbo_Certificants.id;

Ranman256 · Mar 16, 2018

Only show the fields that dont repeat,
say just: name

set the query property UNIQUE VALUES = TRUE

you can continue adding fields...if it repeats, then you hit 2nd school property and will get a 'duplicate' name.

BennyLinton · Mar 16, 2018

What if I want my form to show one unique person but both schools attended?

arnelgp · Mar 16, 2018

We can make the query but it wont be uodateable

BennyLinton · Mar 16, 2018

This is a read-only form. Will I have problems displaying the individual with two schools shown? If I have a search tool on the form that looks up the id # of a person will I get two people returned?

arnelgp · Mar 16, 2018

this will be your query or something like this:

SELECT dbo_certificants.id, dbo_certificants.name, dbo_certificants.firstname, dbo_certificants.lastname, (SELECT TOP 1 degree FROM dbo_education AS T1 WHERE T1.ID = dbo_certificants.id ORDER BY T1.schoolName ASC) As Degree, (SELECT TOP 1 schoolName FROM dbo_education AS T1 WHERE T1.ID = dbo_certificants.id ORDER BY T1.schoolName ASC) As School1, (SELECT TOP 1 schoolName FROM dbo_education AS T1 WHERE T1.ID = dbo_certificants.id ORDER BY T1.schoolName DESC) As School2 From dbo_certificants

Mark_ · Mar 16, 2018

BennyLinton said:
What if I want my form to show one unique person but both schools attended?

So you are asking "How do I get TWO entries in my query for ONE person who has attended TWO schools"?

It seems like your query is already doing that. If not, how do you want "Both schools attended" returned?

Pat Hartman · Mar 16, 2018

Rather than joining the tables, create two forms. A main form for the student information and a subform for the schools. Alternatively, you can use a Concatenate function (search here) to get the schools for an individual all concatenated into a single text string.

Dupes occuring in query results (1 Viewer)

BennyLinton

Registered User.

Ranman256

Well-known member

BennyLinton

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

BennyLinton

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread