UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Look For Same Words Between Two Colums And Give A Result, Access 2016    
 
   
Floresta
post Jan 3 2018, 01:14 PM
Post#1



Posts: 92
Joined: 26-May 05
From: Portugal


Hello, I would like to make a relation between Exemplo1 Table and Exemplo2 table (attachment)
Each row in "Words1" and "Words2" column has letters from the alphabet as an example and imagine each letter to correspond to a word (question that I want to solve).

I want the result to be like in "Exemplo relacao word" table. To make the relation when at least 1 word is the same in both columns, or two words and more until maximum equals words found.

I would appreciate any incite to solve this.

Thanks,


Attached File(s)
Attached File  Exemplo_Search_Words.zip ( 45.67K )Number of downloads: 4
 

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Doug Steele
post Jan 3 2018, 04:35 PM
Post#2


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I believe your example is incomplete. In addition to the 7 examples you show, you should also be showing that IDExemplo1 value 2 (B C D F) matches IDExemplo2 value 2 (F G H), since there's an F in both, and that IDExemplo1 value 4 (G H I J O) matches IDExemplo2 value 4 (Y O), since there's an O in both.

Realistically, I'd say your tables aren't normalized.

Rather than Exemplo1 being
CODE
IDExemplo1  Words1
1           A B C D
2           B C D F
3           F G H I
4           G H I J O
5           E Y O T P

it should be

CODE
IDExmeplo1  WordNumber1  Word1
1           1            A
1           2            B
1           3            C
1           4            D
2           1            B
2           2            C
2           3            D
2           4            F
3           1            F
3           2            G
3           3            H
3           4            I
4           1            G
4           2            H
4           3            I
4           4            J
4           5            O
5           1            E
5           2            Y
5           3            O
5           4            T
5           5            P

Similarly, Exemplo2, which is currently

CODE
IDExemplo2    Words2
1               A C D
2               F G H
3               G H I
4               Y O

should be

CODE
IDExemplo2  WordNumber2  Word2
1           1            A
1           2            C
1           3            D
2           1            F
2           2            G
2           3            H
3           1            G
3           2            H
3           3            I
4           1            Y
4           2            O


Now, a simple query

CODE
SELECT DISTINCT Table1.IDExemplo1, Table2.IDExemplo2
FROM Table1 INNER JOIN Table2
ON Table1.Word1= Table2.Word2;

will give you the correct results:

CODE
IDExemplo1  IDExemplo2
1           1
2           1
2           2
3           2
3           3
4           2
4           3
4           4
5           4

Attached File  Exemplo_Search_Words.zip ( 23.42K )Number of downloads: 2


--------------------
Go to the top of the page
 
Floresta
post Jan 3 2018, 07:25 PM
Post#3



Posts: 92
Joined: 26-May 05
From: Portugal


Hello Doug,

thank you for the reply and let me say nice Resume.

In my case i try to compare all the words from example1 with all the words example 2 when at least it finds more than 4 words, more than 3, more than 2 or just one.

It might content 1 equal word, or 2 or 3 or more than 4. That it makes the relation between the two columns and choose first the more than 4 and reserve this value with the relation, then go for the next record and ignor the rows that already has connection and go on until all of them are related.

I hope i could be more transparent in my question.


Thanks,
This post has been edited by Floresta: Jan 3 2018, 07:27 PM

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Doug Steele
post Jan 3 2018, 08:06 PM
Post#4


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If you change the query to

CODE
SELECT DISTINCT Table1.IDExemplo1, Table2.IDExemplo2, Count(*) AS WordsInCommon
FROM Table1 INNER JOIN Table2 ON Table1.Word1 = Table2.Word2
GROUP BY Table1.IDExemplo1, Table2.IDExemplo2;

it will give you a count of how many words are found in common.

--------------------
Go to the top of the page
 
Floresta
post Jan 4 2018, 07:48 AM
Post#5



Posts: 92
Joined: 26-May 05
From: Portugal


Hello Doug,

Since to normalized the column you separate the words from the row. How did you do that? And is possible to join them together again after the result is complete?

