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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.