Matching Data from Two Tables with Non-Matching Field Data

[twinnyfo]

First, yes, the title of this thread doesn't make sense, so if anyone can come up with a better one, I'm open to it.

Here is the situation:

I have two Tables.

Table1 - This is a list of words in the Greek New Testament, sequential, indexed, listing the word forms, root form, translation and a dictionary entry. This dicitonary entry is the numerical code assigned to the dictionary entry (Field Name: LNNumber). For example the dictionary entry may be 10.30, which means semantic category 10, semantic sub-category 30. As you can see, although this value is "numeric" it is not a number, as the entry 10.3 represents sub-category 3. This field is a text field (because it has to be) in order to maintain the trailing 0.

Here is the problem with Table1. The only way I could download the data to populate this table was to export it directly into Excel. Anyone familiar with Excel knows that Excel likes to think that it is pretty smart, so when data is sent to Excel and it "looks" like a number, Excel is going to treat it like a number. Thus, all entries for 10.30 (in this example) were converted to 10.3--thus, I am unable to differentiate between the two dictionary entries. (Please, no recommendations on how I should oughta or should oughta have downloaded the data--I've tried them all and what I have is what I have).

Table2 - This table has an index and one text field--the actual dictionary entry for said words in Table1. Please note that the indexing for these two tables does NOT correspond to each other, because Table1 has records for punctuation, and other critical notes on the text. Also, Table2 has entries for each chapter and verse number, which are recorded differently in Table1.

Table2, because of how I was able to export the data, is text and has the trailing 0s. Nooooo, it is NOT possible to export both sets of data as raw text (remember, the no recommendations, thing above?). So, the dictionary references in Table2 are correct, but slightly out of alignment with Table1--but each entry in both tables should be sequentially in sync with each other (but I can't guarantee it, as some dictionary entries in Table1 have multiple entries separated by a comma--e.g., "10.30, 13.4"; Table2 has all dictionary references listed as a separate record).

I am very easily able to identify all records in Table2 which have trailing 0s (Right([LNNumber],1)="0"). Not a problem--but there are about 15,000 entries like this and I would like to be able to update Table1.LNNumber with the correct LNNumber from Table2.

I could do this manually--but I hate doing that and this would be a good opportunity to make mistakes.

Any ideas on how to approach this issue? I've been wracking my brain for days on this, and can't seem to visualize how this might come together.

I am open to any advice.

[Seth Schrock]

Is your basic question how to convert 10.3 to 10.30?

I would like to be able to update Table1.LNNumber with the correct LNNumber from Table2.

How do you know what ones match up? By their sequence?

[twinnyfo]

First Question - No.

Second Question - Yes - If I create a query that lists all the entries in Table2 plus a flag that simply identifies ANY records that have a trailing 0 and place it side by side to Table1, I can scroll through both with little problem identifying which record should be updated (I just check to see that the preceeding and following dictionary entries in both lists are the same).

But again, 15,000+ manual updates, in using two table of about 150,000 records. UGH!!!

I just can't figure out how to match up the "non-matching" records to themselves so that I can update Table1.

Thanks for your thoughts, Seth!

[NeoPa]

Hi Twinny. I'm struggling to work out what you're actually asking for. Would it be possible to post some sample data.

Also, I fully read and understand that you don't want suggestions on how to handle other parts of the import process but I'm also very curious and this thread is obviously high priority simply because it has you as the OP. So, I hope you forgive me for considering suggestions if I believe they can simplify your situation. I can say I'll have checked it properly before submitting. Also, if you can send samples of the two tables as they're made available to you then that would be great.

I'm out most of the evening but I can probably find time tomorrow to get something together.

[twinnyfo]

Here is some sample Data:

Table1
ID  Bk  Chp  Vs  Wd   MS   ...   LNNumber
 1   1   1    1   1   Βίβλος     33.38, 33.52 <--two entries
 2   1   1    1   2   γενέσεως   33.19
 3   1   1    1   3   Ἰησοῦ      93.169
 4   1   1    1   4   Χριστοῦ    93.387
 5   1   1    1   5   υἱοῦ       9.4    <--truncated
 6   1   1    1   6   Δαυὶδ      93.91
 7   1   1    1   7   υἱοῦ       9.4    <--truncated
 8   1   1    1   8   Ἀβραάμ     93.7
 9   1   1    1   9   .          
10   1   1    2   1   Ἀβραὰμ     93.7
...

Table2
ID   LNNumber
 1   1      <-- This is a chapter number
 2   2      <-- This is a verse number
 3   33.38  <--separate entry
 4   33.52  <--separate entry
 5   33.19
 6   93.169
 7   93.387
 8   9.40   <--actual dictionary entry
 9   93.91
10   9.40   <--actual dictionary entry
11   93.7
12   2      <-- This is a verse number
13   93.7
...

Hope this hepps! If I must do this manually, I can do it. But that's a lot of finger dancing....

Thanks for all y'all's assistance.

[Rabbit]

Is there an auto-number field that's keeping these records in sequence? Or is it the insert order that's keeping them in sequence?

One thing you could try is to open up two recordsets that step through each table fully. But since they're in sequence, you don't have to revisit previously visited records.

Something along the lines of

rst1 = table1
rst2 = table2
 
do until rst1.eof
   do until rst1.lnn = cdbl(rst2.lnn)
      rst2.movenext
   loop
 
   do until rst1.eof or rst1.lnn <> cdbl(rst2.lnn)
      if right(rst2.lnn, 1) = "0" then
         update rst1.lnn = rst2.lnn
      end if
 
      rst1.movenext
   loop   
loop

Edit: Looks like we cross posted. After seeing the sample data, this should still work, you'll just have to update the methodology to skip chapter and verse numbers. And also to loop through records that contain multiple numbers.

[Rabbit]

Are the two tables, for intents and purposes, one to one matches (assuming you could break down those rows with multiple numbers into a separate row)?

I was under the impression it was a one to many.

[twinnyfo]

Rabbit,

Thanks for the thoughts. I may try running through like you have described in your first post.

And yes, the two tables are "essentially" one-to-one, it's just that the index don't match. BUT, I think I see where you are going with this....

If I can create two lists (via queries) that correspond one-to-one, then I could update any changes that came along. I "think" (but I'll have to double-check) that all the LNNumbers in Table1 with multiple entries have not been truncated (because Excel interpreted "33.38, 33.52" as text, since it couldn't resolve a meaningful numerical value from it.

There may be hope yet! Thanks, Friends! I will try a few things over the next few days and give an update on any trials.

[Rabbit]

If it is "one to one"-ish, you could potentially use a SQL solution where you join table1 to itself to get the previous row's LNN, table 2 to itself to get the previous row's LNN, and then join those 2 results together on the previous and current LNN to verify it's looking at the same record.

[NeoPa]

How is the data sent to Excel? Is it via a CSV file or something similar? Is it exported in Xls or Xlsx format from somewhere?

If it's in a text format before import can we see the same section of example data shown in the pre-import format - or alternatively have a look at the file directly (attached).

[twinnyfo]

Haven't had much time to work on this the past few days. One challenge I have run into is that the total number of LNNumbers from one set of data is 138,155 and the other set is 138,468 -- so there is a disconnect there, anyway. I'll have to relook at this a bit later.

However, y'all have given me some ideas to work with.

@NeoPa - the data is dumped directly into Excel (Ctrl-C; Ctrl-V). Paste Special loses everything except the Greek words themselves (I can't tell you "why" it pastes this way, but it does). Also, a Ctrl-C; Ctrl-V into Word/Notepad also just pastes the Greek Words. Again, we can go into all the dozens of different ways I have already tried to copy and paste this data, which if there WAS a way to do this to capture that dang dangling zero, this thread would not exist.

[NeoPa]

Well Twinny, I'm still curious, but I guess from your last post you would rather I didn't explore the data transfer side of things. In that case I won't, of course :-)

I'd like to help but I can't think what I can help with at this stage. Hopefully some of the ideas given already will bear fruit for you. Good luck anyway.

[Rabbit]

As far as the different number of rows, from the sample data above, it seems to me that one set has chapter and verse numbers while the other doesn't. Also some have multiple LNN Numbers on one row.

In regards to the source of the data, you're copying and pasting from Access to Excel? I thought it was going the other way that was giving you trouble?

[twinnyfo]

Rabbit,

Yes, I've stripped out the Chapter and Verse numbers and both sets of data that I am using, via queries (one of them splitting all entries with multiple LN Numbers, the other removing all the Chapter/Verse numbers) and then ordering both queries sequentially. The ordering query that uses the splitting function takes about 10 minutes to run with 138,000 records.....

Again, this is why I asked folks not to ask about the downloading of the data. I have a different software application that I copy and paste into Excel--but this loses the trailiing zeroes. Pasting anywhere else loses everything else. I am able to copy/paste JUST the LN Numbers so that's how I got here.

Again, I have to take a closer look at what my results are to find out why there is such a disparity in the number of records. With all the chapter/verse numbers removed, there are still about 350 extra records. If I can figure that out, I might be able to work through a real solution.

As a reminder--this thread is not about the transfer/download of data. I have exhausted all possible efforts on that front. This thread is about how to match up these two lists of sequential values, searching for those that don't match. They have to be one-to-one first, though.

Thanks again for all y'all's consideration.

[Seth Schrock]

What would happen if you added a trailing zero at the end of all records, match those up, and then match up with out the trailing zero for the rest? For example, a 10 would come across as 1 and 15 would come across as 15. If you add a zero to the end, you would have 10 and 150. The 10 would then be matched properly, but the 150 wouldn't. You can then removed the trailing zero to match up the 15 to the proper record.