Display on form if found query

[hunsnowboard]

Hi there Everyone! I just started learning MS Access for a few days. I know a little (very little) VBA Excel programming, but Access is a whole new world for me.
Here is my problem:
I have one table (Database table) with a few columns. There are three rows which are relevant in my problem (the rows are called: "Important_0", "Important_1" and "Important_2"). I have also one form (MainForm) in the file. When I run the query I would like to have a message box pop up where I can input the number I am looking for. Then the query should search for the inputed number in all of the three columns, and display the column names where the number was found in the MainForm form. If found in two columns, then display the names of the two columns, if in three then display the name of all the three columns, if not found than display: "Input number not found". Now I read some articles (and I am reading now Access step by step) and I know that I should normalise my table but I really do not know how to do it. The table attached shows connections. Actually it is a table which was created for showing the connections between codenumbers (for easy example let's say names).
So, as I described, this table shows a three connection chain.
Example:
.............................COLUMNS.......................
Important_0........Important_1.........Important_2.
John -----------> George -----------> Rob
John -----------> George -----------> Michael
John -----------> George -----------> Peter
John -----------> George -----------> Brad
John -----------> Mark -----------> Denis
John -----------> Mark -----------> Jones
John -----------> Smith -----------> Amber
...
...
George -----------> John-----------> LLoyd
George -----------> John -----------> Daniel
Smith -----------> Amber -----------> Clark

In the real file there are more than 140.000 rows which means that there are many many connections, with names present in all three columns (column A, B C). And this is my first problem, how do I normalize this table? Could you give me an advice? How would you do it?

Please check the attached sample table. Thank you in advance for your help!

CROSS-POST: http://www.access-programmers.co.uk/...d.php?t=166037

[hansup]

So, as I described, this table shows a three connection chain.
Example:
.............................COLUMNS.......................
Important_0........Important_1.........Important_2.
John -----------> George -----------> Rob
John -----------> George -----------> Michael
John -----------> George -----------> Peter
John -----------> George -----------> Brad
John -----------> Mark -----------> Denis
John -----------> Mark -----------> Jones
John -----------> Smith -----------> Amber
...
...
George -----------> John-----------> LLoyd
George -----------> John -----------> Daniel
Smith -----------> Amber -----------> Clark

In the real file there are more than 140.000 rows which means that there are many many connections, with names present in all three columns (column A, B C). And this is my first problem, how do I normalize this table? Could you give me an advice? How would you do it?

Your example shows only unique combinations. Is that a requirement of your data model? In other words, could you have another row which includes "John -----------> George -----------> Rob"?

If repeating combinations are expected, you might consider a table design like this:

Combination_ID...Person.......Importance
1 ---------------> John ----> 0
1 ---------------> George -> 1
1 ---------------> Rob -----> 2
2 ---------------> John ----> 0
2 ---------------> George -> 1
2 ---------------> Michael -> 2

That design would support the query you wanted for your form:
SELECT DISTINCT Importance FROM tblConnections
WHERE Person = [Enter name of Person];

Hope that helps. But I don't really understand what you're trying to accomplish.

Hans

[CreganTur]

Wow...

I don't think you realize how much information you're requesting in your post. Please note that I am not criticising you at all, I just want you to know that understanding normalization is something that is going to take a while. Honestly, it's a skill I learned through practice more than through study (but study definately helps!), and there's a good bit about normalization I still don't know.

Here is an article that explains normalization in very broad terms. Basically, normalization is a design process that the developer should go through before actually creating the database. I say "should go through" because very often you end up having to normalize an existing database, which is a bit of a pain.

To normalize a database, you plan out all of the tables your database will need, along with all of the fields for each table. Once you have done this, you then look at all of the tables to make sure that you are not duplicating any information. The cornerstone of a relational database is that you have multiple tables that are related to each other by primary and foreign keys.

For example: Say you have two tables: a Customer's table an an orders table. A non-normalized database would show the customer's name, address, and phone number in both of these tables. A normalized database would assign a unique primary key value to every customer in the Customer's tables, and that primary key would be shown as a foreign key in the Orders table to link each order with the customer who placed it.

Now, I've only given a very high-level overview of normalization, but it should give you a decent starting point. The hardest part of normalization is creating solid logic that governs all of your tables and how they are related so that you don't have any unnecessary duplication.

[hunsnowboard]

Hi Hansup and CreganTur!
Thank you for your replies. I'll try to answer you both.

Hansup: the table might have duplicate data. But that it is not important. I will delete those rows. Anyway this table is a hierarchy relation table. Actually it represents companies related to each other (by the mean of ownership; this means that the main company is in column Important_0, then there is the other company (column Important_1) which is related (owner, partner, shareholder, etc) to the main company, and then there are the other companies (important_2) which are related (owner, partner, shareholder, etc) to the second company. Now my problem is that there are more then 10.000 companies, so I cannot make a table for each one!

CreganTur: described above you can see my problem more clearly. As for normalization, I read a few sites, I watched a few tutorials but the problem is that everyone shows this customer-order relation, which I understood clearly and already can make it by myself. The problem is that in my case I really do not have a clue how to normalise this table where there are companies related to each other... That is why I am writing on forums, hoping that someone takes a look at my table and gives me a tip/hint how to begin...

[orange]

Hi Hansup and CreganTur!
Thank you for your replies. I'll try to answer you both.

Hansup: the table might have duplicate data. But that it is not important. I will delete those rows. Anyway this table is a hierarchy relation table. Actually it represents companies related to each other (by the mean of ownership; this means that the main company is in column Important_0, then there is the other company (column Important_1) which is related (owner, partner, shareholder, etc) to the main company, and then there are the other companies (important_2) which are related (owner, partner, shareholder, etc) to the second company. Now my problem is that there are more then 10.000 companies, so I cannot make a table for each one!

CreganTur: described above you can see my problem more clearly. As for normalization, I read a few sites, I watched a few tutorials but the problem is that everyone shows this customer-order relation, which I understood clearly and already can make it by myself. The problem is that in my case I really do not have a clue how to normalise this table where there are companies related to each other... That is why I am writing on forums, hoping that someone takes a look at my table and gives me a tip/hint how to begin...

I fully agree with Hansup and CreganTur. Normalization is a very broad subject that requires a lot of "learning by doing". There are many reference texts and sites.

When I see fields identified as X-importance1, X_importance2, X_importance3 etc it tends to show that the problem is not yet understood. A big part of normalization is understanding clearly what you are trying to do.

If you start by identifying what things you have and how they are related to identify "your business rules"; then you can create some level of data model (no matter how crude it may be).

You then put your model to the test by taking each of you rules and see if the model supports it.

If not, adjust the model, until you are content that the model supports the rules you have identified.

Then design your tables based on the model. May seem like a long way around, but I assure you that you will understand the problem and the facts you are dealing with much more clearly.

Just some thoughts and observations.

Good luck.

Here are a few questions that I have:

Are you concerned with the relationship-type (owner, partner, shareholder...) ?
How do you differentiate and owner from a shareholder etc?

Can a company only have 1 owner?
How do you deal with multiple owners, if it's important?

What are you really interested in -- what will your data base do/solve?

[hansup]

Hansup: the table might have duplicate data. But that it is not important. I will delete those rows. Anyway this table is a hierarchy relation table. Actually it represents companies related to each other (by the mean of ownership; this means that the main company is in column Important_0, then there is the other company (column Important_1) which is related (owner, partner, shareholder, etc) to the main company, and then there are the other companies (important_2) which are related (owner, partner, shareholder, etc) to the second company. Now my problem is that there are more then 10.000 companies, so I cannot make a table for each one!

Still not sure how well I understand the problem you're trying to solve. So I'll suggest an approach and let you tell me why it doesn't work. :-)

I went back to your sample data, which used people names in place of company names:
John -----------> George -----------> Rob

I see that as two associations: one between John and George; and another between George and Rob.

So I created a single table, tblCompanyConnections, to hold those associations. This is the contents of that table in CSV format:
"lside","rside"
"Amber","Clark"
"George","Brad"
"George","John"
"George","Michael"
"George","Peter"
"George","Rob"
"John","Daniel"
"John","George"
"John","LLoyd"
"John","Mark"
"John","Smith"
"Mark","Denis"
"Mark","Jones"
"Smith","Amber"

Then I created qryCompanyConnections:
[vba]SELECT a.lside AS Important_0, a.rside AS Important_1, b.rside AS Important_2
FROM tblCompanyConnections AS a INNER JOIN tblCompanyConnections AS b ON a.rside = b.lside
WHERE (((b.rside)<>[a].[lside]))
ORDER BY a.lside, a.rside, b.rside;[/vba]
The result set from that query is:
"Important_0","Important_1","Important_2"
"George","John","Daniel"
"George","John","LLoyd"
"George","John","Mark"
"George","John","Smith"
"John","George","Brad"
"John","George","Michael"
"John","George","Peter"
"John","George","Rob"
"John","Mark","Denis"
"John","Mark","Jones"
"John","Smith","Amber"
"Smith","Amber","Clark"

That's the best I can do at this point. If it's not what you want, we need more details about your data model. I encourage you to carefully consider the questions orange asked.

Good luck,
Hans

[hunsnowboard]

Hi All! Thank you so much for guidance and trying help! First of all I'll try to answer Orange's questions.


"Are you concerned with the relationship-type (owner, partner, shareholder...) ?
How do you differentiate and owner from a shareholder etc?"

I do not need to differentiate between them. There is a column which shows the type of relation but for solving my problem it does not really matter. I'll explain below why.

"Can a company only have 1 owner?
How do you deal with multiple owners, if it's important?"
Yes a company can have only 1 owner, (as we deal with multiple relations: owner, shareholder, partner, etc) we have multiple type of relations so usually one company has more then relationships.

"What are you really interested in -- what will your data base do/solve?"
The main point of my database is to show the hierarchy of companies. The table shows the connections/relations (a column shows also the type of relation as well: partner, owner, shareholder) and then the relations/connections of the related/connected company. For example let's say that Mercedes Benz is the owner of Chrysler, and Chrysler is partowner of Ford. Then we can have something like
Mercedes ----> Chrysler----->Ford.
And here is a new issue. The first column is the important column, because those companies who are in the first column buyed products from our company. So if Mercedes bought from us, then we can see the following:
(first column is the company who has bought our products, second column is in some relation with our buyer, and third column is in relation with our buyers relation)

First column....Second column....Third column
Mercedes ------>Chrysler ------> Ford

Then maybe Chrysler also bought from us..then the following row is also (besides Mercedes) included in the table:
First column....Second column....Third column
Chrysler ------>Mercedes ------> /here comes a partner of Mercedes/

And then if Ford also bought from us..then the following row is also (besides Mercedes and Chrysler) included in the table:
First column....Second column....Third column
Ford ------>Chrysler ------> Mercedes

So the point of my database would be two things:
1) To make a query which shows if a company (any) is in our database and to show in which context. (if buyer then it should be in first column, if is in relation with one of our buyer then second column, if is in relation with one of our buyers relation then third column)
2) To show all those companies which are related to one of our buyer and also bought from us.

First I would like to have a solution on my first point as the second one is more complex.

Hansup: thank you for your provided solution, but please guide me where I put this VBA code??? I saw on many sites this type of format, but I do not really know where to insert that code!

[hansup]

Hansup: thank you for your provided solution, but please guide me where I put this VBA code??? I saw on many sites this type of format, but I do not really know where to insert that code!

Sorry about that. The SELECT statement can be pasted into a query if you go to the "SQL View" for the query. I misled you by applying the VBA tag to it --- you don't have to use VBA to design or run that query. The VBA tag was a poor choice on my part.

Hans

[hunsnowboard]

Hi Hansup! Thank you for your quick reply! I'll check your solution and test it, and till tomorrow evening I'll reply!

[orange]

Hi All! Thank you so much for guidance and trying help! First of all I'll try to answer Orange's questions.


"Are you concerned with the relationship-type (owner, partner, shareholder...) ?
How do you differentiate and owner from a shareholder etc?"

I do not need to differentiate between them. There is a column which shows the type of relation but for solving my problem it does not really matter. I'll explain below why.

"Can a company only have 1 owner?
How do you deal with multiple owners, if it's important?"
Yes a company can have only 1 owner, (as we deal with multiple relations: owner, shareholder, partner, etc) we have multiple type of relations so usually one company has more then relationships.

"What are you really interested in -- what will your data base do/solve?"
The main point of my database is to show the hierarchy of companies. The table shows the connections/relations (a column shows also the type of relation as well: partner, owner, shareholder) and then the relations/connections of the related/connected company. For example let's say that Mercedes Benz is the owner of Chrysler, and Chrysler is partowner of Ford. Then we can have something like
Mercedes ----> Chrysler----->Ford.
And here is a new issue. The first column is the important column, because those companies who are in the first column buyed products from our company. So if Mercedes bought from us, then we can see the following:
(first column is the company who has bought our products, second column is in some relation with our buyer, and third column is in relation with our buyers relation)

First column....Second column....Third column
Mercedes ------>Chrysler ------> Ford

Then maybe Chrysler also bought from us..then the following row is also (besides Mercedes) included in the table:
First column....Second column....Third column
Chrysler ------>Mercedes ------> /here comes a partner of Mercedes/

And then if Ford also bought from us..then the following row is also (besides Mercedes and Chrysler) included in the table:
First column....Second column....Third column
Ford ------>Chrysler ------> Mercedes

So the point of my database would be two things:
1) To make a query which shows if a company (any) is in our database and to show in which context. (if buyer then it should be in first column, if is in relation with one of our buyer then second column, if is in relation with one of our buyers relation then third column)
2) To show all those companies which are related to one of our buyer and also bought from us.

