I am trying to build a query by selecting information from 4 different tables. I have tried numerous select statements with JOINS and OUTER JOINS, Sub Queries, etc. I just can't seem to get it right.
Basically what I have is 4 tables, Submittal, ContactCompany, Contacts, JobContacts.
I am trying to make a form that will display my submittal information from the query. I know I'm missing something or I don't have something setup on my tables correctly, but when I run my query, it will show multiple lines with the same information. I know where the lines come from (multiple contacts on the same job), but I need to just display only one line.
What i want to display is this:
Company Name | Submittal Number (If available. Some don't have a number) | Submitted Item | Date Submitted | Response Received | SubmitStatus
Any help on my Query would be GREATLY Appreciated.
I have attached a screenshot to show you what I have right now as far as tables go. The Query was just the last query I put in but even that shows the multiple lines.
Thank you.
[Solved] Selecting Information from Multiple Tables
[Solved] Selecting Information from Multiple Tables
Last edited by jsvanc on Fri Feb 23, 2018 5:07 am, edited 1 time in total.
OpenOffice 4.1.5 on Windows 10
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Selecting Information from Multiple Tables
No screenshot attached. Make sure it's smaller than 128kbi. Else, use a file sharing web site.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Selecting Information from Multiple Tables
Sorry. Screenshot added.
OK. So I got this to select the correct lines, but when I go to the form with this query, I shows the same data on every job.
OK. So I got this to select the correct lines, but when I go to the form with this query, I shows the same data on every job.
Code: Select all
SELECT DISTINCT "Submittal".*, "ContactCompany"."CompanyName", "ContactCompany"."ContactType"
FROM "Submittal"
LEFT OUTER JOIN "Jobs" ON "Jobs"."JobID" = "Submittal"."JobID"
LEFT OUTER JOIN "JobContacts" ON "Jobs"."JobID" = "JobContacts"."JobID"
LEFT OUTER JOIN "ContactCompany" ON "Submittal"."CompanyID" = "ContactCompany"."CompanyID"
OpenOffice 4.1.5 on Windows 10
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Selecting Information from Multiple Tables
I'm assuming you the form you mention here is the Jobs form document from the database you uploaded to
Dropdown List on Form but don't want that data saved
It seems to work with
I noticed another issue with your form Jobs that causes problems when navigating on your table controls.
Open both the Properties Form dialog for both subforms and change the Navigation Bar property to Parent Form. That way if you select something on one of the subforms the main navigation controls will remain connected to MainForm.
Dropdown List on Form but don't want that data saved
It seems to work with
Code: Select all
SELECT "Submittal".*, "Jobs".*, "ContactCompany"."CompanyName", "ContactCompany"."ContactType", "Contacts"."ContactName"
FROM "Submittal", "Jobs", "Contacts", "ContactCompany"
WHERE "Submittal"."JobID" = "Jobs"."JobID" AND "Submittal"."JobID" = "Submittal"."JobID" AND "Contacts"."CompanyID" = "Submittal"."CompanyID" AND "ContactCompany"."CompanyID" = "Contacts"."CompanyID";
Open both the Properties Form dialog for both subforms and change the Navigation Bar property to Parent Form. That way if you select something on one of the subforms the main navigation controls will remain connected to MainForm.
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
Re: [SOLVED] Selecting Information from Multiple Tables
Thank you @UnklDonald418. Pretty much have everything working. Now I'm having a problem with Dialogs but I will post a new thread for that.
OpenOffice 4.1.5 on Windows 10