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

Display results from 2 tables including empty values

vavc1980
P: 25
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..
Feb 26 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,705
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
    2. Dim rstResults As DAO.Recordset
    3.  
    4. 'DELETE any existing Records from tblResults
    5. DoCmd.SetWarnings False
    6.   DoCmd.RunSQL "Delete * From tblResults;"
    7.  
    8. 'Add [User] and [IncomingCalls] to tblResults
    9. strSQL = "INSERT INTO tblResults ( [User], IncomingCalls ) SELECT [tblIncomingCalls].[User], " & _
    10.          "[tblIncomingCalls].[IncomingCalls] FROM tblIncomingCalls;"
    11. DoCmd.RunSQL strSQL
    12.  
    13. DoCmd.SetWarnings True
    14.  
    15. 'Create a Recordset based on tblOutgoingCalls
    16. Set MyDB = CurrentDb()
    17. Set MyRS = MyDB.OpenRecordset("tblOutGoingCalls", dbOpenForwardOnly)
    18.  
    19. 'Create a Recordset based on tblResults
    20. Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    21.  
    22. Do While Not MyRS.EOF
    23.   'Does the User already exist? If not then Add to tblResults, but if he/she does exist
    24.   'then Update tblResults with the OutGoingCalls values
    25.   If DCount("*", "tblResults", "[User] = '" & MyRS![User] & "'") = 0 Then
    26.     rstResults.AddNew
    27.       rstResults![User] = MyRS![User]
    28.       rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
    29.     rstResults.Update
    30.   Else
    31.     'Find the right User
    32.     rstResults.FindFirst "[User] = '" & MyRS![User] & "'"
    33.     rstResults.Edit
    34.       rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
    35.     rstResults.Update
    36.   End If
    37.   MyRS.MoveNext
    38. Loop
    39.  
    40. MyRS.Close: Set MyRS = Nothing
    41. rstResults.Close: Set rstResults = Nothing
Feb 26 '08 #2

vavc1980
P: 25
  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:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
    2. Dim rstResults As DAO.Recordset
    3.  
    4. 'DELETE any existing Records from tblResults
    5. DoCmd.SetWarnings False
    6.   DoCmd.RunSQL "Delete * From tblResults;"
    7.  
    8. 'Add [User] and [IncomingCalls] to tblResults
    9. strSQL = "INSERT INTO tblResults ( [User], IncomingCalls ) SELECT [tblIncomingCalls].[User], " & _
    10.          "[tblIncomingCalls].[IncomingCalls] FROM tblIncomingCalls;"
    11. DoCmd.RunSQL strSQL
    12.  
    13. DoCmd.SetWarnings True
    14.  
    15. 'Create a Recordset based on tblOutgoingCalls
    16. Set MyDB = CurrentDb()
    17. Set MyRS = MyDB.OpenRecordset("tblOutGoingCalls", dbOpenForwardOnly)
    18.  
    19. 'Create a Recordset based on tblResults
    20. Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    21.  
    22. Do While Not MyRS.EOF
    23.   'Does the User already exist? If not then Add to tblResults, but if he/she does exist
    24.   'then Update tblResults with the OutGoingCalls values
    25.   If DCount("*", "tblResults", "[User] = '" & MyRS![User] & "'") = 0 Then
    26.     rstResults.AddNew
    27.       rstResults![User] = MyRS![User]
    28.       rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
    29.     rstResults.Update
    30.   Else
    31.     'Find the right User
    32.     rstResults.FindFirst "[User] = '" & MyRS![User] & "'"
    33.     rstResults.Edit
    34.       rstResults![OutGoingCalls] = MyRS![OutGoingCalls]
    35.     rstResults.Update
    36.   End If
    37.   MyRS.MoveNext
    38. Loop
    39.  
    40. MyRS.Close: Set MyRS = Nothing
    41. rstResults.Close: Set rstResults = Nothing
Thanks! that worked!
Feb 26 '08 #3

ADezii
Expert 5K+
P: 8,705
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.
Feb 27 '08 #4

NeoPa
Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [User], 
  2.        Max(subU.IncomingCalls) AS [IncomingCalls],
  3.        Max(subU.OutgoingCalls) AS [OutgoingCalls]
  4. FROM (SELECT [User],
  5.              [Calls] AS [IncomingCalls],
  6.              Null AS [OutgoingCalls]
  7.       FROM [tblIncoming]
  8.       UNION ALL SELECT [User],
  9.                        Null AS [IncomingCalls],
  10.                        [Calls] AS [OutgoingCalls]
  11.       FROM [tblOutgoing]) AS subU
  12. 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 ;)
Feb 27 '08 #5

NeoPa
Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. Val(subName.OutgoingCalls) AS [OutgoingCalls]
Feb 27 '08 #6

ADezii
Expert 5K+
P: 8,705
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [User], 
  2.        Max(subU.IncomingCalls) AS [IncomingCalls],
  3.        Max(subU.OutgoingCalls) AS [OutgoingCalls]
  4. FROM (SELECT [User],
  5.              [Calls] AS [IncomingCalls],
  6.              Null AS [OutgoingCalls]
  7.       FROM [tblIncoming]
  8.       UNION ALL SELECT [User],
  9.                        Null AS [IncomingCalls],
  10.                        [Calls] AS [OutgoingCalls]
  11.       FROM [tblOutgoing]) AS subU
  12. 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.
Feb 27 '08 #7

Post your reply

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