How to pull only needed data

[Brian62]

I am creating a query with multiple tables. What I need is for the query not to pull what "Is Not Null" in column2 and column1 should not be pulled as well. 1=Yes, 2= No, 3 = NA

Example:

Column1 Column2
Doc #2 Com-Doc #2
2 Document is missing


If (Com-Doc #2) is set to "Is Not Null" both should not show. I do not know how to setup Column1 so they both don't show. If column 2 does have data in it then I would need to show the info.

Would a crosstab work for this since it will have many records and columns?

I want to be able to show in a report only data that is needed.

 

[accessfleet]

Re: How to pull only needed datathis is the method in SQL view that I have used succe

SELECT WORKORDERS.RadioUnit, WORKORDERS.[Workorder number], WORKORDERS.[DPS Workorder], WORKORDERS.OpenWorkOrderDate, WORKORDERS.CloseWorkOrderDate, WORKORDERS.Instructions, WORKORDERS.[Supervisor/CrewRemarks], WORKORDERS.[Work type], WORKORDERS.[CLOSED WORKORDER], WORKORDERS.Miles, WORKORDERS.Hours
FROM WORKORDERS
WHERE (((WORKORDERS.RadioUnit)=[Enter Radio Unit]) AND ((WORKORDERS.CloseWorkOrderDate) Between [Enter Start Date] And [Enter End Date]))
ORDER BY WORKORDERS.[Workorder number];

this is the method in SQL view that I have used successfully.

 

[boblarson]

You would use this as a field (this is using the QBE grid) instead of the normal Column1:

Col1:IIF(IsNull([Column2]), "", [Column1])

 

[Brian62]

Here what I did and still not getting what I need. I only want to see if (Com-Doc #2) has data.

SQL View:

SELECT DISTINCTROW tblCommitteeInfoEntry.PI, RDStudyAuditSectionA.[Audit Date], RDStudyAuditSectionA.[doc #2], IIf(IsNull([Com-Doc #2])," ",[Doc #2]) AS [Doc#2]
FROM (RDStudyAuditSectionA RIGHT JOIN tblCommitteeInfoEntry ON RDStudyAuditSectionA.StudyID = tblCommitteeInfoEntry.StudyID) RIGHT JOIN RDStudyAuditSectionE ON tblCommitteeInfoEntry.StudyID = RDStudyAuditSectionE.StudyID
GROUP BY tblCommitteeInfoEntry.PI, RDStudyAuditSectionA.[Audit Date], RDStudyAuditSectionA.[doc #2], IIf(IsNull([Com-Doc #2]),"",[Doc #2]);

Note: There are a lot of different types of data to look at but most will be empty. I most likely will have to create multiple queries to setup the report. Ijust have to get through this problem.

Is there a way that I can hide blanks in a report and where the text boxes can going on the same row and make a new row once that line is full? There maybe only a few that would show. Any ideas?

Thanks!

 

[boblarson]

First, get it working WITHOUT THE GROUPING (also take off the Distinctrow part.

Second, once you get it working, in the past I have had to save that query and then use it in another to do the grouping. It seems to need that at times.

Is there a way that I can hide blanks in a report and where the text boxes can going on the same row and make a new row once that line is full? There maybe only a few that would show. Any ideas?

Not if I'm understanding you.

 

[Brian62]

I was thinking of a way to work it into the report to show only what I need.
I removed the distinctrow and tried distinct and just select and still pulls all data incuding the blanks. Also removed Group By but it didn't like that.

I've had some hard one's in the past but I don't think this one is workable. I have to be able to pull only fields that have data in them for a monthly report.

 

[boblarson]

Sounds like you may need to use a temp table and a set of append queries to populate it. That might be the easiest solution at the moment for you.

 

[Brian62]

I have attached part of the db so you see what i am trying to do.

Thanks!