Basic Query: display on form if found question.

[hunoob]

Hi there Everyone! This is my first post on this forum, and I just started learning MS Access for a few days. I know a little (very little) VBA Excel programming, I thought Access will be easy to handle but I was soooo wrong. Anyway I would like to learn, and already have an ongoing access project. If someone can help, then please try to help as I am very new and do not know the solution. I do not know how to make this query.
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". I have attached the file as well. Thank you in advance for your help!

 

[HiTechCoach]

Since you want to learn ... here goes ....

First, I would urge you to learn the correct terminology of Access and database design and how to name your objects better.

See:
Object Hungarian Notation Naming Conventions

In Access, a database is the MDB/ACCDB file that contains your objects like tables, queries, forms, reports, modules, etc.

Back End: a database that has only tables - this is what is shared with multiple users

Front End: a database with the UI and not tables. This is the forms, queries, reports, etc. It uses the tables in the back end by creating "linked" tables.

A table stores data and is not a database. You should not table your table database. Also "database" is a reserver word and should not be user to name your own objects. See: Problem names and reserved words in Access

In a table, rows are the same as records. Columns are the same as fields.
When you said:

the rows are called: "Important_0", "Important_1" and "Important_2"

that really was not accurate terminology. They really are fields or columns in a single row.


If you will learn the correct terminology, it will make search the Access Help file and the internet a lot more useful.

That brings us to the the repeating fields in your row. This does not follow the rules of database normalization. Your table is design like a spreadsheet and not a relational database.

The fields "Important_0", "Important_1" and "Important_2" should be in separate records in the same table.

See:

Description of the database normalization basics

and

Rules of Data Normalization

You current table should be at least two tables, maybe more.

Because your data is not properly normalized, it is making iit very difficult to search. You search would be very simple, a single column/field, if the database was designed using the rules of normalization and not like a spreadsheet..

Normally you do not open a table or query to the end user. You use a form or report.

Finally, check out:
Access Basics

 

[hunoob]

Hi HiTechCoach!

Thank you for your provided links and guidance. My first problem is that I knew that I have to normalize this table (which was imported from Excel) but I really do not know how to do it. Why? There are two reasons.
1)
The table provided 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........
A............. B.......... C..
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?

My second concern is about updating. As I told you the original file is an excel file, so I get the updates in Excel. Once the table is normalised in access, will I be able to update it?

Thank you for your help!

CROSS-POST: http://www.vbaexpress.com/forum/showthread.php?t=25190

 

[jal]

I added "Form1" with a simple button and textbox that runs a search on the three columns you named.

To test it, I added the value 7 to all 3 columns and then did a search for "7". It reports "Found it in all 3 columns."

Is that what you wanted?

 

[hunoob]

Hi Jal! Thank you very much for your help! This is EXACTLY what I wanted. Uhmm...however can I ask you about a small modification. What should I do if I would like the columns in the search was found to be displayed on the main form? Like for example if I search for number 7 (you have added to all columns) then it should be displayed the way you see it in the attached file on the MainForm. Please check out the MainForm and there you can see what I mean.
Thank you in advance!

 

[jal]

Hope this helps.

 

[hunoob]

