No duplicates

[SVE]

Here's my setup. I have 3 tables--Child, Adult and Track. Adult has a
1-to-many relationship with Child. Child has a 1-to-many with Track. I am
filtering my query on a field in Track called Type with the Criteria=Like
"Initial". When run, there appear multiple Adult IDs due to them having
multiple children. I want to print a report from this filtered query, but I
only want to print one letter for the adult no matter how many children they
have with the criteria "Initial". There are other fields from these tables,
but they are not used for any filtering, just to display data on the report.
I am not very experienced writing SQL, so please be specific.

Thanks for all your help.

 

[Allen Browne]

Presumably the main report is based on the Adult field only, so you only get
one record per adult.

You can use a subquery to filter the main report. The example below will
need modification and testing, but illustrates the idea. It assumes you open
the report by clicking a command button on a form, and there is a combo
named cboInitial where the user chooses the Initial:

Private Sub cmdPreview_Click()
Dim strWhere As String
If Not IsNull(Me.cboInitial) Then
strWhere = "EXISTS (SELECT AdultID " & _
"FROM Child INNER JOIN Track ON Child.ChildID = Track.TrackID " & _
"WHERE (Initial = """ & Me.cboInitial & """) AND (AdultID =
Adult.AdultID))"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Drop the extra quotes if Initial is a Number field (not a Text field.)
For an explanation of the quotes:
http://allenbrowne.com/casu-17.html

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[John W. Vinson]

Here's my setup. I have 3 tables--Child, Adult and Track. Adult has a
1-to-many relationship with Child. Child has a 1-to-many with Track. I am
filtering my query on a field in Track called Type with the Criteria=Like
"Initial". When run, there appear multiple Adult IDs due to them having
multiple children. I want to print a report from this filtered query, but I
only want to print one letter for the adult no matter how many children they
have with the criteria "Initial". There are other fields from these tables,
but they are not used for any filtering, just to display data on the report.
I am not very experienced writing SQL, so please be specific.

Thanks for all your help.

Allan's suggestion is certainly one way to go, but you may also want to handle
the repeats using the Report tools rather than the query. You can use the
Report's Sorting and Grouping dialog to group by the Adult; specify that the
Group Header (and, if desired, footer) is visible for the Adult grouping. You
may also want to sort by Child (by name, by age, whatever suits).

You can then put the address and adult information on the Group Header, and
the child information in the detail section. This will show each adult's
information only once.

If your criterion is

LIKE "Initial"

note that it's doing exactly the same thing as just using "Initial" as the
criterion. The LIKE operator recognizes wildcards (* matching any string, #
any single digit, etc.); it's pointless to use it if you're not using
wildcards.