By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Query Join Properties

P: 5
Query Join Properties

I have 2 tables. one (Data_Participant) has all of the data on individual participants. the other (Data_Main) has the data of how they participated per year. my Data_Participant 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_Participant.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_Participant'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_Participant data and only the Data_Main data that makes it through my filter/search criteria. am i not understanding how the join properties work?

Feb 8 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
Query Join Properties

I have 2 tables. one (Data_Participant) has all of the data on individual participants. the other (Data_Main) has the data of how they participated per year. my Data_Participant 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_Participant.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_Participant'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_Participant 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_Participant are shown (Option 2 as indicated), while the Criteria on the Data_Main Field will naturally restrict those values.
Feb 9 '08 #2

P: 5
i'm filtering on the Data_Main.aquiredyear field. not sure how to setup the one-to-many relationship. i would like to beable to see all of the Data_Participant 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.
Feb 11 '08 #3

ADezii
Expert 5K+
P: 8,597
i'm filtering on the Data_Main.aquiredyear field. not sure how to setup the one-to-many relationship. i would like to beable to see all of the Data_Participant 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:
Expand|Select|Wrap|Line Numbers
  1. 'Does Filter actually return any Records?
  2. If DCount("*", "Data_Main", "[AcquiredYear] = #mm/dd/yyyy#") > 0 Then
  3.   Me.Filter = "[AcquiredYear] = #mm/dd/yyyy#"
  4.   Me.FilterOn = True
  5. Else
  6.   Me.RecordSource = "some New Record Source"
  7. End If
Feb 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.