Desperate to know how to write this quiry string

[richiwatts]

Desperate to know how to write this query string

I want to have 4 search questions to search my database and they are:

language from
language to
area
name

When I do a search I want to be able to use just one of the search boxes or narrow the search results by using more than one. e.g find evryone in the database that matches language from (English) and area (Financial).

My problem is that in my database I have 3 columns/fields for language from, 2 columns/fields for language to and 8 columns/fileds for areas. So how do I write a quiry that makes sure that if I type in a search it checks as follows.

language from = Searches (languagefrom1,languagefrom2,languagefrom3) for a match.

language to = Searches (languageto1,languageto2) for a match.

area = Searches (area1, area2 through to area8) for a match.

name = Search under name field

Does this make sense?

Thank you in advance for any help?

 

[Rich]

I 'd say you would better normalising your data first, search here for posts on the subject. Due to language differences, you might have to search for normalize, you should also read some of the posts by Pat Hartman on the subject.

 

[richiwatts]

Does that mean it can't be done the way i have mentioned above? To be honest, I don't know the first thing about Access. The only reason I have had to come into contact with it is because I have a FORM on my website that collects information from visitor. I can then do a basic search and retrieve the data i want (all in my website).

The thing is that I have used FrontPage 2000 to set this up. Basically it does everything for you. I didn't even need to open Access to set this up.

I have used a results wizard in FrontPage to set up a quiry but it doesn't seem to handle complicated quiries like I have mentioned above. However, it does have a section where i can just paste in a quiry created in Access.

So do you think I still need to change the database or is there a quiry that can do what I need?

 

[Rich]

I've no knowledge of Front Page, so I can't help on that, but I'm sure John K answered something very similar just recently, try searching by the members name.
Sorry should be Jon K

 

[richiwatts]

OK, I have spent the day reading through tutorial after tutorial and I think I have got myself into more of a mess and a headache is now coming on. I feel like I came here wanting help with one problem and now i have two "getting the database right"

I agree that normalising is the best way to go, ecspecially since I will have so much repetative data in my database.

My problem is I don't know where to start to get this right.
The form where people can enter the data into the database is here: http://www.english-partner.com/register.asp

I would be grateful if someone could have a look at the form and advice me with the best possible solution to deal with all this.

Help!...

 

[antomack]

The attached image shows a query which will search through three fields for where one of the fields matches the entered language. Thus if you enter 'English' it would find all records where 'English' is in any one of the three fields.

You could work on it further to do the rest of the search with 'language to' and 'area' being done in the same way.

The query uses the In operator within the criteria to do the check.

 

[richiwatts]

I get error "invalid syntax" when I try to come out of "criteria" box

 

[antomack]

Make sure each of the field names has an opening an closing '[' as in
[field1]
that there is a comma between each field name as in
[field1],[field2]
and that there is an opening '(' and a closing ')'

 

[richiwatts]

In ([tanslatefrom1],[translatefrom2],[translatefrom3])

If you want to e-mail me with it richiwatts@hotmail.com

This is what i have and I can't come out of the criteria

 

[antomack]

Remove the space(s) after the In;

Try
In([Tanslatefrom1],[translatefrom2],[translatefrom3])

Also are you missing an 'r' in the first field name.

 

[richiwatts]

Still no good

I have removed the space and added the r

 

[antomack]

I can't see what the problem is. What version of Access areyou using, I'm doing the test query in 97 and it works fine on the sample I've tried. I've attached the sample I have working if you want to check it. I'm afraid that other than that I'm stumped at the moment as to why it's not working for you?

 

[richiwatts]

OK that work once I opened it and it converted it to Access 2000. However this is how it looked:

In ([TranslateFrom1];[TranslateFrom2];[TranslateFrom3])

Now I just need to play around with it.

Thanks again

 

[antomack]

It looks like they've changed the syntax of In from A97 to A2000.