Sabtu, 10 September 2016

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

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

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 
(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.


.

__,_._,___

Tidak ada komentar:

Poskan Komentar