Access database query setup 3

[mvb00]

Could someone please help me. I am attempting to help a friend setup a database (using access) for creating a searchable table of their clients.

What is the most efficient way to set up query which will create a unique listing of records? For example, I'd like to find how many size six green apples with broken stems shipped between June 1st and June 11th?

I hope this is clear enough. Thank you.

 

[lespaul]

SELECT APPLES, COUNT(APPLES) FROM TABLENAME WHERE SIZE = SIX AND COLOR = GREEN AND STEMBROKEN = TRUE WHERE SHIPDATE BETWEEN #06/01/2004# AND #06/11/2004# GROUP BY APPLES



Leslie

 

[mvb00]

If my fields consist of a date, age, race, ethnicity, behavior, councilor and location (for example)......How would I create a simple interface in order to search any of the existing fields of information within the table. Would I have to predefine each possible combination of search? Is there a way to create an interface more like Filemaker (for example)......Some type of form in which one could submit a search based on entering it into the appropriate field. Is there a way to create a "modify last find" in order to trim the results of a query. I hope this isn't too much to ask. Any followup will be greatly appreciated. Thank you

 

[lespaul]

Sorry haven't seen Filemaker, so I don't know about that. It would depend on what you are searching for. If the user wants to query on ethnicity, are there different values, does he want to select the values to search for or just return all the records where it's not blank?

Do you want to allow the user to enter a single criteria or multiple criteria? For instance, search on age, race and location.

No, you don't have to predefine each search, but you will have to create the query dynamically in code when the user selects all the criteria.

HTH

leslie

 

[mvb00]

There are different values for ethnicity. It could consist of Asian, Black, Caucasion, Hispanic so on and so forth. I would like to allow the user the ability to search for multiple criteria. An example of this could be allowing a search for all database entries from April 5, 2003 to April 7 2004 who are Asian, between the ages of 17 and 23, who are female. This is just one example but I would like to create the ability to for any possible combination of fields to be searchable at a given time.

I am a novice user and very much appreciate the help. Thank you.

 

[lespaul]

Well, that's what we're here for is to help!

First off, is your database designed to third normal form? Have you read 'The Fundamentals of Relational Database Design'?

Here's what I would do:

Create a form that has all of your options on it with check boxes for the user to select the criteria. Once a check box has been filled the show the text boxes or whatever to select or fill in the values to search for:


[]Ethnicity
[]Sex []Male []Female (hidden until []Sex is checked)
[]Date []Single Date []Date Range (hidden until []Date is checked - once a selection is made on single or range, then show the appropriate boxes to enter the date(s))

If the user chooses sex, then in the checkbox event I would change the visible property of the Male/Female section.

continue for all your search choices and have a command button on the form that when pressed takes all the choices and builds the query.







Leslie

 

[mvb00]

I will read the article and attempt your advice. Cheers.

 

[willir]

mvb00

As an alternative approach, I had a similar problem for a meter reading database - simple and flexible.

I setup my meter reading table in as a subform, and embedded this into an unbound main form. This gave me tremendous flexibility in retrieving records by using the

    Me.reading_sbfrm.Form.RecordSource = strSQL1
    Me.reading_sbfrm.Requery

The main form had a combination of combo boxes, command buttons and unbound text fields. I could retrieve a specific meter, group of meters, commodity (gas / water / electricity) for a specific date, date range or what ever.

The trick is to build your SELECT statement based on the options chosen by the end user.

To tackle this problem, I broke the query into it's separate parts, and then concatentated them at the end...

strWhere1 = "where " & strWhere2
strSQL1 = strSelect & strFrom & strWhere1 & strOrderBy

I hope this gives you some ideas.

Richard

 

[mvb00]

Could you provide a little more info about the select statement? I am not very familiar with SQL and have learned a tremendous amount in the last 36 hours. Please be a little more specific in what is happening in the example of code you provided. Much obliged.

 

[mvb00]

Yes, my database is designed in third normal form. With the system of checkboxes you recommend, would the option of [male] or [female] be part of another form/query called by the main search interface? Without a knowledge of SQL it seems that setting up this list of choices which than calls a query of the database from the current population of the text boxes is not within my grasp. Would the text boxes need to be unbound? How do you create hidden input boxes? I think the architecture of your suggestion is great, though I am having trouble building it. Oh, one more thing....how is a standard command button used to submit all of the content of multiple fields within the search interface? Thanks

 

[willir]

mvb00

In the following post, I go an example of creating an SQL statement used for a requery.

http://www.tek-tips.com/viewthread.cfm?SQID=849726&SPID=702&newpid=702&page=1

In SQL, you have four components...
- SELECT clause - what you are selecting
- FROM clause - where the data is coming from
- WHERE clause - conditions or filters for the data
- ORDER BY - controls the order in which the information is presented

- There is also GROUP BY clause which is used for statistical purposes (sum, count)

The WHERE clause is the most difficult, and key to understanding relationships between tables.

Richard