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:
   [CODE=vb]
   Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
   Dim rstResults As DAO.Recordset
   
   'DELETE any existing Records from tblResults
   DoCmd.SetWarnin gs False
   DoCmd.RunSQL "Delete * From tblResults;"
   
   'Add [User] and [IncomingCalls] to tblResults
   strSQL = "INSERT INTO tblResults ( [User], IncomingCalls ) SELECT [tblIncomingCall s].[User], " & _
   "[tblIncomingCall s].[IncomingCalls] FROM tblIncomingCall s;"
   DoCmd.RunSQL strSQL
   
   DoCmd.SetWarnin gs True
   
   'Create a Recordset based on tblOutgoingCall s
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecord set("tblOutGoin gCalls", dbOpenForwardOn ly)
   
   'Create a Recordset based on tblResults
   Set rstResults = MyDB.OpenRecord set("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.AddN ew
   rstResults![User] = MyRS![User]
   rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
   rstResults.Upda te
   Else
   'Find the right User
   rstResults.Find First "[User] = '" & MyRS![User] & "'"
   rstResults.Edit
   rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
   rstResults.Upda te
   End If
   MyRS.MoveNext
   Loop
   
   MyRS.Close: Set MyRS = Nothing
   rstResults.Clos e: Set rstResults = Nothing[/CODE]

[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:
   [CODE=vb]
   Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
   Dim rstResults As DAO.Recordset
   
   'DELETE any existing Records from tblResults
   DoCmd.SetWarnin gs False
   DoCmd.RunSQL "Delete * From tblResults;"
   
   'Add [User] and [IncomingCalls] to tblResults
   strSQL = "INSERT INTO tblResults ( [User], IncomingCalls ) SELECT [tblIncomingCall s].[User], " & _
   "[tblIncomingCall s].[IncomingCalls] FROM tblIncomingCall s;"
   DoCmd.RunSQL strSQL
   
   DoCmd.SetWarnin gs True
   
   'Create a Recordset based on tblOutgoingCall s
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecord set("tblOutGoin gCalls", dbOpenForwardOn ly)
   
   'Create a Recordset based on tblResults
   Set rstResults = MyDB.OpenRecord set("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.AddN ew
   rstResults![User] = MyRS![User]
   rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
   rstResults.Upda te
   Else
   'Find the right User
   rstResults.Find First "[User] = '" & MyRS![User] & "'"
   rstResults.Edit
   rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
   rstResults.Upda te
   End If
   MyRS.MoveNext
   Loop
   
   MyRS.Close: Set MyRS = Nothing
   rstResults.Clos e: Set rstResults = Nothing[/CODE]

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.