Re: [MS_AccessPros] Missing data in reports

 

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 (6)

---

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

.

__,_._,___