Create Access Database using SQL queries

[nelly_1005]

Hey guys,

Basically i want to create a database in Access 2002 that will hold names of functions of a mobile phone. When the user enters a search the databse should query the information held within it, display the appropriate search results.

It should be able to serach for precise function names entered such as Write Message,Inbox, Message Settings, Clear Cache memory.

Partial words such as WRI would display WRITE messgae, Write email, TEmplate, and INB would display INBOX,and also ambigiuos words such as message the results should be Inbox, Outbox, saved messages etc. all words that are related to that catergory.

I am slighlty confused as to how i go about making the different tables as i have never used Access before. Should i create different tables for each main function such as Messgaes and list all function names as records within the table?? or have a table for all ambigiuos words and anoterh for partial?? and how it should be queried using SQL.

If you could help me out and lead me to a right direction i would be grateful!!

THANKS! :lol:

[Zachery]

I'm no access wizzard, any reason you want to use access?

Realisticly, its got about a 20 user threshhold for multiple connections.

So if you are planning somthing big you might want to look into MSSQL PGSQL or MySQL

[autocrat]

To do the sort of thing you wnat, you will have to design the DB in one of the following fashions...

1) keyword tables.... have tables with the correct word/term....then have fields for all the additional alternatives and abbrv.
2) generate standard tables with the keywards in, then ensure that you have strong queries that contain all the variants and abbrv.

I'd actually suggest both.

[VAIBHAV5107]

Hi there,

You can use LIKE sql pattern matches in your embedded sql code of any HL Language.
There are two operators used with LIKE keyword. They are ' % ' ( for any no of characters after the character pattern) and ' _ ' (for a number of characters after the pattern).

For eg.

To search names partially in a table, its Vb code would look like this:

' other code as required
' ----------
wname=text1.text
rescordset.open "select name from tablename where name like '" & wname & "%'", connection_name,adOpenDynamic,adLockOptimistic,adCmdText
' or you can use this syntax
' rs is recordset name and cn is connection name
set rs=cn.Execute("select wname from tablename where name like '" & wname & "%'")")

It will 110% work I have used it.
Enjoy & Hav Fun ...................

[suvarnam89]

Actually we can not create database with the help of SQL query.
So we must to first create database using Wizards. Then using SQL query you can create table.And also you can execute other all function using SQL query.

[ChrisPadgham]

Actually in Access 2002 I think you will need to use * as your wildcard not %

As mentioned above by autocrat you first need to establish your data model. Probably something simple with two tables the first containing a list of phone functions and a description field. The second table contains two columns, the first is a list of keywords you want to search on and the second is the phone function to which the keyword relates.

Then put a text box on a form that allows the person to enter a search string, a button for the user to click to perform the search and a list box to display the results of the search.

The rowsource of the listbox will be something like

SELECT Key_Word, Phone_Function FROM tblKeyWords WHERE Key_Word='*" & text1.text & "*'"

under the button click event put

List1.requery