Re: [MS_AccessPros] Missing data in reports

 

Kacy-

When you change INNER to LEFT, you're performing a LEFT OUTER JOIN.  In SQL syntax, the keyword OUTER is optional, so most programmers usually leave it out, but it helps understand what is going on.  When you do an OUTER join, you're asking the database engine to include all rows from one table or query and any matching rows (based ON the join criteria) from the second table.  You can say LEFT or RIGHT - which indicates that you want either the "left" table or the "right" table to include all rows.

So, Projects LEFT JOIN Needs and Recommendations gets you all projects and any matching needs.  When there are no matching rows in the "right" table, you get Null values returned in those columns.

If you want all rows from both sides, you need a FULL OUTER JOIN, but Access doesn't support that.  To emulate a FULL OUTER JOIN in Access, you need to do:

SELECT ... 

FROM tblA LEFT JOIN tblB

ON tblA.Key = tblB.Key

UNION ALL

SELECT ...

FROM tblA RIGHT JOIN tblB

ON tblA.Key = tblB.Key

WHERE tblA.Key IS NULL;

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Sep 9, 2016, at 21:11, theaterkacy <no_reply@yahoogroups.com> wrote:

John,

I realize that I've used the wrong query for the SQL below, although I think the issue is the same. The query I provided earlier was actually qProjectNeed, which is used in the form where projects are assigned to needs.

Ultimately, however, I believe it is the same issue, with additional selected fields for the report.
The SQL for the report is the following:

SELECT Projects.ID AS Projects_ID, Projects.Hide, Projects.[Project Number], Projects.[Project Low], Projects.[Project High], Projects.[Estimated Downtime], Projects.[Action Year], Projects.Rec, Projects.[Project Desc], Projects.[Related Projects], Projects.[Related Projects].Value, Projects.[Proj Full Descript], Projects.[Proj Priority], [Needs and Recommendations].[Stated Need], [Needs and Recommendations].Recommendation, [Needs and Recommendations].ID AS [Needs and Recommendations_ID], [Needs and Recommendations].Priority, [Needs and Recommendations].[Work By], [Needs and Recommendations].Photo1, [Needs and Recommendations].Photo2, [Needs and Recommendations].Comment, [Needs and Recommendations].[System Primary], [Needs and Recommendations].[System Other], [Needs and Recommendations].[System Other].Value
FROM Projects INNER JOIN ([Needs and Recommendations] INNER JOIN tProjectNeed ON [Needs and Recommendations].ID = tProjectNeed.NeedID) ON Projects.ID = tProjectNeed.ProjectID;

Not every Need has a Project, there are Projects with multiple Needs. There may be some Projects without Needs. (I didn't do the data entry on this one, so I'd need to dig a little to confirm.)

So, I took the above SQL and revised it to:
SELECT...
FROM Projects LEFT JOIN ([Needs and Recommendations] RIGHT JOIN tProjectNeed ON [Needs and Recommendations].ID = tProjectNeed.NeedID) ON Projects.ID = tProjectNeed.ProjectID;

Now the query reports all projects! And, the report, of course, is also now showing the appropriate data.
Thanks for the help!

So that I understand:
    What does changing the joins in this fashion mean? Should I be keeping my eye out for missing Needs, since changing the join to LEFT JOIN for this table?
   

-Kacy


 

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Kacy-

Ah, looks like you need a LEFT JOIN from Projects to tProjectNeed.  There must be some Projects that have no matching record in the needs table.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Sep 9, 2016, at 02:26, theaterkacy <no_reply@yahoogroups.com> wrote:

John,

Thanks for the reply. 

SELECT tProjectNeed.ProjectID, Projects.[Project Number], Projects.[Project Desc], tProjectNeed.NeedID, tProjectNeed.ID 

FROM Projects INNER JOIN tProjectNeed ON Projects.[ID] = tProjectNeed.[ProjectID];

What would you like to know about the tables?

My apologies for not including the prior conversation in my last post. I thought I had, but I'm having a bear of a time using Yahoo groups on my mobile device. 

-Kacy

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Kacy-

It would help to see the SQL of your query and the structure of your tables.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Sep 8, 2016, at 5:37 PM, theaterkacy <no_reply@yahoogroups.com> wrote:

Hello all,

I've been stumped by a bug when I run some of my reports. 

I have a database that collects "Needs and Recommendations" and then matches these to improvement projects. Needs and Projects are separate tables. Some projects are designated "recommended" by a checkbox, but that option does not seem related to this bug. There are multiple needs per project, and some needs may be met by more than one project.

When I run my full report, listing all projects with the associated needs, some projects do not appear. (Projects have alphanumeric "numbers", so the sort order is a bit clunky.) The report is based on a query, which is also missing these projects. I can't figure out why.

My query/report includes projects 1, 10A-C, 12A-C, 15A-C, 16, 2, 3, 4A-B, 5A-B, 6A-C, 6E, 7, 8A-D, 9A-B. It is missing 11, 13, 14, 15D-E, 4C, 5C, 6D. 

I wondered if this had anything to do with order of entry. In the "Projects" table, the missing projects mostly have IDs at the end of the records. The only exception is 4C, whose ID is 6. It is possible that this project was renumbered after the initial data entry.

So, it seems that my query is omitting the most recent data entry.

These projects do appear in other reports, which use different sources.

How can I fix this?

The query, "qProjectA", uses joins to connect the tables "Projects" and "Needs". (The latter actually has a longer name with spaces in it, for what it's worth.) The join table is "tProjectNeed". Projects.ID is connected to tProjectNeed.ProjectID by a type 1 join. Needs.ID is connected to tProjectNeed.NeedID by a type 1 join. The query uses fields from the two source tables and not from tProjectNeeds.

Thank you for the help!

Kacy

 

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___