Can I send you a true example using a private message? I wulod like to explain better using a true example, but I didn't want to expose the subject.

Thanks,

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Doug Steele
post Jan 4 2018, 07:52 AM
Post#6


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Sure, send me a real example and I'll write code to normalize and denormalize the data to make it more useful to you.

--------------------
Go to the top of the page
 
Floresta
post Jan 4 2018, 08:28 AM
Post#7



Posts: 92
Joined: 26-May 05
From: Portugal


Do I use Contact information/ send message? Can I send you attachments using this send messages button?

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Jeff B.
post Jan 4 2018, 08:34 AM
Post#8


UtterAccess VIP
Posts: 9,958
Joined: 30-April 10
From: Pacific NorthWet


... an additional comment ...

If you are saying that the same word(s) can appear in more than one (Access table) column, you may want to consider further normalization of your data.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Floresta
post Jan 4 2018, 08:48 AM
Post#9



Posts: 92
Joined: 26-May 05
From: Portugal


Ok. I'm going to send you a real example. It doesn't matter if it's private or not.

Give some time.

Thanks,

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Floresta
post Jan 4 2018, 07:04 PM
Post#10



Posts: 92
Joined: 26-May 05
From: Portugal


Hello Doug, I'm adding the file that I talk about. One is an access database and the other is a excel table. Make "Extract here" to see the split volume zip.

First in access database there is a table "Compras Produto" with a field called "Nome do Produto Total" that comes from some fields from the same table but it shows the ID numbers instead of the names (this field should be the same as in the form Compras Produtos "Nome do Produto"). Note that in "Nome do Produto Total" exists fields that could diverge only in "EAN" and also in the "ANO" (Year) of the product that can be a wine. Two or more wines with different EAN and different years. If is a wine they could be from another year. This table is to complex with a lot of fields and is not totally normalized. I would like to make a relation between the same column "Nome do Produto Total" in Compras Produtos. I can say that I have duplicate values in the names except the year because correspond to different prices.

Then I would like to make the relation between the access "Compras Produto" column "Nome do Produto Total" already related with each other and the column "Descricao" in the excel table. This excel table are simply invoices from the distributors and I want to relate to the values that I have.

Some fields that are included in the"Nome do Produto Total" they are already normalized but not all.

First question - "In my case i try to compare all the words from example1 with all the words example 2 when at least it finds more than 4 words, more than 3, more than 2 or just one.

It might content 1 equal word, or 2 or 3 or more than 4. That it makes the relation between the two columns and choose first the more than 4 and reserve this value with the relation, then go for the next record and ignor the rows that already has connection and go on until all of them are related.

I hope i could be more transparent in my question."

Second question - "Since to normalized the column you separate the words from the row. How did you do that? And is possible to join them together again after the result is complete?

Can I send you a true example using a private message? I would like to explain better using a true example, but I didn't want to expose the subject."

This is a products database from Portugal where you can check some wines names.

I'm afraid that this is almost impossible to accomplished if I don't use query's.

This table happen like this because I duplicate values using the form Compras Produtos and change some data in it to correspond to the new value. (Year, price, discount, Cap and others). Make a test in the form and you will understand what I mean. Choose a value from the list on the right duplicate it and change the values that could refer to another bottle or product.
All the fields in the "Compras Produtos" table could make a complete database of products of prices during a time.

Sorry for the confusion. I hope you understand.

Best regards,

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Doug Steele
post Jan 4 2018, 08:15 PM
Post#11


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I'll try and take a look at it tomorrow.

--------------------
Go to the top of the page
 
Floresta
post Jan 4 2018, 09:35 PM
Post#12



Posts: 92
Joined: 26-May 05
From: Portugal


I have to correct the reply.

Eliminate my previous post, please.

It was made in hurry and is wrong put.

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Floresta
post Jan 5 2018, 05:20 AM
Post#13



Posts: 92
Joined: 26-May 05
From: Portugal


Hello Doug, I'm adding the file that I talk about. One is an access database and the other is a excel table. Make "Extract here" to see the split volume zip.

