Answered by:
How to return all records in Table A while returning all records in Table B that are linked by same ID

Question
-
How to return all records in Table A while returning all records in Table B that are linked by same ID
I have 3000 records in Table A
I have 5000 records in Table B
They both are linked by an ID field.
I want to write a query to return all records in Table A but allow a form to use the query to show one of the 3000 records in Table A and then all the details that are contained in Table B with the same ID.
I have this working with a LEFT JOIN where Table C contains 1000 records and has the same ID as Table A.
When the query runs it returns the 3000 records in Table A and shows the Table C details where they have the same ID.
However, when I try to include table B I get more than the 3000 records in Table A returned.
Nath
Wednesday, January 18, 2017 3:00 PM
Answers
-
I only need the distinct number of records in table A where the string matches table Bs records to be returned
To filter a parent form on the basis of a value in a subform's recordset, where the subform is based on a table to which the parent form's table is related in a one-to-many relationship type requires a little more than a simple restriction. It is necessary to return those rows from the parent form's recordset where the value of its primary key is in the subset of foreign key values in those rows from the subform's recordset which match the search criterion.
Firstly, forget about joins. As I said before the parent and form and subform should each be based on a single table only, not on a join of two or more tables. You then need to build a string expression which filters the parent form in the way I described above. You'll find an example in FindRecord.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.
In this little demo file the option to 'filter by a subform value' opens a form in which an unbound combo box in the form header can be used to select a value from the subform's recordset. Code in the AfterUpdate event procedure of the combo box then filters the parent form as follows:
Const MESSAGETEXT = "No matching records found."
Dim ctrl As Control
Dim strFilter As String
Set ctrl = Me.ActiveControl
strFilter = "ProjectID IN(SELECT ProjectID " & _
"FROM ContactProjects WHERE ContactID = " & ctrl & ")"
If Nz(ctrl, 0) = 0 Then
' turn off filter
Me.FilterOn = False
Else
If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
' filter form to name selected in combo box
Me.Filter = strFilter
Me.FilterOn = True
Else
' inform user if no matching records found and show all records
MsgBox MESSAGETEXT, vbInformation, "Warning"
Me.FilterOn = False
Me.Requery
End If
End If
As you can see it does this by building a subquery on the subform's table, ContactProjects, to which the IN operator is applied to filter the parent form to those rows where the primary key, ProjectID, is in the set of values returned by the subquery.
Another option in the demo filters not only the parent form, but also the subform to the selected value by extending the code as follows:
Const MESSAGETEXT = "No matching records found."
Dim ctrl As Control
Dim strFilter As String
Set ctrl = Me.ActiveControl
strFilter = "ProjectID IN(SELECT ProjectID " & _
"FROM ContactProjects WHERE ContactID = " & ctrl & ")"
If Nz(ctrl, 0) = 0 Then
' turn off filter
Me.FilterOn = False
Me.fsubContacts.Form.FilterOn = False
Else
If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
' filter form to name selected in combo box
Me.Filter = strFilter
Me.FilterOn = True
' filter subform to selected contact
Me.fsubContacts.Form.Filter = "ContactID = " & ctrl
Me.fsubContacts.Form.FilterOn = True
Else
' inform user if no matching records found and show all records
MsgBox MESSAGETEXT, vbInformation, "Warning"
Me.FilterOn = False
Me.Requery
Me.fsubContacts.Form.FilterOn = False
End If
End If
Ken Sheridan, Stafford, England
- Edited by Ken Sheridan Thursday, January 19, 2017 12:01 PM Hyperlink inserted.
- Marked as answer by NaPazz Thursday, January 19, 2017 9:14 PM
Thursday, January 19, 2017 11:59 AM
All replies
-
Hi Nath,
Have you considered using subforms in your form? The main form should be the parent table and the subforms will contain the child tables.
Hope it helps...
Wednesday, January 18, 2017 3:10 PM -
Hi.
I have subforms on tabs.
The problem seems to be that Table B has more records than Table A, so when the form uses the query it returns over the 3000.
However, I need the subform to be able to see the details in Table B that correspond to the same ID in Table A.
If I use a DISTINCT in the query on Table A, can I still be able to see all of the corresponding entries in Table B in my subform.
Nath
Wednesday, January 18, 2017 3:29 PM -
Hi Nath,
Not sure I'll be able to understand without seeing your db. Are you able to share it?
Wednesday, January 18, 2017 4:37 PM -
Unfortunately not.
I'm probably missing something simple.
I think I might just need another query returning a distinct list of Table A for the recordset.
My doubt with this is, will all of the additional records for the entry in Table A be available still.
Nath
Wednesday, January 18, 2017 8:56 PM -
Hi Nath,
Typically, a form is based on only one data source. Related data sources are then placed in subforms. Unless, if your form is just for displaying all the related data, then basing it on multiple data sources is okay. One reason for it, using a LEFT JOIN or DISTINCT will make the data "read-only."
Just my 2 cents...
Thursday, January 19, 2017 12:01 AM -
The parent form's RecordsSource should select rows from Table A only. Neither Table B or Table C should be included in the query, i.e. the query should be along these lines:
SELECT *
FROM [Table A]
ORDER BY [SomeColumn];
The two subforms should similarly be based on queries which select rows from Table B only, and Table C only respectively. Each subform should be linked to the parent form by setting the LinkMasterFields and LinkChildFields to ID in each case. As you navigate from record to record in the parent form the subforms will show the related rows from Table B and Table C.Ken Sheridan, Stafford, England
- Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Thursday, January 19, 2017 1:27 AM
Thursday, January 19, 2017 12:08 AM -
Hi Ken,
I have my form and subforms set out as you describe.
I am doing a search of table B for a string. I then want the records in table A where the string exists to be returned.
When table B has fewer records than table A, a LEFT JOIN query returns the correct number of records for table A and fills in with blanks, correctly.
When table B has more records than table B, a duplicate record for each entry in table B is returned. which isnt what I require.
I only need the distinct number of records in table A where the string matches table Bs records to be returned
I have tried to do this with a GROUP BY or another query returning DISTINCT, but haven't had any joy.
Nath
Thursday, January 19, 2017 10:35 AM -
Hi Ken,
I have my form and subforms set out as you describe.
I am doing a search of table B for a string. I then want the records in table A where the string exists to be returned.
When table B has fewer records than table A, a LEFT JOIN query returns the correct number of records for table A and fills in with blanks, correctly.
When table B has more records than table A, a duplicate record for each entry in table B is returned. which isnt what I require.
I only need the distinct number of records in table A where the string matches table Bs records to be returned
I have tried to do this with a GROUP BY or another query returning DISTINCT, but haven't had any joy.
Nath
Thursday, January 19, 2017 11:05 AM -
I only need the distinct number of records in table A where the string matches table Bs records to be returned
To filter a parent form on the basis of a value in a subform's recordset, where the subform is based on a table to which the parent form's table is related in a one-to-many relationship type requires a little more than a simple restriction. It is necessary to return those rows from the parent form's recordset where the value of its primary key is in the subset of foreign key values in those rows from the subform's recordset which match the search criterion.
Firstly, forget about joins. As I said before the parent and form and subform should each be based on a single table only, not on a join of two or more tables. You then need to build a string expression which filters the parent form in the way I described above. You'll find an example in FindRecord.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.
In this little demo file the option to 'filter by a subform value' opens a form in which an unbound combo box in the form header can be used to select a value from the subform's recordset. Code in the AfterUpdate event procedure of the combo box then filters the parent form as follows:
Const MESSAGETEXT = "No matching records found."
Dim ctrl As Control
Dim strFilter As String
Set ctrl = Me.ActiveControl
strFilter = "ProjectID IN(SELECT ProjectID " & _
"FROM ContactProjects WHERE ContactID = " & ctrl & ")"
If Nz(ctrl, 0) = 0 Then
' turn off filter
Me.FilterOn = False
Else
If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
' filter form to name selected in combo box
Me.Filter = strFilter
Me.FilterOn = True
Else
' inform user if no matching records found and show all records
MsgBox MESSAGETEXT, vbInformation, "Warning"
Me.FilterOn = False
Me.Requery
End If
End If
As you can see it does this by building a subquery on the subform's table, ContactProjects, to which the IN operator is applied to filter the parent form to those rows where the primary key, ProjectID, is in the set of values returned by the subquery.
Another option in the demo filters not only the parent form, but also the subform to the selected value by extending the code as follows:
Const MESSAGETEXT = "No matching records found."
Dim ctrl As Control
Dim strFilter As String
Set ctrl = Me.ActiveControl
strFilter = "ProjectID IN(SELECT ProjectID " & _
"FROM ContactProjects WHERE ContactID = " & ctrl & ")"
If Nz(ctrl, 0) = 0 Then
' turn off filter
Me.FilterOn = False
Me.fsubContacts.Form.FilterOn = False
Else
If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
' filter form to name selected in combo box
Me.Filter = strFilter
Me.FilterOn = True
' filter subform to selected contact
Me.fsubContacts.Form.Filter = "ContactID = " & ctrl
Me.fsubContacts.Form.FilterOn = True
Else
' inform user if no matching records found and show all records
MsgBox MESSAGETEXT, vbInformation, "Warning"
Me.FilterOn = False
Me.Requery
Me.fsubContacts.Form.FilterOn = False
End If
End If
Ken Sheridan, Stafford, England
- Edited by Ken Sheridan Thursday, January 19, 2017 12:01 PM Hyperlink inserted.
- Marked as answer by NaPazz Thursday, January 19, 2017 9:14 PM
Thursday, January 19, 2017 11:59 AM -
Thank you Ken.
That is a brilliant solution.
I think it will definitely work for me.
I have a complex dynamically constructed WHERE clause taking multiple selections to filter the Table by, so I'll need to get this to work as a Filter rather than changing the form's recordsource as it does at present.
I havent used Filters much yet, but perhaps it will work as is? I'll try it tomorrow and let you know.
Cheers.
Nath
Nath
Thursday, January 19, 2017 9:19 PM -
If you wished, you could still restrict the parent form by changing its RecordSource rather than filtering the form. You'd need include the IN operator and subquery in the query's WHERE clause. You could even to the same with the subform's RecordSource. On the whole, though, I think filtering might be simpler. If the parent form is already restricted by changes made to its RecordSource the filter will be supplementary to those restrictions.
Ken Sheridan, Stafford, England
Thursday, January 19, 2017 10:48 PM -
Yes, this has worked Ken,
It was all due to the IN operator and subquery in the query's WHERE clause.
Thanks
Nath
Friday, January 20, 2017 3:59 PM