Hi Jal! Thank you very much for your help! This is what I wanted!!! Thank you very much! Is there a possibility to make the frame of txtResult box invisible? I tried in the properties menu but could not find the solution.
I also have another question if you can help.... I would like to have another search option (let's call it ConnectionSearch) which checks for the connection/relations of a company. I explain it a little bit more detailed. Lets say we have a "7" number in all columns but different rows.
Example:
...........Column 1..........Column 2............Column 3
row 14 7____________13_____________24
row 52 41____________7______________11
row 75 87____________19_____________7

So if I press ConnectionSearch for number 7 this search will look for the number 7 in all of the columns and if found in any then copy the data from the other two columns. In our example I should get a list with the following numbers:
13, 24, 41, 11, 87, 19
The three relevant columns are the same: Important_0, Important_1, Important_2

Thank you very much!

 

[jal]

A basic query to do this is as follows (save this query under the name qryConnections)

SELECT Important_0 as Connection1, Important_1 as Connection2 FROM [Database]
WHERE Important_2 = @NumberToFind

UNION ALL

SELECT Important_0 as Connection1, Important_2 as Connection2 FROM [Database]
WHERE Important_1 = @NumberToFind

UNION ALL

SELECT Important_1 as Connection1, Important_2 as Connection2 FROM [Database]
WHERE Important_0 = @NumberToFind

If I have time I'll show you how to use it.

 

[hunoob]

Hi Jal! Thank you very much for your help and support! The code you have provided... I have to paste it in SQL view, is that right?

 

[jal]

Yes, and then I planned to call it in the main form and display the results there just like I did with the other result set - but I'm running into bizarre error messages. Any chance you copied, exported, or imported your form from one MDB file to another? That sort of thing causes data corruption, and usually there is no way to fix it other than to start over.

 

[jal]

Wow, that's one of the most subtle bugs I've ever seen. My code was

dictionary1.Add(rs("Connections"))

this didn't work. What worked was:


dictionary1.Add(rs("Connections").VALUE)

When you step through the code with breakpoints, it "seems" to be working. All the right values show up in the debugger for:

rs("Connectons")

but these values never got added to the dic - what apparently got added is the "column object" itself, the field called "Connections." Took me an hour and a half to figure this out. At least with ADODB, don't recall this problem, the above code always worked. Maybe DAO differs on this issue.

 

[hunoob]

HI Jal! Thank you very much for the code! This is exactly what I wanted! I run the code and it works perfectly.
Answering your question: this is a sample file. I made this file by altering the original file. (I am not allowed to show the original file because of privacy issues). I changed a few things, deleted a lot of columns and then saved it. Could this be the problem why the code was not running properly?

Can I ask you a question as I do not understand something. If I put a few rows, and duplicate a row, then why it does not display twice? I mean it is very good that it does not display the duplicate codenumber, but I just do not understand why? I checked the code and I did not find any command that says not to display anything!

Ok, and now if it is not to stiffing for you there are to more things to be done in order for this query to be complete. First the first one.
This is regarding the qryConnetions query. Now, if I run a search on a codenumber all the connections/relations related to this number are displayed. But I would like to query to display only those connections/relations/codenumbers which are found in the table in column Important_0!
Example: Lets say there are two rows where number 7 is present

........Important_0..........Important_1.............Important_2
row x___7________________4528_____________8965
row y___8965_____________9841_____________7
row z___9841_____________7________________4528

In this case the qryConnections should display only 8965 and 9841 as these codenumbers are present in column Important_0. The codenumber 4528 should not be displayed as it is not present in column Important_0 (however it is connected to codenumber 7). I hope you understand what I am trying to say.

I really do understand if you will not help me further as you already helped me a lot, and worked a lot on this! Thank you so much for this! Have a nice weekend!

 

[jal]

I changed a few things, deleted a lot of columns and then saved it. Could this be the problem why the code was not running properly?

No, I think it's running properly now, as I found the bug. Nevermind that issue.

Can I ask you a question as I do not understand something. If I put a few rows, and duplicate a row, then why it does not display twice? I mean it is very good that it does not display the duplicate codenumber, but I just do not understand why? I checked the code and I did not find any command that says not to display anything!

I used a lookkup table (a hash table) which is called a "dictionary" to prevent dups. The code checks the dictionary to see if an item was already added and, if so, does not readd it to the dictionary.

 

[jal]

Ok, and now if it is not to stiffing for you there are to more things to be done in order for this query to be complete. First the first one.
This is regarding the qryConnetions query. Now, if I run a search on a codenumber all the connections/relations related to this number are displayed. But I would like to query to display only those connections/relations/codenumbers which are found in the table in column Important_0!
Example: Lets say there are two rows where number 7 is present

........Important_0..........Important_1.............Important_2
row x___7________________4528_____________8965
row y___8965_____________9841_____________7
row z___9841_____________7________________4528

In this case the qryConnections should display only 8965 and 9841 as these codenumbers are present in column Important_0. The codenumber 4528 should not be displayed as it is not present in column Important_0 (however it is connected to codenumber 7). I hope you understand what I am trying to say.

Ok this sounds similar to the last request and therefore I can reuse some of the code I suppose. I take it you want this result to display in a third results-textbox?

 

[hunoob]

Hi Jal! Thank you for your help! Sure, you can reuse the query, and there is no need, for a third textbox. You can output it to the second one (latest). Thank you!

 

[jal]

Ok now I am confused (I am easily confused, unfortunately). This latest request seems to be a subset of the previous request and therefore would seem redundant.

That's why I suggested a third textbox as to highlight the fact that this is just a subset.

What am I missing here?

 

[jal]

Maybe you want this third requst to replace the second one, thus doing away with the second one?

 

[hunoob]

Sorry, for the misleading suggestion. What I was trying to say is that: the "third request to replace the second one". So I do not need the second one! Sorry!

 

[jal]

Ok so the new version of the query would be as follows?

SELECT Important_0 as Connection1 FROM [Database]
WHERE Important_2 = @NumberToFind

UNION ALL

SELECT Important_0 as Connection1 FROM [Database]
WHERE Important_1 = @NumberToFind

 

[jal]

Paste it into sql view, run it, and let me know if that's what you want.