Display results from 2 tables including empty values

[vavc1980]

Hi!
I have a problem with a query, I need to put together into one table the contents of 2 tables, but in the correspondent row for each match, and include the no-matches too.

Table A:
User | IncomingCalls|
---------|-----------------------|
smith | ___10_______ |
jones | ____5_______ |
lee __| ____8 _______ |
harp _| ____2 _______ |

Table B:
User | OutgoingCalls|
---------|-----------------------|
taylor | _____15 _____ |
jones | ______6______ |
lee __| ______1 ______ |

And the result table or view that I want is:

Results Table:
User | IncomingCalls | OutgoingCalls|
---------|---------------------|----------------------|
smith | ____10_____ | ____________|
jones | _____5_____ | ______6_____ |
lee __| _____8_____ | ______1_____ |
harp _| _____2_____ | ____________|
taylor | ___________ | ______15____ |

How can I accomplish this?
I appreciate the help..

[ADezii]

Hi!
I have a problem with a query, I need to put together into one table the contents of 2 tables, but in the correspondent row for each match, and include the no-matches too.

Table A:
User | IncomingCalls|
---------|-----------------------|
smith | ___10_______ |
jones | ____5_______ |
lee __| ____8 _______ |
harp _| ____2 _______ |

Table B:
User | OutgoingCalls|
---------|-----------------------|
taylor | _____15 _____ |
jones | ______6______ |
lee __| ______1 ______ |

And the result table or view that I want is:

Results Table:
User | IncomingCalls | OutgoingCalls|
---------|---------------------|----------------------|
smith | ____10_____ | ____________|
jones | _____5_____ | ______6_____ |
lee __| _____8_____ | ______1_____ |
harp _| _____2_____ | ____________|
taylor | ___________ | ______15____ |

How can I accomplish this?
I appreciate the help..

1. Create a Table named tblResults with the following Fields
   1. User [TEXT]
   2. IncomingCalls [LONG]
   3. OutGoingCalls [LONG]
2. I'm assuming User Names are Unique and spelled correctly if they exist in both the Incoming and OutGoing Calls Tables.
3. I've made the Test Database for this Thread available to you, it is a lot easier then trying to figure out what is going on.
4. Do not rely 100% on this solution, the SQL guys will probably have a better one!
5. The entire code block is listed below, but, again download the Attachment:
   

   Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
   Dim rstResults As DAO.Recordset
    
   'DELETE any existing Records from tblResults
   DoCmd.SetWarnings False
     DoCmd.RunSQL "Delete * From tblResults;"
    
   'Add [User] and [IncomingCalls] to tblResults
   strSQL = "INSERT INTO tblResults ( [User], IncomingCalls ) SELECT [tblIncomingCalls].[User], " & _
            "[tblIncomingCalls].[IncomingCalls] FROM tblIncomingCalls;"
   DoCmd.RunSQL strSQL
    
   DoCmd.SetWarnings True
    
   'Create a Recordset based on tblOutgoingCalls
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset("tblOutGoingCalls", dbOpenForwardOnly)
    
   'Create a Recordset based on tblResults
   Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    
   Do While Not MyRS.EOF
     'Does the User already exist? If not then Add to tblResults, but if he/she does exist
     'then Update tblResults with the OutGoingCalls values
     If DCount("*", "tblResults", "[User] = '" & MyRS![User] & "'") = 0 Then
       rstResults.AddNew
         rstResults![User] = MyRS![User]
         rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
       rstResults.Update
     Else
       'Find the right User
       rstResults.FindFirst "[User] = '" & MyRS![User] & "'"
       rstResults.Edit
         rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
       rstResults.Update
     End If
     MyRS.MoveNext
   Loop
    
   MyRS.Close: Set MyRS = Nothing
   rstResults.Close: Set rstResults = Nothing

[vavc1980]

1. Create a Table named tblResults with the following Fields
   1. User [TEXT]
   2. IncomingCalls [LONG]
   3. OutGoingCalls [LONG]
