Query Left Inner Join and Null Data?

[luism]

I created an interface to allow users to search records, however with the following code it only displays data when a Contact has a contract and it does not show Contacts that have no contracts linked to them whatsoever. I need it so that it also displays all contacts regardless of Contract or not and if they don't to display "none" on the field...

Private Sub cmdShowAllContacts_Click()
strSQL = "SELECT Tbl_Contacts.ContactID, Tbl_Contacts.LName, Tbl_Contacts.Address, Tbl_Contacts.City, Tbl_Contacts.State, Tbl_Contacts.Zip, Tbl_Contacts.HomePhone, Tbl_Contacts.WorkPhone, Tbl_Contacts.CellPhone, Tbl_Contacts.Email, Tbl_Contracts.DateEnds, [LName] & iif([FName]is not null,', ' & [FName],'') AS ContactName FROM Tbl_Contacts INNER JOIN Tbl_Contracts ON Tbl_Contacts.ContactID = Tbl_Contracts.ContactID ORDER BY Tbl_Contacts.LName, Tbl_Contacts.FName"

Forms![Frm_SearchAllContacts].Form.RecordSource = strSQL
End Sub

The following code allows it to SHOW all contacts with or without Contracts BUT I can't seem to find a way to get it say "None", it just shows up empty and the rest that have dates come up with the dates as required.

Private Sub cmdShowAllContacts_Click()
strSQL = "SELECT Tbl_Contacts.ContactID, Tbl_Contacts.LName, Tbl_Contacts.Address, Tbl_Contacts.City, Tbl_Contacts.State, Tbl_Contacts.Zip, Tbl_Contacts.HomePhone, Tbl_Contacts.WorkPhone, Tbl_Contacts.CellPhone, Tbl_Contacts.Email, Tbl_Contracts.DateEnds, [LName] & iif([FName]is not null,', ' & [FName],'') AS ContactName FROM Tbl_Contacts LEFT OUTER JOIN Tbl_Contracts ON Tbl_Contacts.ContactID = Tbl_Contracts.ContactID ORDER BY Tbl_Contacts.LName, Tbl_Contacts.FName"

Forms![Frm_SearchAllContacts].Form.RecordSource = strSQL
End Sub

Any help is tremendously appreciated!

 

[jdraw]

You may have to use a Union query along this line: (untested)

a) those contacts with Contracts
and b) those Contacts with no Contracts

Select fld1, fld2,fld3, fldContracNum from Contacts Inner Join Contracts....
union
SELECT fld1, fld2,fld3, "none" as ReqdField from Contacts Left Join Contracts

 

[luism]

Any way you can give me a code that would work for this? I'm a bit lost! This query is complicated for my skills xD

The form is also doing a query of both tables but no Criteria is set anywhere so I'm doing the rest on VBA after "showAllContacts" button is clicked event

 

[mahenkj2]

You may use Query wizard to create a query and then select there union query and then go as jdraw suggested.

 

[jdraw]

I don't have your tables.

I have a set up with Students, Enrollments and Courses.

Some students have assigned courses, some do not.

In order to get all Students -- those with no courses "None" and those with some "Some Course(s)" I use the following Union query

SELECT  Student.studentName, "None" AS Expr1
FROM Student Left JOIN SelectedCourses ON SelectedCourses.StudentId = Student.id
WHERE (((SelectedCourses.CourseId) Is Null))
UNION
SELECT distinct  Student.studentName,"SomeCourse(s)"
FROM (Student INNER JOIN SelectedCourses ON Student.id = SelectedCourses.StudentId) INNER JOIN Course ON SelectedCourses.CourseId = Course.CId;

My output:

studentName	Expr1
Bill	None
Cletis	SomeCourse(s)
Elvira	SomeCourse(s)
James	SomeCourse(s)
Jane	SomeCourse(s)
Jihad	SomeCourse(s)
John	SomeCourse(s)
Karhu	SomeCourse(s)
Mary	SomeCourse(s)
Sam	None
William	SomeCourse(s)

see http://www.techonthenet.com/sql/union.php
http://www.techonthenet.com/access/queries/joins2.php

 

[luism]

Unfortunately, that doesn't work for what I'm trying to do.

I am selecting one field from one table Tbl_Contracts (DateEnds)

And multiple fields from another table Tbl_Contacts

I got it to work as I stated earlier, it does show DateEnds if it exists but I can't get the DateEnds field to say "none" on it if it's null/empty, please refer to my query on my original post.

Thank you for your help!

 

[mahenkj2]

can you post a sample dB, so one can have a look at it.

 

[mahenkj2]

If you say your code is working but you just want to show none when date does not exist, may try Nz function. Nz([Date],"None").

 

[luism]

duh, why didn't I think of that. i wanted the query to return "none" but i guess it's easier to tell the field =Nz([DateEnds],"None") and it worked fine, even added some conditional formatting colors. thanks!

 

[luism]

After inputting some data, this is returning multiple results for the same person. I'll post my tables in a bit *sigh* Any help is most definitely appreciated. I'll post it soon.

 

[luism]

I have attached my tables
Tbl_Contacts
Tbl_Contracts

I also included my form Frm_SearchAllContacts

You will clearly see how Jane Doe and John Smith have multiple results. This causes confusion, my goal is to display each contact once and if a contract doesn't exist to just say "None"

Thank you!

View attachment MyDB.accdb

 

[jdraw]

I have acc 2003 and can not use/open an accdb format database.

Take a good look at the example I posted in #5.

 

[luism]

The problem is that I can have multiple Contracts per person so it makes sense that the "SearchAll" form results in duplicates or more being displayed, I'd just like to grab the latest contract created and display that info and if no contract then "none", so something like

Jane Doe, Full Address, Phone, Email, Contract (DateEnds) / or none.

I tried to mess with your code jdraw, it didn't give me what I seek. I tried to save the DB as a 2002-2003 access file but it didn't let me. Or would you like me to just give you my tables without the form?

 

[luism]

NVM, here it is in 2002-2003 format.

View attachment MyDB.mdb

 

[mahenkj2]

Have you checked Totals query. In the dateEnds field, select Last.

 

[mahenkj2]

Please find attached modified. Press Show all contact. You have used VBA in all buttons, so I had to convert a totals query and then paste it.

I have also made a form which converts SQL into VBA, might help you.

Thanks to http://allenbrowne.com/ser-71.html

regards.

 

[jdraw]

The data in the tables in your MyDB.mdb do not reflect the issue you described.
There are no contracts without a Contact, and no contacts without a Contract???

I see 2 contacts 1 and 2; and 6 Contracts 1,2,3,4,5,6.

If I have missed something, please describe.

 

[luism]

Please find attached modified. Press Show all contact. You have used VBA in all buttons, so I had to convert a totals query and then paste it.

I have also made a form which converts SQL into VBA, might help you.

Thanks to http://allenbrowne.com/ser-71.html

regards.

Can't thank you enough, this helped a lot. Thank you VERY much!

 

[luism]

*sigh* :banghead:

Back again, this interface has given me so many issues. A new one has arrived after importing some data from old db to my new one.

See the attached please, you can clearly see that everyone has a contract that will end either in a month or so or even next year yet it is showing that two out of three of these contacts have expired contracts.

How can i fix this? last didn't seem to work on this case =\ if i enter NEW data, in order, then it works but for some reason the old DB didnt enter data this way so "last" total doesn't work, nor first or max or min i tried but can't figure it out.

View attachment MyDB.mdb

 

[luism]

any takers to help me finish this completely by sorting this last issue i have?