Query of Queries to Return Only Populated Fields in All Queries

Hi All,

This is likely going to be a confusing request, so I apologize. Here is an example of what I am trying to do:

Here are examples of fields in one table of my database:

-Note Number
-Corrected Name
-Name Correction Needed? (Y/N)
-Corrected Address
-Address Correction Needed? (Y/N)
-Phone Number
-Corrected Phone Number
-Phone Number Correction Needed? (Y/N)

I have built separate queries to return:
1) All Note Numbers where "Correction Needed" is True for Name
2) All Note Numbers where "Correction Needed" is True for Address
3) All Note Numbers where "Correction Needed" is True for Phone Number

Is there a way to build a query that would say:
For Note Number [Enter Note Number], show me only those fields where "Correction Needed" is True, presumably based on these queries but open to other suggestions.

So if there is a correction needed for Name and Address fields, but not Phone Number fields, I want to see in my query:
Note Number, Name, Corrected Name, Name Correction Needed (True), Address, Corrected Address, Address Correction Needed (True)

I don't want to see any Phone Number fields if there is no change required for that Note Number.

Is this possible?


Who is Participating?
By definition, tables and queries have the same set of columns for each row.  If you want the three different queries to only select the relevant columns, you can do that but you'll need to stick with using three queries.

I've attached a partial screenshot of a form I use to present my users with demographics errors and allow them to correct them. DemographicsI narrowed some columns so I could hide identifying info.

This is the query that is used to select the records for viewing.  It selects a client record if ANY demographics field is missing or in some cases in error.

SELECT tblClients.ClientID, tblClients.ems, tblClients.FirstName, tblClients.LastName, tblClients.DOB, tblClients.ssn, tblClients.address, tblClients.TownCD, tblClients.state, tblClients.zip, tblClients.Phone, tblClients.GenderID, tblClients.MarriedID, tblClients.RaceID, tblClients.StatusID, tblClients.ChangeBy, tblClients.ChangeDT, tblClients.LevelID, DateDiff("yyyy", DOB, Date()) AS YearAge
FROM tblClients
WHERE (IIf([forms]![frmClientsWithErrors]![fraStatus]=2,IIf(tblClients.[StatusID]=1319,True,False),IIf([forms]![frmClientsWithErrors]![fraStatus]=1,IIf(tblClients.[StatusID]=1318,True,False),True))=True)
(tblClients.FirstName  Is Null OR tblClients.LastName Is Null OR tblClients.DOB Is Null OR tblClients.ssn Is Null OR tblClients.address Is Null OR tblClients.TownCD Is Null OR tblClients.state Is Null OR tblClients.zip Is Null OR tblClients.Phone Is Null OR tblClients.GenderID Is Null OR tblClients.MarriedID Is Null OR tblClients.RaceID Is Null OR (Left(ssn,1) = Mid(ssn, 2,1) AND Left(ssn,1) = Mid(ssn, 3,1) And Left([ssn],1)=Mid([ssn],4,1) And Left([ssn],1)=Mid([ssn],5,1)) OR (DateDiff("yyyy", DOB, Date()) > 120 AND tblClients.StatusID = 1318) OR DateDiff("yyyy", DOB, Date()) < 18)
AND Left(tblClients.FirstName, 4) <> "test"
ORDER BY tblClients.ems;

The final step is to colorize the empty fields using conditional formatting.
Conditional Formatting
This gives the users a simple to use interface where I can control the updating because it uses a form.
Walter RitzelSenior Software EngineerCommented:
after careful reading of your question, my straight answer is NO. Your final query will need to have all columns for names, addresses and telephones.

What you can do is this:
1) Transform your queries 1, 2 and 3 in views.
2) Create a new view that will contain only the Note Numbers that had any correction (using a pseudo SQL, you may need to adjust):
create view note_numbers_corrected as
select note_number from tableA where name_correction_needed = 'Y' or address_correction_needed = 'Y' or telephone_correction_needed = 'Y'

Open in new window

3) Then, create a new query that will join all the 4 views:
select a.note_number, b.*, c.*, d.*
from note_numbers_corrected a 
    left join view1 b on a.note_number = b.note_number
    left join view2 b on a.note_number = c.note_number
    left join view3 b on a.note_number = d.note_number

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.