First I would like to have a solution on my first point as the second one is more complex.

Hansup: thank you for your provided solution, but please guide me where I put this VBA code??? I saw on many sites this type of format, but I do not really know where to insert that code!

Hunsnowboard,

Thanks for the explanation.
I think you a re dealing with customers, on the basis that a Customer is some entity that has bought from us.

Also, it seems that you are only interested in customer-customer relationships. Your example shows you are interested in Mercedes and Chrysler - because both Mercedes and Chrysler have bought from you.

If Chrysler had not bought from you, then you would not have Chrysler in your database?

How do you expect to maintain such Customer-Customer links? Companies sell, merge, partial mergers etc all the time. How would you keep the info current?

What does Importance (column 1 ) vs Importance (Column 2) really mean?

What are the Table names in your database?

[hunsnowboard]

Hi Orange! You seem to begin to understand the database. However I need to do some further explanation. The basic of the whole table is that only those companies have bought from our company who are in column 1! Column 2 shows the companies who are related to the company in column 1. Companies in column 3 are related to the company in column 2. So the table might have (and it has) companies which did not by from our company. Those companies who did not buy from our company are present in column 2 or 3.
We update the table 4 times a year, so if there is a change in a company, than we update it as well.


As I told you this file was an excel file, so, so far I imported the whole file to one table. I have only one table (the excel file).

Hansup! I did not manage to try your solution so far as I was very busy! I'll test it tomorrow morning and let you know about it! Thank you.
Have a nice weekend all!

[hunsnowboard]

Hi Hansup! I checked your code above and it seems to work fine. But I also have another code already adapted in the file:

Private Sub btnSearch_Click()
    txtResults.Value = ""
    Címke13.Caption = ""
    Dim hits As String
    Dim theCount As Long
    Dim column As Variant
    For Each column In Array("Important_0", "Important_1", "Important_2")
            Err.Clear
           On Error Resume Next
           theCount = DCount(column, "DataBase", column & " = " & txtNumber.Value)
           If Err.Number <> 0 Then theCount = 0
           On Error GoTo 0
           If theCount > 0 Then hits = hits & column & vbCrLf
    Next column
    If Len(hits & "") = 0 Then
            Címke13.Caption = "The number '" & txtNumber.Value & "' was not found in any of the columns."
    Else: Címke13.Caption = "The number '" & txtNumber.Value & "' that you have searched for was found in the following columns:"
        txtResults.Value = hits
    End If
End Sub

And this works also perfectly! Anyway thank you a lot for your help!

[orange]

Hi Hansup! I checked your code above and it seems to work fine. But I also have another code already adapted in the file:

Private Sub btnSearch_Click()
    txtResults.Value = ""
    Címke13.Caption = ""
    Dim hits As String
    Dim theCount As Long
    Dim column As Variant
    For Each column In Array("Important_0", "Important_1", "Important_2")
            Err.Clear
           On Error Resume Next
           theCount = DCount(column, "DataBase", column & " = " & txtNumber.Value)
           If Err.Number <> 0 Then theCount = 0
           On Error GoTo 0
           If theCount > 0 Then hits = hits & column & vbCrLf
    Next column
    If Len(hits & "") = 0 Then
            Címke13.Caption = "The number '" & txtNumber.Value & "' was not found in any of the columns."
    Else: Címke13.Caption = "The number '" & txtNumber.Value & "' that you have searched for was found in the following columns:"
        txtResults.Value = hits
    End If
End Sub

And this works also perfectly! Anyway thank you a lot for your help!

Glad that you have a siolution for your particular problem.

In the general case of companies related to companies related to companies, you may want to look at family relationship or genealogy related data bases structures/solutions. I think the underlying issue you are addressing fits a genealogy-type model.

[hansup]

And this works also perfectly! Anyway thank you a lot for your help!

Jal's btnSearch_Click() subroutine works with your original table. But in your first post in this thread, you expressed an interest in normalizing your database. Do you expect btnSearch_Click() to still work after you've normalized? Or have you decided to forget about normalizing?

Hans

[hunsnowboard]

Hi Hansup! As I would like the query to work as soon as possible I forget about normalizing. Maybe in a few months I will get to know access enough that I will be able to start normalizing the table. That would be the most ideal solution. Thank you for asking!