No query results if Field left blank (1 Viewer)

mkdrep

Registered User.
Local time
Today, 11:52
Joined
Feb 6, 2014
Messages
176
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
 

Attachments

  • Empty_AIA_Info_In_Qry.pdf
    46.6 KB · Views: 78

jdraw

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Jan 23, 2006
Messages
15,364
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:52
Joined
Aug 30, 2003
Messages
36,118
Double or right click on the join line between the tables to edit the join and the correct selection should be obvious.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
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.
 

Micron

AWF VIP
Local time
Today, 11:52
Joined
Oct 20, 2018
Messages
3,476
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.
 

mkdrep

Registered User.
Local time
Today, 11:52
Joined
Feb 6, 2014
Messages
176
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

Registered User.
Local time
Today, 11:52
Joined
Feb 6, 2014
Messages
176
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:52
Joined
Aug 30, 2003
Messages
36,118
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!
 

Micron

AWF VIP
Local time
Today, 11:52
Joined
Oct 20, 2018
Messages
3,476
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.
 

Users who are viewing this thread

Top Bottom