Query Join Properties

[silverstriip]

Query Join Properties

I have 2 tables. one (Data_Participa nt) has all of the data on individual participants. the other (Data_Main) has the data of how they participated per year. my Data_Participan t table has a primary key (ParticipantID) which does not allow duplicates and the Data_Main table is setup w/ a non-primary key field (ID) which will allow dupes. these two fields are joined in my query with the Data_Participan t.ParticipantID set to include all records and and only those records from the Data_Main.ID field where the records are equal.

to me, the join properties being setup like this would allow me to put any type of search criteria i would like under the Data_Main table fields and i would still get all of the Data_Participan t's tables as a result, but only those from Data_Main that i had filtered.

ultimately, i'd like to see all of my Data_Participan t data and only the Data_Main data that makes it through my filter/search criteria. am i not understanding how the join properties work?

[ADezii]

Query Join Properties

I have 2 tables. one (Data_Participa nt) has all of the data on individual participants. the other (Data_Main) has the data of how they participated per year. my Data_Participan t table has a primary key (ParticipantID) which does not allow duplicates and the Data_Main table is setup w/ a non-primary key field (ID) which will allow dupes. these two fields are joined in my query with the Data_Participan t.ParticipantID set to include all records and and only those records from the Data_Main.ID field where the records are equal.

to me, the join properties being setup like this would allow me to put any type of search criteria i would like under the Data_Main table fields and i would still get all of the Data_Participan t's tables as a result, but only those from Data_Main that i had filtered.

ultimately, i'd like to see all of my Data_Participan t data and only the Data_Main data that makes it through my filter/search criteria. am i not understanding how the join properties work?

Which Field in Data_Main is your Search/Filter based on? It appears as though your need a Left Outer Join in which 'ALL' Records from Data_Participan t are shown (Option 2 as indicated), while the Criteria on the Data_Main Field will naturally restrict those values.

[silverstriip]

i'm filtering on the Data_Main.aquir edyear field. not sure how to setup the one-to-many relationship. i would like to beable to see all of the Data_Participan t information even when there are no records that make it through the filter on the Data_Main table. this way, i will be able to pull up all of my data via year (aquiredyear) and add a record if it does not yet exist from my form.

[ADezii]

i'm filtering on the Data_Main.aquir edyear field. not sure how to setup the one-to-many relationship. i would like to beable to see all of the Data_Participan t information even when there are no records that make it through the filter on the Data_Main table. this way, i will be able to pull up all of my data via year (aquiredyear) and add a record if it does not yet exist from my form.

I'm not sure if this would work in your specific case, but how about first checking to see if the Applied Filter would actually return any Records? If it does, apply the Filter, if not possibly modify the Record Source of the Form? The logic would be as follows:

'Does Filter actually return any Records?
If DCount("*", "Data_Main", "[AcquiredYear] = #mm/dd/yyyy#") > 0 Then
  Me.Filter = "[AcquiredYear] = #mm/dd/yyyy#"
  Me.FilterOn = True
Else
  Me.RecordSource = "some New Record Source"
End If