Previous icon

Next icon

Common duplication problems

Example 1

It is possible to have duplicate names. For example, if you do not select StudentContactType as SC1, you only pick up the primary contacts and not Term and Alternate as well. Otherwise, you would be returning one line for every contact that a student has.

Query with duplicate records

SELECT ID, StudentContactMailName, StudentContactAddressComma, StudentContactType

FROM vStudentContactAddress

WHERE CurrentSemesterOnlyFlag = 1

Returns

ID

ContactMailName

ContactAddress

ContactType

364

Mr Joe Black

15 Green St, MT WAVERLEY VIC 3149

SC1

364

Mrs Jane Black

341 Flowerdale Ave, St Kilda VIC 3182

SC2

Query with correction

SELECT ID, StudentContactMailName, StudentContactAddressComma, StudentContactType

FROM vStudentContactAddress, StudentContact

WHERE CurrentSemesterOnlyFlag = 1 AND NormalMailFlag = 1

Returns

ID

ContactMailName

ContactAddress

ContactType

364

Mr Joe Black

15 Green St, MT WAVERLEY VIC 3149

SC1

Example 2

Another cause of duplication in a student contact query comes from the selection of any student fields.

For example, you want to mail out one letter to the parents of students, regardless of whether they have multiple siblings at the school. You also decide that you want to sort by YearLevel so you use this field as well.

To adjust the query to avoid duplications because siblings may be in different year levels, you need to add the:

Query with correction

SELECT DISTINCT StudentContactMailName, StudentContactAddressComma, YearLevel

FROM vStudentContactAddress

WHERE StudentContactNormalMailFlag = 1 AND

CurrentSemesterOnlyFlag = 1

Returns

ContactMailName

ContactAddress

YearLevel

Mr Joe Black

15 Green St, MT WAVERLEY VIC 3149

5

Mr Joe Black

15 Green St, MT WAVERLEY VIC 3149

10

Last modified: 26/05/2017 5:13:59 PM

See Also

Extracting data from the Synergetic database

What's new to Extracting data

Synergetic views

Extracting data using the Microsoft Query wizard

Editing a Microsoft Query using the wizard

Inserting an existing SQL statement

Extracting data using Microsoft Query directly

Advanced Microsoft Query and SQL examples

Extracting data for Crystal Reports

Using stored procedures to retrieve Synergetic data

© 2017 Synergetic Management Systems. Published 20 July 2017.

Top of page