I have a spreadsheet that extracts text and numbers from two other sheets and can display images to go along with the text. Both the text and images to the spreadsheet works as expected.
The problem is if the database image table cell is blank the image process stops working.
I am using a list box to get a number for the images, and a refresh form button to get the information from the database. That value is seen in a single number filter table. The image control box on the spreadsheet uses an SQL query to a Filter Table and extracts the image number from a database table for the information required to fill the image boxes.
Currently every blank table cell has been populated with 1 and everything works as expected.
I am trying to understand why blank cells break the form refresh button, and prevent any images to display, otherwise everything is working as expected when no cells are empty.
I am looking for suggestions or a test to find how to work with blank cells in the database table.
Put three image files in a P_Photos folder folder in the same folder as the spreadsheet and database. Name them 02.jpg 03.jpg 04.jpg
- SQL help request.ods
- (51.78 KiB) Downloaded 155 times
- Image database
- (90.86 KiB) Downloaded 157 times
Reason: tagged solved.
on my mobile I can't check your database-files, but just the description reminds me of several places, where I had to take care of the special value NULL.
Instead of SELECT value ...
I use SELECT IFNULL(value, 1)...
to achive the effect you described for filling the
empty cells with 1.
There are other ways like LEFT JOIN instead of JOIN
around the problem with NULL - depends on the use case..
Yes, any operation with a Null value returns Null. COALESCE is the function which can handle this.
, "C1"."Photo" AS "Photo01Display"
, "C2"."Photo" AS "Photo02Display"
, "C3"."Photo" AS "Photo03Display"
, "C4"."Photo" AS "Photo04Display"
, "C5"."Photo" AS "Photo05Display"
FROM "tbl_FILTER", "Photo_Image"
, "Photo_Image" AS "C1"
, "Photo_Image" AS "C2"
, "Photo_Image" AS "C3"
, "Photo_Image" AS "C4"
, "Photo_Image" AS "C5"
WHERE "tbl_FILTER"."F_ImageID" = "ImageID"
AND "Photo_Image"."Photo01" = "C1"."ImageID"
AND "Photo_Image"."Photo02" = "C2"."ImageID"
AND "Photo_Image"."Photo03" = "C3"."ImageID"
AND "Photo_Image"."Photo04" = "C4"."ImageID"
AND "Photo_Image"."Photo05" = "C5"."ImageID"
"The queried table is not normalized. It has fields like Photo01, Photo02, Photo03 indicating a many-to-many relation." Yet in my WHERE "tbl_FILTER"."F_ImageID" = "ImageID", the filter passes a single value to "ImageID" and the Photo01, Photo02, Photo03 are many from that one ImageID.
I like most people are struggling to understand a database. Where could I find a good basic source to understand Normalized.
Where can I find COALESCE as it doesn't show up in the Help section of open office or in the query how would I include it?
https://docs.microsoft.com/en-us/office ... escription
This is a many-to-many relation.
It requires a flat list of images, a flat list of photos and a third table mapping images to photos.
Having a table "Photos" where each row describes one distinct photo plus an auto-incrementing "ID" column and
having a table "Images" where each row describes one distinct image plus an auto-incrementing "ID" column,
the following statement produces the linking table without many clicks
Code: Select all
CREATE TABLE "Photos_Images"( "PH_ID" INTEGER NOT NULL, "IMG_ID" INTEGER NOT NULL, PRIMARY KEY("PH_ID","IMG_ID"), FOREIGN KEY("PH_ID") REFERENCES "Photos"("ID"), FOREIGN KEY("IMG_ID") REFERENCES "Images"("ID"))
Now you see the many-to-many relation in the relations window.
The linking table consists of 2 integer columns, both columns constitute the primary key (no duplicates possible), each of them references the primary key of another table (no orphans possible). This way each image entry in this table belongs to some existing photo only once and each photo entry belongs to some existing image only once.
This table is not editable directly unless you know all the ID numbers by heart. This is where input forms come into play. A combination of subforms, table controls and listboxes makes many-to-many relations editable.
In the Base examples section of this forum you find plenty of examples with many-to-many relations and forms. I made a very simple one for educational purpose only: [Example] Relations reflected by list boxes in forms. The relation between persons and things is many-to-many. The relation between animals and persons is one-to-many (each person has one animal).
In your database you may have 2 forms then:
Form #1 assignes photos to a selected image: Images in main form --> mapping table in subform with table control ---> photos selectable from listbox in subform's table control
Form #2 assignes images to a selected photo: Photos in main form --> mapping table in subform with table control ---> images selectable from listbox in subform's table control
The problem might have been the attached files I provided didn't fully represent what my major project was, but to try to show the problem it took me about three days to put together the attached files.
My major project was and still is a Family tree, that you earlier said I needed to do it with a database. I spent many hours trying to understand and make a database, finally sticking with the spreadsheet I had, that now has about 500 people and 200 families and produces all the output sheets I want with or without photos.
The reason for again attempting a data base was to add pictures or not to the output pages if I wanted them, which against all odds I was able to achieve. The pictures reside on my computer in the same folder as the spreadsheet and database, images in a folder, one for each person.
The only time a picture is called for more than once is when a person has multipul marriages or is a step child, but those conditions don't end up on the same print out.
The data base I have is working with has a single information table, which I am going to try to normalize as I see given names and surnames can repeat. Although with my spreadsheet adding the same name over and over is just one of those things that happens and is necessary.
My big challenge with the Data base is when requesting a child's ID number from a family so I can get the photo location information (text reference - P_Person/###_photo_name.jpg). If any of the fields in that row (up to 16 children - recently updated to 20) is blank the query fails and no images are returned. The work around was to fill each field with 1 so no field is empty and everything works as expected.
The issue of putting a family number into a cell on the spreadsheet and pressing return to get the text data from the spreadsheet is a normal operation to tell the spreadsheet to get do all the formulas on the page, and all the cells are populated from the People and Family sheets.
To get pictures, on the spreadsheet I use a dropdown box to select a family number, with that number selected the form refresh button on the sheet is pressed, which sends the family number to the database and the query uses it to extract the data from the table and in turn puts pictures in the containers on the spread sheet. From what I have read the only way to over come this is with a macro (yet another steep learning curve) or accept just another button press.
I would like to solve the blank field problem that prevents pictures being displayed.
a well structured database is the correct tool to use for your project, the time & effort required will be rewarded many times over.
you have a spreadsheet not a database & with that comes a spreadsheet mentality.
i am not overjoyed in proposing a solution to your issue because i would prefer to discourage rather than encourage your continued use of that spreadsheet.
using AND in the WHERE clause acts as a filter, if the expression returns false then the entire record is discarded.
LEFT JOIN retains all previously selected records while adding the newly selected fields only where the join proves true.
Code: Select all
select p."ImageID", p."Photo01", p."Photo02", p."Photo03", p."Photo04", p."Photo05", p."PersonID", p."Photo", p1."Photo" "Photo01Display", p2."Photo" "Photo02Display", p3."Photo" "Photo03Display", p4."Photo" "Photo04Display", p5."Photo" "Photo05Display" from "Photo_Image" p left join (select "ImageID", "Photo" from "Photo_Image") p1 on p."Photo01" = p1."ImageID" left join (select "ImageID", "Photo" from "Photo_Image") p2 on p."Photo02" = p2."ImageID" left join (select "ImageID", "Photo" from "Photo_Image") p3 on p."Photo03" = p3."ImageID" left join (select "ImageID", "Photo" from "Photo_Image") p4 on p."Photo04" = p4."ImageID" left join (select "ImageID", "Photo" from "Photo_Image") p5 on p."Photo05" = p5."ImageID" where "ImageID" = (select "F_ImageID" from "tbl_FILTER" where ID_F = 1)
I started with a spreadsheet mostly because I could make things work, where even today I struggle to have a database provide even basic results. It is like most things in life, we struggle in the beginning, improve over time and finally become very proficient.
I am going to tell you I am amazed at what you provided for me, I can see some of it that makes sense. I took what you provided, put it into the example I posted in the beginning. I took some number out of the image table, and were able to produce the output expected to the posted spreadsheet. My main project spreadsheet output I think would be similar to a database report.
I don't disagree that a well structured database is the correct tool for my project. The more experience and understanding I get might some day have me working with a database. For now my main project a family tree, has grown from a tree to a little forest, with no thought of making it much bigger. More people like me find working with spreadsheets easier than what appears more complex data bases.
Without photos my family tree is half a dozen display pages (like custom reports) that only require a family number or person number (I display lots of reference numbers and drop down lists for better navigation) to populate all the available cells with the extracted data from the two data collection sheets. I even converted it to Excel for my brother who lives in a different town.
Now I will integrate your solution into my main project, I with your help I would say this is solved. Which I will do in a couple of days, just in case someone has a question or suggestion.