This is a products database from Portugal where you can check some wines names and years for the Port Wines.

The objective of this is to take advantage over the OCR of the Distributors Invoice in excel table and make a connection with the words from the field "Descricao" Excel and the field "Nome Produto Total" from table "Compras Produtos" that is formatted (Normalized) as I designate This name is to be used in the receipt to the final consumer in my store and to print labels.

Note: the content of the field "Nome Produto Total" should be the same as in the FORM "Compras Produtos" "Nome do Produto". For any particular reason it shows the ID number instead the related names.

Look to the table "Compras Produtos". It has a field called "Nome do Produto Total".

This table is to complex with a lot of fields and is not totally normalized. I have to separate this into others tables but I didn't eliminate the data in this example for you to check all the fields table that could make a complete database of products of prices during a time.

The field "Nome Produtos Total" is normalized. I would like to make a relationship between the column "Nome do Produto Total" in "Compras Produtos" table and the field "Descricao" from the excel.

The first to do is, since in this table I have duplicate values in the field "Nome do Produto Total" is necessary to separate them into one to many relationship.

This table happens to be like this because I duplicate values using the form "Compras Produtos", and change the data to correspond to the new values for Ano (Year), price, discount, Cap and others.
Make a test in the form and you will understand what I mean. Choose a value from the list on the right, click on Duplicate and in the new row change the values.

Then I would like to make the relationship between the access "Compras Produtos" column "Nome do Produto Total" and the table Excel field "Descricao" adding the values to the other table created, many relationships. This excel table are simply invoices from the distributors and I want to relate the names to the values that I have in access.


First question - "In my case i try to compare all the words from example1 with all the words example 2 when at least it finds more than 4 words, more than 3, more than 2 or just one.

It might content 1 equal word, or 2 or 3 or more than 4. That it makes the relation between the two columns and choose first the more than 4 and reserve this value with the relation, then go for the next record and ignor the rows that already has connection and go on until all of them are related.

I hope i could be more transparent in my question."

Second question - "Since to normalized the column you separate the words from the row. How did you do that? And is possible to join them together again after the result is complete?"

In this case the "Nome Produto Total" and "Descricao" becames related as one to many. Then I can use the last entry fields from the Many relationship to be the default value price in use for the products.

I'm afraid that this is almost impossible to accomplished if I don't use query's.

I don't know if it make sense to you.


Sorry for the confusion. I hope you understand.

Thanks,
Attached File(s)
Attached File  Livro_FT.zip ( 866.19K )Number of downloads: 2
 

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Doug Steele
post Jan 5 2018, 07:51 AM
Post#14


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Your attachments Real_Example_1.zip and Real_Example_2.zip seem faulty. I get an error when I try to open the first, and the second just contains a file with an extension of .z01.

--------------------
Go to the top of the page
 
Floresta
post Jan 5 2018, 08:42 AM
Post#15



Posts: 92
Joined: 26-May 05
From: Portugal


Hello Doug just put both extract file on the same folder and use Real Example.zip no extract a second time. The files are split in Real Example.zip and Real Example.z01. Just make extract here in both files and it will extract to a folder named Real Example and in there extract a second time using Real Example.zip

Thanks,

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Doug Steele
post Jan 5 2018, 09:12 AM
Post#16


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I'm sorry, I don't understand your instructions.

Real_Example_1.zip and Real_Example_2.zip are both in the same folder. When I try to extract from Real_Example_1.zip, I get this error:

Attached File  Extract_Error.jpg ( 45.09K )Number of downloads: 0


How did you zip the file? Did you use a third-party product that split them into multiple files?

--------------------
Go to the top of the page
 
Floresta
post Jan 5 2018, 12:16 PM
Post#17



Posts: 92
Joined: 26-May 05
From: Portugal


Hello Doug, this was made using Winrar. UtterAccess only support files until 2MB.

Do you have Winzip or Winrar installed?

If yes, Select both files Real Example1 and Real Example 2 and make right click mouse and make extract here.
It will put the extracted files in a folder called Real Example. Then open the folder and you have Real Example.zip and Real Example.z01. Both is a complement of each other. Both has to be in the same folder. Then select only Real example.zip and extract and the database is extracted from both files.

