Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    93

    Unanswered: Match query for records between 3 tables

    Hello

    I have 3 different tables with ID as Common field among them

    I am trying to find ID match and get the total count of records which exist in table2 and table3

    here is my query for 2 tables which is working fine

    SELECT Count(table2.GROUP_ID) AS CountOfGROUP_ID
    FROM table1 INNER JOIN table2 ON table1.GROUP_ID = table2.GROUP_ID;

    but when i add table3 to the above query, its showing the result as 0 and below is the query

    SELECT Count(table2.GROUP_ID) AS CountOfGROUP_ID, Count(table3.GROUP_ID) AS CountOfGROUP_ID1
    FROM (table1 INNER JOIN table2 ON table1.GROUP_ID = table2.GROUP_ID) INNER JOIN table3 ON table1.GROUP_ID = table3.GROUP_ID;

    any help would be appreciated

    Thanks in advance

  2. #2
    Join Date
    Nov 2007
    Posts
    93
    still haven't had any success

    any thoughts would be appreciated

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Solution

    Below is an example of code to run thhrough 2 different recordsets of criteria using a count function in the SQL statement. I tested this with my table and data using two real strings for the criteria and running the code.

    The query view of the first sql statement returns 35 and the query view of the second returns 23.

    When i ran this code it returned a messagebox statement with 58



    Public Function fnTestCount()
    Dim strsql As String
    Dim rst As Recordset
    Dim x, y
    Dim lngTotal As Long

    strsql = "SELECT [CRX TASK new data].Line, Count([CRX TASK new data].Line) AS CountOfLine " _
    & "FROM [CRX TASK new data] " _
    & "GROUP BY [CRX TASK new data].Line " _
    & "HAVING [CRX TASK new data].Line='CRX BF.61000.01'"

    Set rst = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)
    x = 0
    If Not rst.EOF Then
    Do Until rst.EOF
    x = x + rst!countofline
    rst.MoveNext
    Loop
    End If


    y = 0

    strsql = "SELECT [CRX TASK new data].Line, Count([CRX TASK new data].Line) AS CountOfLine " _
    & "FROM [CRX TASK new data] " _
    & "GROUP BY [CRX TASK new data].Line " _
    & "HAVING [CRX TASK new data].Line='CRX BF.61000.04'"

    Set rst = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)

    If Not rst.EOF Then
    Do Until rst.EOF
    y = y + rst!countofline
    rst.MoveNext
    Loop
    End If


    lngTotal = x + y
    MsgBox lngTotal

    End Function
    Dale Houston, TX

  4. #4
    Join Date
    Nov 2007
    Posts
    93
    Thanks a ton

    I tweaked the SQL query and was able to get the count of X and Y

    The all i need to do now is to store the values of X,Y in tablez like update query from recordset to update the value of X and Y in tablez

    Would appreciate if you can guide me through

    Once again thanks for your effort and time

  5. #5
    Join Date
    Nov 2007
    Posts
    93
    i got the solution using

    Docmd****nsql (insert into (x,y) values (" & x &", " & y &")

    I am getting warning messages but i can easily turn off the warnings, that should not be a problem

    Thanks once again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •