Search Form Query

[xluserg]

Hi all

New to Access 2010

Say I've a table called "Test" with 3 fields, Primary key, Number and Name. I need to search for a name "Tom" in a form with 2 unbound text boxes I search for Tom and it would get all the records for Tom, I can do that. How would I search for Tom and get all the other records that have the same "Number" Record has Tom. I just can't get my head around this.

example:

0001 Tom
0001 Jim
0001 Bob
0005 Bill
0005 Bob
0005 Tom
0040 Ted
0040 Bob
0040 Tom
0040 Carl

and is there away to highlight Tom for better viewing, sorry for this extra I've been using excel for to long.

Hope I've explained ok.

Thanks in advance

Graham

 

[s.ridd]

Not in front of a copy of Access, but an example of some SQL might be something like

SELECT t1.Number, t1.Name
FROM Test t1 INNER JOIN (SELECT Number FROM Test WHERE Name = "Tom") AS t2 ON t1.Number = t2.Number

although I suspect Access won't let you have a column name called "Name".

Hope this helps

Simon

 

[xluserg]

Hi Simon

Your correct Access won't let me have a column called "Name" so I give the column a heading of "PerName" I'll make sure next time to create the file first instead of explaining what I'd like before I have the file.
Your example of SQL works fine and is very useful. I much appreciate your help.

Is it not possible with a seach, using a text box on a form and criteria in a query?

Thanks Simon

Ragards

Graham

 

[s.ridd]

Hi Graham,

Yes, you should be able to just change your SQL to something like the below

SELECT t1.Number, t1.PerNameFROM Test AS t1 INNER JOIN (SELECT Number FROM Table2 WHERE PerName=Forms!YourForm!YourTextBox)  AS t2 ON t1.Number = t2.Number

replacing YourForm!YourTextBox with the actual reference.

Let me know how you get on

Simon

 

[xluserg]

Hi Simon

It works fiine.

SELECT t1.Number, t1.PerName
FROM Test AS t1 INNER JOIN (SELECT Number FROM test WHERE PerName=Forms!Form1!YourTextBox) AS t2 ON t1.Number = t2.Number;

I am looking into SQL as this will be very useful in the future.

Thanks very much for your help.

All the best

Graham