Creating search database in access 1

[Kevinmalcolm]

Basically I'm illiterate in terms of Access, and was wondering if anyone could help me out a little. I am trying to make a database that allows you to search for something like a first name for example, and it will show you everyone with that first name. If anyone could give me some basic help on this or needs me to elaborate further please tell me.

 

[vistor]

Sounds like you need to get a good Access book, search Amazon.com for one. Take a class at a local community college / try an online tutorial:

http://polaris.umuc.edu/~acarswel/ADMN640/AccessTutorial.html

1. Create a table with the required fields. Example:
FirstName, MiddleName, LastName, Address, City, State, Zip, Phone, Email.

2. Create a query based on this table. In the criteria row of the query for the field you want to search on you would put a parameter like this [Search by last name:]

 

[Kevinmalcolm]

Alrigth I did that and it worked quite well, but do you know how I could change it to make it search by more than one of the search criteria. What I mean is so that you could search by First Name and Last Name at the same time.

 

[fpdkris]

Continue as per CDWD's earlier advice. For each field you'd also like to use as a search criteria add an additional bracketed parameter in the criteria row. For example under first name [Enter First name] and street [Enter street where your subject lives]. Access will use all parameter fields in the same row as an AND set of conditional statements and evaluate then as all needing to match your answers to all questions.

Enter First Name: Tom Enter Street where subject lives: Adams Street
Will return all database rows where a Tom lives on Adams Street or to phrase this differently where each first name field = Tom and each street name = Adams street.

 

[vistor]

Sorry I've not answered before - been out out the forum for awhile.

If you want the user to search by two fields such as first and last name you have to concatenate (join) the two separate fields from the tables in your query. You do this by creating a new column in your query called Name, you are NOT going to pull the FirstName and LastName fields into your query.

Put this in the Field row of the column:
NAME: [FIRST NAME] & " " & [LAST NAME]

Table row of the column will be blank

Put this in the Criteria row of the column:
[Enter the Name You are Searching For:]

 

[Kevinmalcolm]

Thanks for your help, but I still have one minor problem. I cannot use only 2 or 3 of the search criteria at one given time. I must always use the same ones.

Let's say my fields are a)First Name b)Middle Initial c)Last Name

If I didn't know this specific persons middle initial how would I make it skip this step. Right now if I just hit "ok", and pass by it then I won't get any results.

 

[vistor]

If you want to search on part of a field, i.e. you are not sure how to spell the person's name but know the last name starts with Cru; then you need a Like query. The search will then bring up all people who have Cru in their name, if it is in the first, middle or last.

Same concept for Order ID, City, Zip Code search, whatever you need.

Place this in the Criteria row:
Like "*" & [Persons Name:] & "*"

CDWD

 

[Basia]

Hi. Usually for this type of search I use the Filter by Form option. It allows me to use any of the fields on the screen as well as and/or functionality.

HTH...Basia

 

[Kevinmalcolm]

Thank you all very much for your help. My database is working fine, and I'm very grateful that you spent some of your time to help me out. Hopefully the next time I come back here it will be to help other people instead of irritate you guys. Well, thanks again.

See you later.