Database "find" function?

willhub · 27 Jan 2010 at 14:20

Hi.

I'm making a database for my assignment. It's a database to log products and then customer orders, the thing is, if you've got a very large database, to find these orders, I need a quicker way than scrolling down, so perhaps typing in the product ID or the name of the product could bring it up?

I'm unsure if the built in function on Access can do this, I clicked on find but it did not work, just said what I typed in could not be found even though I am positive it was in there.

So is what I am asking something I have to make using SQL or perhaps a query?

Thanks
Will.

redeye_uk · 27 Jan 2010 at 14:31

I don't use Access, but you should just be able to write a query.

My understanding is you want to return customer orders for a given productID? In which case something like this will do that...

SELECT * FROM Orders o
WHERE o.ProductID = @ID;

Yakyb · 27 Jan 2010 at 14:34

you should just be able to use the built in query designer to do this

willhub · 27 Jan 2010 at 14:44

Well, I'm looking through a long list of products on a form that has retrieved them from a products table.

Imagine if there was masses of products, I want to find one particular product, for example, HDF4870, I can't find it, so I want a search/find function and type in say "HD", and it brings up all products that start with "HD", the "Find" function in the toolbar does not seem to do this.

redeye_uk · 27 Jan 2010 at 14:49

SELECT * FROM Products p
WHERE Lower(COALESCE(p.Name,'')) LIKE Lower(@SearchPhrase)

willhub · 27 Jan 2010 at 15:09

See image, would it be possible to even as you type something in, a drop down box appears with products from the product table when starting typing in the "Product ID" field?