https://www.win-rar.com/start.html?&L=0

Tell me if you did it.

Thanks.

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Doug Steele
post Jan 5 2018, 12:47 PM
Post#18


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Sorry, I don't have WinZip nor WinRAR installed, nor am I willing to install them.

How big is this database? I thought you were simply going to provide a representative sample! Have you at least compacted the database before zipping it?

--------------------
Go to the top of the page
 
Floresta
post Jan 5 2018, 01:41 PM
Post#19



Posts: 92
Joined: 26-May 05
From: Portugal


Hello Doug, new version. More light.

Transpose of the previous text.

The two files that I uploaded one is a access database and the other is a excel table.

This is a products database from Portugal where you can check some wines names and years for the Port Wines.

The objective of this is to take advantage over the OCR of the Distributors Invoice in excel table and make a connection with the words from the field "Descricao" Excel and the field "Nome Produto Total" from table "Compras Produtos" that is formatted (Normalized) as I designate This name is to be used in the receipt to the final consumer in my store and to print labels.

Note: the content of the field "Nome Produto Total" should be the same as in the FORM "Compras Produtos" "Nome do Produto". For any particular reason it shows the ID number instead the related names.

Look to the table "Compras Produtos". It has a field called "Nome do Produto Total".

This table is to complex with a lot of fields and is not totally normalized. I have to separate this into others tables but I didn't eliminate the data in this example for you to check all the fields table that could make a complete database of products of prices during a time.

The field "Nome Produtos Total" is normalized. I would like to make a relationship between the column "Nome do Produto Total" in "Compras Produtos" table and the field "Descricao" from the excel.

The first to do is, since in this table I have duplicate values in the field "Nome do Produto Total" is necessary to separate them into one to many relationship.

This table happens to be like this because I duplicate values using the form "Compras Produtos", and change the data to correspond to the new values for Ano (Year), price, discount, Cap and others.
Make a test in the form and you will understand what I mean. Choose a value from the list on the right, click on Duplicate and in the new row change the values.

Then I would like to make the relationship between the access "Compras Produtos" column "Nome do Produto Total" and the table Excel field "Descricao" adding the values to the other table created, many relationships. This excel table are simply invoices from the distributors and I want to relate the names to the values that I have in access.


First question - "In my case i try to compare all the words from example1 with all the words example 2 when at least it finds more than 4 words, more than 3, more than 2 or just one.

It might content 1 equal word, or 2 or 3 or more than 4. That it makes the relation between the two columns and choose first the more than 4 and reserve this value with the relation, then go for the next record and ignor the rows that already has connection and go on until all of them are related.

I hope i could be more transparent in my question."

Second question - "Since to normalized the column you separate the words from the row. How did you do that? And is possible to join them together again after the result is complete?"

In this case the "Nome Produto Total" and "Descricao" becames related as one to many. Then I can use the last entry fields from the Many relationship to be the default value price in use for the products.

I'm afraid that this is almost impossible to accomplished if I don't use query's.

I don't know if it make sense to you.


Sorry for the confusion. I hope you understand.

Thanks,
This post has been edited by Floresta: Jan 5 2018, 01:56 PM
Attached File(s)
Attached File  Livro_FT.zip ( 866.19K )Number of downloads: 0
Attached File  Real_Example.zip ( 465.58K )Number of downloads: 2
 

--------------------
Srvictor

"I think, therefore I am."
Go to the top of the page
 
Doug Steele
post Jan 5 2018, 03:34 PM
Post#20


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You say
QUOTE
The field "Nome Produtos Total" is normalized.


Fields aren't normalized, tables are. And there isn't a field named Nome Produtos Total in table Compras Produtos: there's only a field called Nome do Produto Total, which violates First Normal Form, since it contains multiple pieces of information.

I understand what you're trying to do, but I'm afraid your database design is too flawed for me to do anything worthwhile with.

Sorry. Good luck with your project!

--------------------
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 07:41 PM