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

Answer 1

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...

Answer 2

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

Answer 3

Hi Nath,

Not sure I'll be able to understand without seeing your db. Are you able to share it?

Answer 4

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

Answer 5

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...

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

Yes, this has worked Ken,

It was all due to the IN operator and subquery in the query's WHERE clause.

Thanks

---

Nath