OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

query search criteria - Solved

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
nathstevenson
Newbie
Newbie


Joined: 25 Apr 2012
Posts: 4
PostPosted: Wed Apr 25, 2012 6:05 am    Post subject: query search criteria - Solved Reply with quote
Hi

I am making a database to find filters for sensors based upon the gas to be detected. I have three tables

Gas - Gas Id, Gas Name, Half power bandwidth, centre wavelength
Filter - Filter Id, Filter name, Half power bandwidth minimum, Half power bandwidth maximum, centre wavelength minimum, centre wavelength maximum
Comparison - Comparison Id, Gas id, Filter Id (I don't know if this table is needed)

I want a query that allows the user to put in a gas (or select one in a form) and for the query to take the information in the gas table (Half power bandwidth, centre wavelength) and to find a filter(s) based upon the the Half power bandwidth and centre wavelength fitting within the Half power bandwidth minimum, Half power bandwidth maximum and centre wavelength minimum, centre wavelength maximum stored in the filter table

I know that the criterion will include <= for the minimums and => for the maximums but I am unsure how to make the query compare it to another records field

Thanks in advance to anyone who can help

Last edited by nathstevenson on Thu Apr 26, 2012 1:28 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2473
Location: 3rd Rock From The Sun
PostPosted: Wed Apr 25, 2012 6:51 am    Post subject: Reply with quote
Based ( database pun intended Smile ) on your description above, I suspect something like the following will meet your needs. Of course, you will have to confirm I have entered each table and field name EXACTLY as you entered them in your database table ( that is the CASE ( UPPER / Mixed / lower ) are entered correctly. I took them from what you entered above . . . but . . . you will have to confirm you wrote them correctly, and, I did the same.

Code:
Select
   "Gas"."Gas Name",
   "Filter"."Filter Id",
   "Filter"."Filter name"

From "Gas",
     "Filter",
     "Comparison"
     
Where "Gas"."Gas Name" = :Enter_Gas_Name
  and "Comparison"."Gas Id" = "Gas"."Gas Id"
  and "Comparison"."Filter Id" = "Filter"."Filter Id"
  and "Gas"."Half power bandwidth" BETWEEN "Filter"."Half power bandwidth minimum" and "Filter"."Half power bandwidth maximum"
  and "Gas"."centre wavelength" BETWEEN "Filter"."centre wavelength minimum" and "Filter"."centre wavelength maximum"


Explanation:
  1. The above will return the data in the Select portion, I am not certain exactly which fields you wanted to return, but, I made a guess.

  2. The user is prompted to input the desired "Gas Name". This is 'defined' from the 'code': Where "Gas"."Gas Name" = :Enter_Gas_Name . The colon ( : )is used to indicate a user prompt. The characters afterwards contains no spaces, so, I used an underscore to simulate words.

  3. The use of BETWEEN in the Where clause ( two times per your description above ) is used to determine the 'range' of values.

  4. You can 'test' this Query by following these steps:
    1. Open your OpenOffice Base file ( *.odb )
    2. On the left, click on the Queries icon
    3. Under Tasks, click on: Create Query in SQL View
    4. Copy and paste the above Query code
    5. Run the Query ( F5 or Run Query icon on toolbar )
    6. Save the Query
    7. Smile and say: "Gee Sliderule, that was easy. Now, all I have to do is mark the forum post as [Solved]". Smile
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
nathstevenson
Newbie
Newbie


Joined: 25 Apr 2012
Posts: 4
PostPosted: Thu Apr 26, 2012 1:29 am    Post subject: thank you very much Reply with quote
Thank you very much your solution worked once I had all of my field titles right.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group