[Solved] Need Help Making a Query
-
- Posts: 1
- Joined: Tue Jun 26, 2018 8:38 pm
[Solved] Need Help Making a Query
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 259 times
Last edited by needhelplz on Thu Jun 28, 2018 5:33 pm, edited 1 time in total.
OpenOffice 4.0.1
Windows 10
Windows 10
-
- Volunteer
- Posts: 300
- Joined: Thu Apr 23, 2009 10:19 pm
- Location: Sydney Australia
Re: Need Help Making a Query
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.
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.
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
Re: Need Help Making a Query
"Quick and dirty", searching in filename and filetype, excluding record key and physical file location from the search:
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).
Code: Select all
SELECT * FROM "Files" where ("File Name" || "File Type") like ('%' || :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).
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Need Help Making a Query
As Keme demonstrated searching is one of the strengths of databases. There are also search functions available in SQL, for instance
would also work.
Code: Select all
SELECT * FROM "Files" WHERE LOCATE( :Enter_Search_String, "File Name" || "File Type" ) > 0
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11