I want to create a report that I can use to ask my club members to update their details and fill in any missing details. I have data in at least three tables linked with one to many relationships. My attempts have only been able to retrieve data only if the fields contain data. I need to fill in missing data.
Create a query from the tables and with the fields you need.
The ID of the tables should be included in any case.
Take the query as the basis for your report.
Otherwise, anonymize the DB and upload it, so we can see if there is a problem.
Call the form assistant
For “Table and Queries”, select a table or query.
Select the desired fields:
With “Tables and Queries” you can change as you like.
Of course, the required fields from tables or queries must make sense or must be connected.
If you are a beginner, check the documentation as well:
https://documentation.libreoffice.org/en/english-documentation/
Or check out some useful videos here:
https://wiki.documentfoundation.org/Video_Tutorials/de#Base_-_Tipps_und_Tricks
Sorry, only in german.
I have added a sample database and a dummy form that I would like to use to enable the members to update their data.
Sorry I cannot seem to find a way to add the sample files. Can you tell me how please?
Sorry for the german UI.
I appreciate your help. I can clearly see what you intend to show.
Hello,
This question is difficult to provide a direct answer to. It lacks key information such as the which fields from which tables & how these tables are related. I partially agree with the answer by @Hrbrgr but it seems another part of the query is going to require joining portions of tables to one another. There are a variety of JOIN methods available. You can find some info on this here → SQL Joins.
Although there are no table details in this post → Base Query Only Showing Records Where Each Field Has Values, my answer does show the SQL for a very similar situation.
If further help is needed, more specifics must be provided.
Edit 2018-07-24:
Unfortunately I don’t believe this will help much since I have already given you the answer you need above - same as the linked answer. The problem you are having (with this function) is you must join the records properly to produce empty fields where no data exits already. Using your tables and an small portion of the data you eventually want, here is how it is done:
Now this is only joining two of the three tables together so another left join must be done there along with adding ALL the fields you many want to include within the report. Again, same as the already provided example but with your data.
Also, you are not very careful with what you are doing. Notice the name of the Members table - you have placed a space at the end of the name. This makes debugging that much more difficult. Briefly looking at the data, it appears this may not be structured in the best manner. You only allow for one contact & one doctor - no room for growth.
You also left all the analysis up to us answering questions. What tables were you using? How the intended process was structured (still not 100% sure of this). You did not even provide any example on how you had tried to do this yourself even when you were given the answer. Please be a little more considerate with future questions in providing information & not leaving it up for guessing.
Thank you for your help. I have been successful in getting the data I needed from three tables in a single query. Now to generate the desired report.
The SQL statement looks like this.
SELECT * FROM ( SELECT “Surname”, “Given_Name”, “DoctorID”, “ContactID” FROM “Members” ) “A” LEFT JOIN ( SELECT “DoctorID”, “Title”, “Initial”, “Surname” FROM “MedicalContacts” ) “B” ON “A”.“DoctorID” = “B”.“DoctorID” LEFT JOIN ( SELECT “ContactID”, “Given Name”, “Surname” FROM “Contacts” ) “C” ON “A”."ContactID
Your statement is good except for the end portion was cut off due to space limitation ( end is "C" ON "A"."ContactID" = C."ContactID"). Also, it would seem your selection has some unnecessary data and missing info. Instead of “Select *” select the fields you need (SELECT “Surname”, “Given_Name”…) from the joins avoiding multiple Doctor & Contact ID’s (don’t see need for these at all). Also include “MemberID” and use that in the report for grouping with a new page forced after each group.
Sorry for the lateness in replying to you for your help. I have been very successful in preparing the reports based on your help. Another man has joined our Men’s Shed and he has spent a good deal of his career working with SQL servers and is extremely well versed in SQL statements.
I have added a sample database and a dummy form that I would like to use to enable the members to update their data. Sorry I cannot seem to find a way to add the sample files. Can you tell me how please?Camelot.odbCAMELOT MEN’s SHED Membership Uodate Form.odt
@twhelan_au First, do not use answers to add further info to question. EDIT your original question instead with the additional info & note it as edited. Answers are for responding to original question.
Don’t know what you mean by “… cannot add sample files” as I see you have done so here.
Your further info makes your original question confusing. I don’t see where this has anything to do with a “Report”.
Are you expecting to give these members a Writer document (supplied sample is NOT a Base form) to fill in and then expect that data to be connected to Base? Can’t see that. Need a lot more clarification as to exactly what you are attempting to accomplish. Steps please.
Or is it you want to print this form in Base to distribute & be filled in then when sent back the data will be entered into Base? (more logical explanation). Don’t see in sample any attempt at this.
I thank you all for your patience. I am new to forums such as this and I am finding my feet with the correct protocol. I did eventually find out how to add files after I posted the first comment so i apologise for the confusion in my post. Yes your second comment is correct. I wish to create a report and print it as a form to distribute and be returned by members so that the data can be entered into Base.
I will study your notes and attempt to write an SQL statement to do what has been suggested. I have limited time for this project so I will probably take some days to reply. Thanks again for your patience. Thank you for picking up the space in the table name. I had missed it completly.