Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-11-2019, 07:59 AM   #1
mkdrep
Newly Registered User
 
Join Date: Feb 2014
Posts: 146
Thanks: 28
Thanked 0 Times in 0 Posts
mkdrep is on a distinguished road
No query results if Field left blank

I have set up a simply query as shown in attached (.pdf)
[specjobs] is my main Project database, [Job Process] is where the users put their project notes. [Architect] is a database containing Architectural Firms that would be involved in the design of the Project

The way the query is set up, I would like the Architect info included in the report I generate. Unfortunately, not all projects have an Architect involved in it, so those projects will not show up on the report.

How can I get a project to show up on a report even when it does not have an Architect involved?

Thank you
Attached Files
File Type: pdf Empty_AIA_Info_In_Qry.pdf (46.6 KB, 27 views)

mkdrep is offline   Reply With Quote
Old 01-11-2019, 08:13 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,815
Thanks: 70
Thanked 1,953 Times in 1,901 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: No query results if Field left blank

Your diagram indicates these tables are related 1:1.
You should review the picture and info to see what data is available in the various JOINs .

I have a feeling that there may be some hidden entities based on your specjobs table.
Seems JT_ might signify something
Status, JobBidDate,EU_Contact,JobTrackDate....may indicate different "things" that are not expressly identified in your pdf.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 01-11-2019, 08:13 AM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,770
Thanks: 11
Thanked 4,015 Times in 3,952 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: No query results if Field left blank

Double or right click on the join line between the tables to edit the join and the correct selection should be obvious.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
mkdrep (01-11-2019)
Old 01-11-2019, 09:57 AM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: No query results if Field left blank

Quote:
Your diagram indicates these tables are related 1:1
.
I am not sure what you are seeing but that is not what it is showing. A 1-1 will have a one on both sides.
1-----------------1
Which is done by linking two indexed unique fields.

Nothing on the end shows that referential integrity is not set
.------------------.
Even if it is a 1 to many without refential integrity you still get nothing on the end of the lines.

The arrows show inner and outer joins and having nothing to do with how tables are related.
MajP is offline   Reply With Quote
Old 01-11-2019, 10:46 AM   #5
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 432
Thanks: 2
Thanked 85 Times in 81 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: No query results if Field left blank

I'm not seeing a clear recipe for success without 2 queries as the architect join doesn't involve a PK field. IMHO, the AIA_ID PK should be linked to specjobs but there is no field for it. Would have to play around to be sure but the Firm fields look like a many many join, plus there is the issue of nulls in specjobs.
Suggest you avoid spaces in object names and not just fields.
Micron is online now   Reply With Quote
Old 01-11-2019, 11:00 AM   #6
mkdrep
Newly Registered User
 
Join Date: Feb 2014
Posts: 146
Thanks: 28
Thanked 0 Times in 0 Posts
mkdrep is on a distinguished road
Re: No query results if Field left blank

Quote:
Originally Posted by pbaldy View Post
Double or right click on the join line between the tables to edit the join and the correct selection should be obvious.
That did it! I changed the JOIN between [specjobs].[firm] and [Architect].[Firm] to "All records from 'specjobs' and only those records from "Architect' where joined fields are equal."

Thank you very much!
mkdrep is offline   Reply With Quote
Old 01-11-2019, 11:02 AM   #7
mkdrep
Newly Registered User
 
Join Date: Feb 2014
Posts: 146
Thanks: 28
Thanked 0 Times in 0 Posts
mkdrep is on a distinguished road
Re: No query results if Field left blank

Quote:
Originally Posted by Micron View Post
I'm not seeing a clear recipe for success without 2 queries as the architect join doesn't involve a PK field. IMHO, the AIA_ID PK should be linked to specjobs but there is no field for it. Would have to play around to be sure but the Firm fields look like a many many join, plus there is the issue of nulls in specjobs.
Suggest you avoid spaces in object names and not just fields.
I appreciate the suggestion about avoidng spaces in object names. Unfortunately, I wrote a lot of this program YEARS ago when I didn't realize spaces could become a problem. Too many changes would have to be made and I haven't really run into any issues.

mkdrep is offline   Reply With Quote
Old 01-11-2019, 11:05 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,770
Thanks: 11
Thanked 4,015 Times in 3,952 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: No query results if Field left blank

Quote:
Originally Posted by mkdrep View Post
That did it! I changed the JOIN between [specjobs].[firm] and [Architect].[Firm] to "All records from 'specjobs' and only those records from "Architect' where joined fields are equal."

Thank you very much!
Happy to help!
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-11-2019, 12:21 PM   #9
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 432
Thanks: 2
Thanked 85 Times in 81 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: No query results if Field left blank

Well, I guess I didn't have enough data that I made up because that didn't work for me. Still think the table relationship isn't correct - assuming for a given record, you are storing the same text value in both places yet there's no PK field involved at all.

Micron is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query results no record because another criteria field is blank ITguy1981 Queries 3 04-16-2012 12:14 PM
Query By Form (No results when field on table = blank) George10988 Modules & VBA 2 04-29-2011 09:43 AM
Return all results with parameters left blank Rowen Queries 4 06-05-2009 04:59 AM
Unable to return query if a field is left blank cooh23 Queries 16 01-03-2008 01:03 PM
Queries codes for giving results as all if criteri is left blank shapman Queries 3 09-13-2007 07:36 AM




All times are GMT -8. The time now is 11:58 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World