2. I'm assuming User Names are Unique and spelled correctly if they exist in both the Incoming and OutGoing Calls Tables.
3. I've made the Test Database for this Thread available to you, it is a lot easier then trying to figure out what is going on.
4. Do not rely 100% on this solution, the SQL guys will probably have a better one!
5. The entire code block is listed below, but, again download the Attachment:
   

   Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
   Dim rstResults As DAO.Recordset
    
   'DELETE any existing Records from tblResults
   DoCmd.SetWarnings False
     DoCmd.RunSQL "Delete * From tblResults;"
    
   'Add [User] and [IncomingCalls] to tblResults
   strSQL = "INSERT INTO tblResults ( [User], IncomingCalls ) SELECT [tblIncomingCalls].[User], " & _
            "[tblIncomingCalls].[IncomingCalls] FROM tblIncomingCalls;"
   DoCmd.RunSQL strSQL
    
   DoCmd.SetWarnings True
    
   'Create a Recordset based on tblOutgoingCalls
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset("tblOutGoingCalls", dbOpenForwardOnly)
    
   'Create a Recordset based on tblResults
   Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    
   Do While Not MyRS.EOF
     'Does the User already exist? If not then Add to tblResults, but if he/she does exist
     'then Update tblResults with the OutGoingCalls values
     If DCount("*", "tblResults", "[User] = '" & MyRS![User] & "'") = 0 Then
       rstResults.AddNew
         rstResults![User] = MyRS![User]
         rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
       rstResults.Update
     Else
       'Find the right User
       rstResults.FindFirst "[User] = '" & MyRS![User] & "'"
       rstResults.Edit
         rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
       rstResults.Update
     End If
     MyRS.MoveNext
   Loop
    
   MyRS.Close: Set MyRS = Nothing
   rstResults.Close: Set rstResults = Nothing

Thanks! that worked!

[ADezii]

Thanks! that worked!

You are quite welcome but like I previously stated, there may be a better, SQL based approach, so I would periodically check back. Take care and good luck.

[NeoPa]

This is quite fiddly to do in Access Jet SQL. I think in Transact-SQL it is referred to as a FULL [OUTER] JOIN and is really very straightforward. Jet SQL doesn't support this. It is possible to do, but a simple LEFT JOIN or RIGHT JOIN link would ensure ONE of the tables would have all its records shown but not the Null ones from the other.

To do it then we need to employ the following basic steps :

1. Join the two recordsets into a single recordset using a UNION query. Each recordset would populate a DIFFERENT [Calls] field.
2. Form this into a subquery.
3. GROUP the results together by [User].

Assuming [tblIncoming] & [tblOutgoing] then, we want something like the following :

SELECT [User], 
       Max(subU.IncomingCalls) AS [IncomingCalls],
       Max(subU.OutgoingCalls) AS [OutgoingCalls]
FROM (SELECT [User],
             [Calls] AS [IncomingCalls],
             Null AS [OutgoingCalls]
      FROM [tblIncoming]
      UNION ALL SELECT [User],
                       Null AS [IncomingCalls],
                       [Calls] AS [OutgoingCalls]
      FROM [tblOutgoing]) AS subU
GROUP BY subU.User

Of course a normalised database would have a single [tblCall] table with all the data in it and simply add a flag to indicate whether it's incoming or outgoing ;)

[NeoPa]

A further point worth mentioning is that the [OutgoingCalls] field is interpreted by Jet as non-numeric because the first reference to it is simply Null. If this is a problem there are ways around it.
One is to add a very basic query as the first SELECT line of the UNION query which ensures all the fields are interpreted correctly but then filter out the resultant line. Another would be to use the whole query (either as a saved QueryDef or as another level of subquery) as the source of another and cast the field using :

Val(subName.OutgoingCalls) AS [OutgoingCalls]

[ADezii]

This is quite fiddly to do in Access Jet SQL. I think in Transact-SQL it is referred to as a FULL [OUTER] JOIN and is really very straightforward. Jet SQL doesn't support this. It is possible to do, but a simple LEFT JOIN or RIGHT JOIN link would ensure ONE of the tables would have all its records shown but not the Null ones from the other.

To do it then we need to employ the following basic steps :

1. Join the two recordsets into a single recordset using a UNION query. Each recordset would populate a DIFFERENT [Calls] field.
2. Form this into a subquery.
3. GROUP the results together by [User].

Assuming [tblIncoming] & [tblOutgoing] then, we want something like the following :

SELECT [User], 
       Max(subU.IncomingCalls) AS [IncomingCalls],
       Max(subU.OutgoingCalls) AS [OutgoingCalls]
FROM (SELECT [User],
             [Calls] AS [IncomingCalls],
             Null AS [OutgoingCalls]
      FROM [tblIncoming]
      UNION ALL SELECT [User],
                       Null AS [IncomingCalls],
                       [Calls] AS [OutgoingCalls]
      FROM [tblOutgoing]) AS subU
GROUP BY subU.User

Of course a normalised database would have a single [tblCall] table with all the data in it and simply add a flag to indicate whether it's incoming or outgoing ;)

Hello NeoPa, thanks a lot for your valuable input on this Thread, it is greatly appreciated. To me, it just seemed like approximately 3 dozen lines of code to solve this problem was a little excessive, but after looking at your explanation, I realize that it may not have been - thanks again.