Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    ernest_rhaniel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    8

    Matching 2 tables with no unique fields

    T1:
    ID | Date | Hour

    T2:
    ID | Date | Hour
    I basically need to join these tables when their IDs, dates, and hours match. However, I only want to return the results from table 1 that do not match up with the results in table 2.
    I know this seems simple, but where I'm stuck is the fact that there are multiple rows in table 1 that match up with table 2 (there are multiple intervals for any given hour). I need to return all of these intervals so long as they do not fall within the same hour period in table 2.
    Example data:


    T1:
    1 | 1/1/2011 | 1
    1 | 1/1/2011 | 1
    1 | 1/1/2011 | 1
    1 | 1/1/2011 | 2
    T2:
    1 | 1/1/2011 | 1
    1 | 1/1/2011 | 1
    My expected result set for this would be the last 2 rows from T1. Can anyone point me on the right track?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Matching dates are hard.
    You only need 2 fields ID, DateFld.
    the date and time should be a single field. 1/1/2017 1pm
    Access can match a single field, perform elapsed time, (and it takes up half the space) .

    unless your 'hour' is not a time stamp,then you can match dates,by joining on them,BUT
    if there IS any time in the date field, then the Times must match too.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure I agree, but then I'm not sure I completely understand what you have either. Reason being, you say you want records from T1 where the hour period is not in T2, which according to you, that is the 3rd and 4th rows from T1. Yet the 3rd row from T1 seems to exactly match at least 2 rows from T2?? If you need to break down data retrieval to the hour, you are probably going about it the right way, assuming you're working with data that was given to you and you're trying to do something with it. Less so if you're actually storing it that way from the start, but without a clear understanding of the business at hand, it's just a supposition of mine.

    More info would help to figure out whether or not you're on the right path. Maybe you're presenting over-simplified dummy data, or data that doesn't really match the real thing (I really dislike those posts as you end up being led through what turns out to be a useless exercise). For example, is the hour really represented as an integer like 1, or is it the time portion of a date, which if it is, would make me lean towards the first answer. That's why example data is so important to the solution.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I agree with micron, you have three rows in the first dataset that are identical which match 2 identical rows in the second table. In essence if you joined these two tables on the three fields you're interested in you'd get 6 records for ID 1, Date 1/1/2011, Time 1. It seems to me you are more likely need some sort of algorithm to determine if each ID/Date/Time has a match but only match the item 1 time otherwise look for a new match. This would likely involve some sort of table update as well I can't think of a clean way to do it in queries. It also matters whether or not you want to find records in table 2 that have no match in table 1.

    This was my table1

    T1_ID T1_Date T1_Hour MatchNum
    1 1/1/2011 1
    1 1/1/2011 2
    1 1/1/2011 1
    1 1/1/2011 1
    1 1/1/2011 3
    1 1/1/2011 3
    1 1/1/2011 3
    1 1/1/2011 3


    This was my table2
    T2_ID T2_Date T2_Hour MatchNum
    1 1/1/2011 3
    1 1/1/2011 3
    1 1/1/2011 1
    1 1/1/2011 1
    1 1/1/2011 3
    1 1/1/2011 3
    1 1/1/2011 3
    1 1/1/2011 3

    This is the query I used to find unique combinations of ID, date and hour from table1
    Code:
    SELECT Table1.T1_ID, Table1.T1_Date, Table1.T1_HourFROM Table1
    GROUP BY Table1.T1_ID, Table1.T1_Date, Table1.T1_Hour
    ORDER BY Table1.T1_ID, Table1.T1_Date, Table1.T1_Hour;
    This is the code I ran:

    Code:
    Dim rstT1 As Recordset
    Dim rstUpdate As Recordset
    Dim iID, iHour As Long
    Dim dDate As Date
    Dim iT1, iT2 As Long
    Dim iMatchNum As Long
    Dim iMatchID As Long
    Dim i As Long
    
    
    CurrentDb.Execute ("UPDATE Table1 SET MatchNum = null")
    CurrentDb.Execute ("UPDATE Table2 SET MatchNum = null")
    Set rstT1 = CurrentDb.OpenRecordset("qryTable1Processing")
    
    
    Do While rstT1.EOF <> True
        iID = rstT1!t1_id
        dDate = rstT1!t1_date
        iHour = rstT1!t1_hour
        iT1 = DCount("*", "Table1", "[T1_ID] = " & iID & " AND [T1_Date] = #" & dDate & "# AND [T1_Hour] = " & iHour)
        iT2 = DCount("*", "Table2", "[T2_ID] = " & iID & " AND [T2_Date] = #" & dDate & "# AND [T2_Hour] = " & iHour)
        If iT1 > 0 And iT2 > 0 Then
            If iT1 > iT2 Then
                iMatchNum = iT2
            Else
                iMatchNum = iT1
            End If
            
            Debug.Print dDate & "  " & iMatchNum
            Set rstUpdate = CurrentDb.OpenRecordset("SELECT TOP " & iMatchNum & " MatchNum FROM Table1 WHERE [T1_ID] = " & iID & " AND [T1_Date] = #" & dDate & "# AND [T1_Hour] = " & iHour)
            i = 0
            Do While rstUpdate.EOF <> True
                i = i + 1
                rstUpdate.Edit
                rstUpdate!matchnum = iMatchID + i
                rstUpdate.Update
                rstUpdate.MoveNext
            Loop
            rstUpdate.Close
            Set rstUpdate = CurrentDb.OpenRecordset("SELECT TOP " & iMatchNum & " MatchNum FROM Table2 WHERE [T2_ID] = " & iID & " AND [T2_Date] = #" & dDate & "# AND [T2_Hour] = " & iHour)
            i = 0
            Do While rstUpdate.EOF <> True
                i = i + 1
                rstUpdate.Edit
                rstUpdate!matchnum = iMatchID + i
                rstUpdate.Update
                rstUpdate.MoveNext
            Loop
            rstUpdate.Close
            
            Debug.Print "adding " & iMatchID & " to " & iMatchNum
            iMatchID = iMatchID + iMatchNum
        End If
        rstT1.MoveNext
    Loop
    rstT1.Close
    Set rstT1 = Nothing
    End Sub
    After it was done running you could select all records from table 1 or table 2 to find ones that didn't have a match but testing for a null value in the matchnum field.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was thinking of an unmatched query based on the hour portion not being in T2 WHERE the dates are equal (as per equal join I guess) provided the hour is integer/long.
    Suggest waiting for OP to respond; it's been several hours.

  6. #6
    ernest_rhaniel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    8
    Hi Sir, the reason why only want the two rows to be matched is because I want to reconcile the two tables not only per record but also per count. Table 1 is my report and Table 2 is the report came from our client.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    did you try the code I gave you

  8. #8
    ernest_rhaniel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    8
    @rpeare I got error message "Data Type Mismatch in Criteria Expression" for below line

    iT1 = DCount("*", "Table1", "[T1_ID] = " & iID & " AND [T1_Date] = #" & dDate & "# AND [T1_Hour] = " & iHour)

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you have to substitute in your own field/table names, if you re-create the example as I stated it you will see how it works then you can apply it to your problem.

  10. #10
    ernest_rhaniel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    8
    I just re-create it but got an error. Hope you can help.

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would sugest you did not make the

    T1_ID is a number field
    T1_Date is a date field
    T1_Hour is a number field

    make your datatypes match this and you'll be fine.

  12. #12
    ernest_rhaniel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    8
    Got it and it works. Thank you so much sir! I'll try to use it on my data table.

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The OP sent me a message stating
    I already tried the code on my data table but it took me long enough to finish running. Is there a way to make it faster? Also, I encountered "Run time error 3035" (*System Resource exceeded)

    I am posting a response here in case anyone else needs this thread.

    There are a number of ways you could handle this but your description of your process is not really helping me understand more than your basic need, nor are your tables set up to help you, neither table has a primary key and you are relying on multiple fields that, combined, don't make the record unique which is going to make your processing very slow no matter what you do. I also have no idea whether you are interested in the unmatched records in both tables or just one of the tables. I also have no idea of whether or not what my code did is what you want, that's not clearly stated either, I just made some assumptions and went with it. As for processing time, I do not have, and I'm not going to create a 250,000 record table to test code on.

    Another option would be to create two aggregate queries that count the number of instances of each 3 field combination (the three fields from the original problem) then link those two queries together and take the smaller of the two numbers.

    Once you have that third query you can use that as your recordset to cycle through the different combinations and update records on both tables.

  14. #14
    ernest_rhaniel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    8
    Thanks for your input sir. To give you idea on my table, I have 3 fields (Account Number,Amount,Concatenate Account Number/Amount) you are right I really have no primary key and I'm relying on the fields specifically the concatenate data as basis for my matching. What I really want to get is the unmatched records for both tables and your code really help solve my problem. The only issue I got is the processing time because I have 25,000 (to correct my previous message) records for both tables and I received Run Time Error 3035 (System Resource Exceeded). Hope you guys can help avoid this error and make the processing time shorter.

    Many thanks,

  15. #15
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A couple of points based on the last post.
    25,000 records is not a lot, and the replies don't seem to shed any light on what it means with respect to taking too long. Is that 3 minutes, 3 hours or 3 days? If sys resources are being taxed with that many records, the problem is not the record quantity, it is the db design. Really, you probably need to research normalization as this is usually the underlying cause of this problem, along with creating calculated or concatenated data in tables.

    Also no mention has been made of indexes, and I'd bet that none have been created since knowledge of these is usually preceded by knowledge of normalization, good design and the need for PK/FK fields in relationships.

    The problem with Access is that it's too easy to dive in and create poorly designed db's. I liken it to handing a book about driving and the keys to the car to a teenager who doesn't have a license. They might get the car from the start position to some other place, but the result is not likely to be pretty, and the outcome might actually be damaging.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-09-2016, 09:03 PM
  2. Matching id values between two tables
    By Nimo in forum Access
    Replies: 8
    Last Post: 06-04-2014, 01:29 PM
  3. Replies: 3
    Last Post: 05-01-2012, 01:57 PM
  4. Matching between 2 tables
    By slevin in forum Access
    Replies: 1
    Last Post: 06-16-2010, 07:28 AM
  5. Replies: 5
    Last Post: 03-20-2010, 08:30 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums