Eliminating Rows if fields have null values? 1

[ConfusedNAccess]

HELP! I'm new to Access. Never taken a class. I'm self taught, and I feel like i have an ok handle on things.. but good grief this stuff will drive you nuts!

PLEASE HELP!

I have a query that returns results of names of borrowers, names of reports due for that borrower, and if it is an active report, returns the due date.
The point is to find reports that have exceeded the due date.

I have found the information what i'm looking for, plus some. The results returned show ALL reports due for the borrower, not just the ones that have corresponding dates.

SELECT Breaches.[Geographic Locataion], Breaches.Location, Breaches.[City/State], Breaches.[City #], Breaches.RM, Breaches.[Account Manager], Breaches.AM_NumBreaches, Breaches.BorrID, Breaches.BorrowerName, Breaches.SingleName, Breaches.Brr, Mid$([SRF],1,7) AS SRF1, Breaches.ClientYearEnd, Breaches.Borr_NumBreaches, Breaches.Borr_NumCovBreaches, Breaches.Borr_NumCovNonFinBreaches, Breaches.Borr_NumMarginBreaches, Breaches.Borr_NumLABreaches, Breaches.Borr_NumRepBreaches, Breaches.Borr_NumEWSNonFinBreaches, Breaches.ActualYear, Breaches.SheetRowText, Breaches.EffectiveFromDate, Breaches.EffectiveToDate, Breaches.Frequency, Breaches.GracePeriod, IIf([Breaches].[Breach 1]=Yes,[Breaches].[Month01DueDate],Null) AS Jan, IIf([Breaches].[Breach 2]=Yes,[Breaches].[Month02DueDate],Null) AS Feb, IIf([Breaches].[Breach 3]=Yes,[Breaches].[Month03DueDate],Null) AS Mar, IIf([Breaches].[Breach 4]=Yes,[Breaches].[Month04DueDate],Null) AS Apr, IIf([Breaches].[Breach 5]=Yes,[Breaches].[Month05DueDate],Null) AS May, IIf([Breaches].[Breach 6]=Yes,[Breaches].[Month06DueDate],Null) AS Jun, IIf([Breaches].[Breach 7]=Yes,[Breaches].[Month07DueDate],Null) AS Jul, IIf([Breaches].[Breach 8]=Yes,[Breaches].[Month08DueDate],Null) AS Aug, IIf([Breaches].[Breach 9]=Yes,[Breaches].[Month09DueDate],Null) AS Sep, IIf([Breaches].[Breach 10]=Yes,[Breaches].[Month10DueDate],Null) AS Oct, IIf([Breaches].[Breach 11]=Yes,[Breaches].[Month11DueDate],Null) AS Nov, IIf([Breaches].[Breach 12]=Yes,[Breaches].[Month12DueDate],Null) AS [Dec]
FROM Breaches;


Breaches QUERY shows
SELECT [Account Manager Info].[Geographic Locataion], [Account Manager Info].Location, [Account Manager Info].[City/State], [Account Manager Info].[City #], IIf(IsNull([AM RM SM.Risk Manager]),[CRM-Commercial.Risk Manager],[AM RM SM.Risk Manager]) AS RM, [Account Manager Info].[Account Manager], [Account Manager Info].AM_NumBreaches, dbo_ActualReports.BorrID, dbo_Borrowers.BorrowerName, dbo_Borrowers.SingleName, dbo_Borrowers.Brr, dbo_Borrowers.SRF, dbo_Borrowers.ClientYearEnd, dbo_ActualReports.ActualYear, dbo_BorrowerReports.SheetRowText, dbo_BorrowerReports.EffectiveFromDate, dbo_BorrowerReports.EffectiveToDate, dbo_BorrowerReports.Frequency, dbo_BorrowerReports.GracePeriod, IIf(([dbo_ActualReports]![Active01]=-1 And [dbo_ActualReports]![Month01Value]=0),Yes,No) AS [Breach 1], dbo_ActualReports.Month01DueDate, dbo_ActualReports.Month01SignOff, IIf(([dbo_ActualReports]![Active02]=-1 And [dbo_ActualReports]![Month02Value]=0),Yes,No) AS [Breach 2], dbo_ActualReports.Month02DueDate, dbo_ActualReports.Month02SignOff, IIf(([dbo_ActualReports]![Active03]=-1 And [dbo_ActualReports]![Month03Value]=0),Yes,No) AS [Breach 3], dbo_ActualReports.Month03DueDate, dbo_ActualReports.Month03SignOff, IIf(([dbo_ActualReports]![Active04]=-1 And [dbo_ActualReports]![Month04Value]=0),Yes,No) AS [Breach 4], dbo_ActualReports.Month04DueDate, dbo_ActualReports.Month04SignOff, IIf(([dbo_ActualReports]![Active05]=-1 And [dbo_ActualReports]![Month05Value]=0),Yes,No) AS [Breach 5], dbo_ActualReports.Month05DueDate, dbo_ActualReports.Month05SignOff, IIf(([dbo_ActualReports]![Active06]=-1 And [dbo_ActualReports]![Month06Value]=0),Yes,No) AS [Breach 6], dbo_ActualReports.Month06DueDate, dbo_ActualReports.Month06SignOff, IIf(([dbo_ActualReports]![Active07]=-1 And [dbo_ActualReports]![Month07Value]=0),Yes,No) AS [Breach 7], dbo_ActualReports.Month07DueDate, dbo_ActualReports.Month07SignOff, IIf(([dbo_ActualReports]![Active08]=-1 And [dbo_ActualReports]![Month08Value]=0),Yes,No) AS [Breach 8], dbo_ActualReports.Month08DueDate, dbo_ActualReports.Month08SignOff, IIf(([dbo_ActualReports]![Active09]=-1 And [dbo_ActualReports]![Month09Value]=0),Yes,No) AS [Breach 9], dbo_ActualReports.Month09DueDate, dbo_ActualReports.Month09SignOff, IIf(([dbo_ActualReports]![Active10]=-1 And [dbo_ActualReports]![Month10Value]=0),Yes,No) AS [Breach 10], dbo_ActualReports.Month10DueDate, dbo_ActualReports.Month10SignOff, IIf(([dbo_ActualReports]![Active11]=-1 And [dbo_ActualReports]![Month11Value]=0),Yes,No) AS [Breach 11], dbo_ActualReports.Month11DueDate, dbo_ActualReports.Month11SignOff, IIf(([dbo_ActualReports]![Active12]=-1 And [dbo_ActualReports]![Month12Value]=0),Yes,No) AS [Breach 12], dbo_ActualReports.Month12DueDate, dbo_ActualReports.Month12SignOff, IIf([Breach 1]=Yes,[dbo_ActualReports].[Month01DueDate],No) AS Jan, IIf([Breach 2]=Yes,[dbo_ActualReports].[Month02DueDate],No) AS Feb, IIf([Breach 3]=Yes,[dbo_ActualReports].[Month03DueDate],No) AS Mar, IIf([Breach 4]=Yes,[dbo_ActualReports].[Month04DueDate],No) AS Apr, IIf([Breach 5]=Yes,[dbo_ActualReports].[Month05DueDate],No) AS May, IIf([Breach 6]=Yes,[dbo_ActualReports].[Month06DueDate],No) AS Jun, IIf([Breach 7]=Yes,[dbo_ActualReports].[Month07DueDate],No) AS Jul, IIf([Breach 8]=Yes,[dbo_ActualReports].[Month08DueDate],No) AS Aug, IIf([Breach 9]=Yes,[dbo_ActualReports].[Month09DueDate],No) AS Sep, IIf([Breach 10]=Yes,[dbo_ActualReports].[Month10DueDate],No) AS Oct, IIf([Breach 11]=Yes,[dbo_ActualReports].[Month11DueDate],No) AS Nov, IIf([Breach 12]=Yes,[dbo_ActualReports].[Month12DueDate],No) AS [Dec]
FROM [Account Manager Info] INNER JOIN (((dbo_Borrowers INNER JOIN (dbo_BorrowerReports INNER JOIN dbo_ActualReports ON dbo_BorrowerReports.BorrRepID = dbo_ActualReports.BorrRepID) ON (dbo_Borrowers.BorrID = dbo_BorrowerReports.BorrID) AND (dbo_Borrowers.BorrID = dbo_ActualReports.BorrID)) INNER JOIN dbo_Centres ON dbo_Borrowers.CentreID = dbo_Centres.CentreID) INNER JOIN dbo_AccountManagers ON dbo_Borrowers.AM_ID = dbo_AccountManagers.AM_ID) ON [Account Manager Info].AM_ID = dbo_AccountManagers.AM_ID
ORDER BY [Account Manager Info].[Account Manager], dbo_Borrowers.BorrowerName;

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ConfusedNAccess]

Thanks for your help. But again, I'm new to this. Can you walk me through it?

Also,

What can you tell me about fields that are popping up in my query that are XML? How can I confert these particular fields to read as they should?

 

[PHV]

Have you read the above link and understood why I gave it to you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ConfusedNAccess]

Yes thank you. I'm reading and re-reading it now. Very valuable information. I'll review it and then apply this new knowledge to my DB. You're awesome! Thanks for your assistance!

 

[ConfusedNAccess]

ok, I don't have control as to the primary keys in any of the tables. They are linked tables to a program. I have a question.

I'm joining a borrower table to a borrower report table to a borrower actual report table.

the borrower table shows the name... borrower report.. shows report data, and actual report.. shows if it's active, if it's been received, due date, etc.

I've just noticed that in the borrower table, the last 2 (borrower reports & Borrower Actual Reports) are listed in sub data sheets. Will this make my life any easier?

Can I completely re-write my query, since all the info is housed in one table?