[Solved] Need Help Making a Query

Creating tables and queries
Post Reply
needhelplz
Posts: 1
Joined: Tue Jun 26, 2018 8:38 pm

[Solved] Need Help Making a Query

Post by needhelplz »

I have a table recording documents used for general inventory and finding the documents (linked below). My boss thinks this is not good enough and wants 3 different tables. He wants to be able to search a keyword into a query and find what he is looking for. Like "Meadows" and all the files with the word "Meadows" shows up along with the file type and location. It would mean a lot if you would help. Thank you.
Attachments
File Database.odb
(8.41 KiB) Downloaded 244 times
Last edited by needhelplz on Thu Jun 28, 2018 5:33 pm, edited 1 time in total.
OpenOffice 4.0.1
Windows 10
mgroenescheij
Volunteer
Posts: 300
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: Need Help Making a Query

Post by mgroenescheij »

Has your boss also told you why he want's three tables?
Meadow is not a keyword it's just a word in your table.
What you have made is much easier to put in a spreadsheet where you can use the standard filter to search for words.
AOO 4.1.5 on MS Windows 10 Professional & MacOS High Sierra 10.13.5
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Need Help Making a Query

Post by keme »

"Quick and dirty", searching in filename and filetype, excluding record key and physical file location from the search:

Code: Select all

SELECT * FROM "Files" where ("File Name" || "File Type") like ('%' || :search_term || '%');
A cleaner approach would be to test the fields individually, but the above is faster on some platforms. It will also match records where the search term "crosses the border", as it were. (Where file name ends with "...Mead" and File type starts with "ows...", using your example search term.)

Note that if your database is on a different SQL platform (moving from "embedded data" to a separate server), the SQL syntax may be slightly different (use of single vs. double quotes, input variable prompt).
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Need Help Making a Query

Post by UnklDonald418 »

As Keme demonstrated searching is one of the strengths of databases. There are also search functions available in SQL, for instance

Code: Select all

SELECT * FROM "Files" WHERE LOCATE( :Enter_Search_String, "File Name" || "File Type" ) > 0
would also work.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply