[Solved] Need Help Making a Query

Creating tables and queries

[Solved] Need Help Making a Query

Postby needhelplz » Tue Jun 26, 2018 8:45 pm

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.
File Database.odb
(8.41 KiB) Downloaded 41 times
Last edited by needhelplz on Thu Jun 28, 2018 5:33 pm, edited 1 time in total.
OpenOffice 4.0.1
Windows 10
Posts: 1
Joined: Tue Jun 26, 2018 8:38 pm

Re: Need Help Making a Query

Postby mgroenescheij » Wed Jun 27, 2018 1:46 am

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.
Posts: 300
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: Need Help Making a Query

Postby keme » Wed Jun 27, 2018 11:14 am

"Quick and dirty", searching in filename and filetype, excluding record key and physical file location from the search:
Code: Select all   Expand viewCollapse view
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).
User avatar
Posts: 3138
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Need Help Making a Query

Postby UnklDonald418 » Wed Jun 27, 2018 3:43 pm

As Keme demonstrated searching is one of the strengths of databases. There are also search functions available in SQL, for instance
Code: Select all   Expand viewCollapse view
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.6 & LibreOffice - Windows 10 Professional
Posts: 1122
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest