Jerry Whittle said:There's only one practical way for us to know: Show us the entire SQL
statement. Open the query in design view. Next go to View, SQL View and copy
and past it here. Information on primary keys and relationships would be a
nice touch too.
A better description of the problem such as what it being returned and what
you want to see returned would help too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
ewood said:'m using Access 2003 and my queries are summing a portion of my data and the
others are left blank. Below is a copy of the query with issues:
Sum(IIf([NSS]="Pilot" And [Rec Month]=#3/1/2007#,1,Null))
KARL DEWEY said:What data does [Rec Month] contain? Is it a DateTime field?
--
KARL DEWEY
Build a little - Test a little
ewood said:'m using Access 2003 and my queries are summing a portion of my data and the
others are left blank. Below is a copy of the query with issues:
Sum(IIf([NSS]="Pilot" And [Rec Month]=#3/1/2007#,1,Null))
ewood said:Jerry,
Thanks for your reply...I'm looking for the fields in the pnc pilot
submission field (blank for rose and hopkins leaders) to have a number but
it's missing ( see total submission field). Below is the sql statement
(input any work day number 1,2...)
SELECT tbl_Team_Leader.[Team Leader], tbl_Team_Leader.[Team Captain],
tbl_Loan_Officer.[Loan Officer], Max([Mar 2007 Team Goal Submissions].[Active
LCs]) AS [MaxOfActive LCs], Max([Mar 2007 Leads by LO].Leads) AS MaxOfLeads,
Max(([Leads]/22)*[Working Days]) AS [Projected Leads], Max([Mar 2007 Leads by
LO].[Actual Leads]) AS [MaxOfActual Leads], Max([Mar 2007 TMO Loan Officer
Entries].[Prev Mo Total Submissions]) AS [MaxOfPrev Mo Total Submissions],
Max(IIf([nss-mem].NSS="Pilot",([Working Days])*2,[Working Days])) AS
[Projected Submissions], Max([Mar 2007 TMO Loan Officer Entries].Submissions)
AS [FH Submissions], Sum(IIf([NSS]="Pilot" And [Rec
Month]=#3/1/2007#,1,Null)) AS [PNC Pilot Submissions], (Sum(IIf([Rec
Month]=#3/1/2007#,1,0)))+[FH Submissions] AS [Total Submissions], ([Total
Submissions]/[Working Days]) AS [Sub per Day], ([Total Submissions]/[Working
Days])/([MaxOfActive LCs]) AS [Sub per Day1], [Total Submissions]/Max([Actual
Leads]) AS [Sub/Leads], Max([Mar 2007 Leads by LO].[Prev Mo Closings]) AS
[MaxOfPrev Mo Closings], Max([Mar 2007 Leads by LO].Closings) AS [Proj
Closings], Max([Mar 2007 TMO Loan Officer Entries].Closings) AS [FH
Closings], Sum(IIf([NSS]="Pilot" And [Status]="BKD" And [Status
Month]=#3/1/2007#,1,Null)) AS [PNC Pilot Closings], Sum(IIf([Status
Month]=#3/1/2007# And [Status]="BKD",1,0))+[FH Closings] AS [Total Closings],
[Total Closings]/(Max([Prev Mo Total Submissions])) AS [Closings/Subs],
(Sum(IIf([Status Month]=#3/1/2007# And [Status]="BKD",1,0))+[FH
Closings])/(Max([Actual Leads])) AS [Closings/Leads], Max([Mar 2007 TMO Loan
Officer Entries].[Closings Loan Amt]) AS [FH Closing Volume],
Sum(IIf([NSS]="Pilot" And [Status Month]=#3/1/2007# And
[Status]="BKD",([Appvd Amount]),Null)) AS [PNC Pilot Closed Volume],
Sum((IIf([Status Month]=#3/1/2007# And [Status]="BKD",([Appvd
Amount]),0)))+[FH Closing Volume] AS [Total Closed Volume],
Avg(IIf([Status]="BKD",[Status Date]-[Rec Date],Null)) AS [Average Turn
Time], Max([Leads]/22)*[Working Days] AS [Projected Leads2], ([Total
Submissions]/[Working Days])/([MaxOfTeam Roll]) AS [Sub per day2], Max([Mar
2007 Team Goal Submissions].[Team Roll]) AS [MaxOfTeam Roll], Count(IIf([Pck
Ind]="A" And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg],
Count(IIf([Pck Ind]="B" And [Status Month]=#3/1/2007#,1,Null)) AS [Price
Pkg1], Count(IIf([Pck Ind]="C" And [Status Month]=#3/1/2007#,1,Null)) AS
[Price Pkg2], Count(IIf([Pck Ind]="D" And [Status Month]=#3/1/2007#,1,Null))
AS [Price Pkg3], Count(IIf([Pck Ind]="E" And [Status
Month]=#3/1/2007#,1,Null)) AS [Price Pkg4], Count(IIf([Pck Ind]="F" And
[Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg5], Count(IIf([Pck Ind]="G"
And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg6], Count(IIf([Pck
Ind]="N" And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg7]
FROM ((((tbl_Loan_Officer LEFT JOIN tbl_Team_Leader ON
tbl_Loan_Officer.[Loan Officer] = tbl_Team_Leader.[Loan Officer]) LEFT JOIN
[Mar 2007 Leads by LO] ON tbl_Loan_Officer.[Loan Officer] = [Mar 2007 Leads
by LO].[Loan Officer]) LEFT JOIN [Mar 2007 TMO Loan Officer Entries] ON
tbl_Loan_Officer.[Loan Officer] = [Mar 2007 TMO Loan Officer Entries].[Loan
Officer]) LEFT JOIN [Mar 2007 Team Goal Submissions] ON
(tbl_Team_Leader.[Team Leader] = [Mar 2007 Team Goal Submissions].[Team
Leader]) AND (tbl_Team_Leader.[Team Captain] = [Mar 2007 Team Goal
Submissions].[Team Captain])) LEFT JOIN [nss-mem] ON tbl_Loan_Officer.[Loan
Officer] = [nss-mem].LO
GROUP BY tbl_Team_Leader.[Team Leader], tbl_Team_Leader.[Team Captain],
tbl_Loan_Officer.[Loan Officer]
HAVING (((tbl_Loan_Officer.[Loan Officer])<>"#N/A"))
ORDER BY tbl_Team_Leader.[Team Leader] DESC;
Jerry Whittle said:There's only one practical way for us to know: Show us the entire SQL
statement. Open the query in design view. Next go to View, SQL View and copy
and past it here. Information on primary keys and relationships would be a
nice touch too.
A better description of the problem such as what it being returned and what
you want to see returned would help too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
ewood said:'m using Access 2003 and my queries are summing a portion of my data and the
others are left blank. Below is a copy of the query with issues:
Sum(IIf([NSS]="Pilot" And [Rec Month]=#3/1/2007#,1,Null))
ewood said:Karl,
Thanks for your reply...below is a copy of the sql. I hope this helps!!
SELECT tbl_Team_Leader.[Team Leader], tbl_Team_Leader.[Team Captain],
tbl_Loan_Officer.[Loan Officer], Max([Mar 2007 Team Goal Submissions].[Active
LCs]) AS [MaxOfActive LCs], Max([Mar 2007 Leads by LO].Leads) AS MaxOfLeads,
Max(([Leads]/22)*[Working Days]) AS [Projected Leads], Max([Mar 2007 Leads by
LO].[Actual Leads]) AS [MaxOfActual Leads], Max([Mar 2007 TMO Loan Officer
Entries].[Prev Mo Total Submissions]) AS [MaxOfPrev Mo Total Submissions],
Max(IIf([nss-mem].NSS="Pilot",([Working Days])*2,[Working Days])) AS
[Projected Submissions], Max([Mar 2007 TMO Loan Officer Entries].Submissions)
AS [FH Submissions], Sum(IIf([NSS]="Pilot" And [Rec
Month]=#3/1/2007#,1,Null)) AS [PNC Pilot Submissions], (Sum(IIf([Rec
Month]=#3/1/2007#,1,0)))+[FH Submissions] AS [Total Submissions], ([Total
Submissions]/[Working Days]) AS [Sub per Day], ([Total Submissions]/[Working
Days])/([MaxOfActive LCs]) AS [Sub per Day1], [Total Submissions]/Max([Actual
Leads]) AS [Sub/Leads], Max([Mar 2007 Leads by LO].[Prev Mo Closings]) AS
[MaxOfPrev Mo Closings], Max([Mar 2007 Leads by LO].Closings) AS [Proj
Closings], Max([Mar 2007 TMO Loan Officer Entries].Closings) AS [FH
Closings], Sum(IIf([NSS]="Pilot" And [Status]="BKD" And [Status
Month]=#3/1/2007#,1,Null)) AS [PNC Pilot Closings], Sum(IIf([Status
Month]=#3/1/2007# And [Status]="BKD",1,0))+[FH Closings] AS [Total Closings],
[Total Closings]/(Max([Prev Mo Total Submissions])) AS [Closings/Subs],
(Sum(IIf([Status Month]=#3/1/2007# And [Status]="BKD",1,0))+[FH
Closings])/(Max([Actual Leads])) AS [Closings/Leads], Max([Mar 2007 TMO Loan
Officer Entries].[Closings Loan Amt]) AS [FH Closing Volume],
Sum(IIf([NSS]="Pilot" And [Status Month]=#3/1/2007# And
[Status]="BKD",([Appvd Amount]),Null)) AS [PNC Pilot Closed Volume],
Sum((IIf([Status Month]=#3/1/2007# And [Status]="BKD",([Appvd
Amount]),0)))+[FH Closing Volume] AS [Total Closed Volume],
Avg(IIf([Status]="BKD",[Status Date]-[Rec Date],Null)) AS [Average Turn
Time], Max([Leads]/22)*[Working Days] AS [Projected Leads2], ([Total
Submissions]/[Working Days])/([MaxOfTeam Roll]) AS [Sub per day2], Max([Mar
2007 Team Goal Submissions].[Team Roll]) AS [MaxOfTeam Roll], Count(IIf([Pck
Ind]="A" And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg],
Count(IIf([Pck Ind]="B" And [Status Month]=#3/1/2007#,1,Null)) AS [Price
Pkg1], Count(IIf([Pck Ind]="C" And [Status Month]=#3/1/2007#,1,Null)) AS
[Price Pkg2], Count(IIf([Pck Ind]="D" And [Status Month]=#3/1/2007#,1,Null))
AS [Price Pkg3], Count(IIf([Pck Ind]="E" And [Status
Month]=#3/1/2007#,1,Null)) AS [Price Pkg4], Count(IIf([Pck Ind]="F" And
[Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg5], Count(IIf([Pck Ind]="G"
And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg6], Count(IIf([Pck
Ind]="N" And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg7]
FROM ((((tbl_Loan_Officer LEFT JOIN tbl_Team_Leader ON
tbl_Loan_Officer.[Loan Officer] = tbl_Team_Leader.[Loan Officer]) LEFT JOIN
[Mar 2007 Leads by LO] ON tbl_Loan_Officer.[Loan Officer] = [Mar 2007 Leads
by LO].[Loan Officer]) LEFT JOIN [Mar 2007 TMO Loan Officer Entries] ON
tbl_Loan_Officer.[Loan Officer] = [Mar 2007 TMO Loan Officer Entries].[Loan
Officer]) LEFT JOIN [Mar 2007 Team Goal Submissions] ON
(tbl_Team_Leader.[Team Leader] = [Mar 2007 Team Goal Submissions].[Team
Leader]) AND (tbl_Team_Leader.[Team Captain] = [Mar 2007 Team Goal
Submissions].[Team Captain])) LEFT JOIN [nss-mem] ON tbl_Loan_Officer.[Loan
Officer] = [nss-mem].LO
GROUP BY tbl_Team_Leader.[Team Leader], tbl_Team_Leader.[Team Captain],
tbl_Loan_Officer.[Loan Officer]
HAVING (((tbl_Loan_Officer.[Loan Officer])<>"#N/A"))
ORDER BY tbl_Team_Leader.[Team Leader] DESC;
KARL DEWEY said:What data does [Rec Month] contain? Is it a DateTime field?
--
KARL DEWEY
Build a little - Test a little
ewood said:'m using Access 2003 and my queries are summing a portion of my data and the
others are left blank. Below is a copy of the query with issues:
Sum(IIf([NSS]="Pilot" And [Rec Month]=#3/1/2007#,1,Null))
KARL DEWEY said:You did not answer my question. If [Rec Month] is not a DateTime field and
the date is stored with any time after midnight it will not test as True.
--
KARL DEWEY
Build a little - Test a little
ewood said:Karl,
Thanks for your reply...below is a copy of the sql. I hope this helps!!
SELECT tbl_Team_Leader.[Team Leader], tbl_Team_Leader.[Team Captain],
tbl_Loan_Officer.[Loan Officer], Max([Mar 2007 Team Goal Submissions].[Active
LCs]) AS [MaxOfActive LCs], Max([Mar 2007 Leads by LO].Leads) AS MaxOfLeads,
Max(([Leads]/22)*[Working Days]) AS [Projected Leads], Max([Mar 2007 Leads by
LO].[Actual Leads]) AS [MaxOfActual Leads], Max([Mar 2007 TMO Loan Officer
Entries].[Prev Mo Total Submissions]) AS [MaxOfPrev Mo Total Submissions],
Max(IIf([nss-mem].NSS="Pilot",([Working Days])*2,[Working Days])) AS
[Projected Submissions], Max([Mar 2007 TMO Loan Officer Entries].Submissions)
AS [FH Submissions], Sum(IIf([NSS]="Pilot" And [Rec
Month]=#3/1/2007#,1,Null)) AS [PNC Pilot Submissions], (Sum(IIf([Rec
Month]=#3/1/2007#,1,0)))+[FH Submissions] AS [Total Submissions], ([Total
Submissions]/[Working Days]) AS [Sub per Day], ([Total Submissions]/[Working
Days])/([MaxOfActive LCs]) AS [Sub per Day1], [Total Submissions]/Max([Actual
Leads]) AS [Sub/Leads], Max([Mar 2007 Leads by LO].[Prev Mo Closings]) AS
[MaxOfPrev Mo Closings], Max([Mar 2007 Leads by LO].Closings) AS [Proj
Closings], Max([Mar 2007 TMO Loan Officer Entries].Closings) AS [FH
Closings], Sum(IIf([NSS]="Pilot" And [Status]="BKD" And [Status
Month]=#3/1/2007#,1,Null)) AS [PNC Pilot Closings], Sum(IIf([Status
Month]=#3/1/2007# And [Status]="BKD",1,0))+[FH Closings] AS [Total Closings],
[Total Closings]/(Max([Prev Mo Total Submissions])) AS [Closings/Subs],
(Sum(IIf([Status Month]=#3/1/2007# And [Status]="BKD",1,0))+[FH
Closings])/(Max([Actual Leads])) AS [Closings/Leads], Max([Mar 2007 TMO Loan
Officer Entries].[Closings Loan Amt]) AS [FH Closing Volume],
Sum(IIf([NSS]="Pilot" And [Status Month]=#3/1/2007# And
[Status]="BKD",([Appvd Amount]),Null)) AS [PNC Pilot Closed Volume],
Sum((IIf([Status Month]=#3/1/2007# And [Status]="BKD",([Appvd
Amount]),0)))+[FH Closing Volume] AS [Total Closed Volume],
Avg(IIf([Status]="BKD",[Status Date]-[Rec Date],Null)) AS [Average Turn
Time], Max([Leads]/22)*[Working Days] AS [Projected Leads2], ([Total
Submissions]/[Working Days])/([MaxOfTeam Roll]) AS [Sub per day2], Max([Mar
2007 Team Goal Submissions].[Team Roll]) AS [MaxOfTeam Roll], Count(IIf([Pck
Ind]="A" And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg],
Count(IIf([Pck Ind]="B" And [Status Month]=#3/1/2007#,1,Null)) AS [Price
Pkg1], Count(IIf([Pck Ind]="C" And [Status Month]=#3/1/2007#,1,Null)) AS
[Price Pkg2], Count(IIf([Pck Ind]="D" And [Status Month]=#3/1/2007#,1,Null))
AS [Price Pkg3], Count(IIf([Pck Ind]="E" And [Status
Month]=#3/1/2007#,1,Null)) AS [Price Pkg4], Count(IIf([Pck Ind]="F" And
[Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg5], Count(IIf([Pck Ind]="G"
And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg6], Count(IIf([Pck
Ind]="N" And [Status Month]=#3/1/2007#,1,Null)) AS [Price Pkg7]
FROM ((((tbl_Loan_Officer LEFT JOIN tbl_Team_Leader ON
tbl_Loan_Officer.[Loan Officer] = tbl_Team_Leader.[Loan Officer]) LEFT JOIN
[Mar 2007 Leads by LO] ON tbl_Loan_Officer.[Loan Officer] = [Mar 2007 Leads
by LO].[Loan Officer]) LEFT JOIN [Mar 2007 TMO Loan Officer Entries] ON
tbl_Loan_Officer.[Loan Officer] = [Mar 2007 TMO Loan Officer Entries].[Loan
Officer]) LEFT JOIN [Mar 2007 Team Goal Submissions] ON
(tbl_Team_Leader.[Team Leader] = [Mar 2007 Team Goal Submissions].[Team
Leader]) AND (tbl_Team_Leader.[Team Captain] = [Mar 2007 Team Goal
Submissions].[Team Captain])) LEFT JOIN [nss-mem] ON tbl_Loan_Officer.[Loan
Officer] = [nss-mem].LO
GROUP BY tbl_Team_Leader.[Team Leader], tbl_Team_Leader.[Team Captain],
tbl_Loan_Officer.[Loan Officer]
HAVING (((tbl_Loan_Officer.[Loan Officer])<>"#N/A"))
ORDER BY tbl_Team_Leader.[Team Leader] DESC;
KARL DEWEY said:What data does [Rec Month] contain? Is it a DateTime field?
--
KARL DEWEY
Build a little - Test a little
:
'm using Access 2003 and my queries are summing a portion of my data and the
others are left blank. Below is a copy of the query with issues:
Sum(IIf([NSS]="Pilot" And [Rec Month]=#3/1/2007#,1,Null))
Jerry Whittle said:There's only one practical way for us to know: Show us the entire SQL
statement. Open the query in design view. Next go to View, SQL View and copy
and past it here. Information on primary keys and relationships would be a
nice touch too.
A better description of the problem such as what it being returned and what
you want to see returned would help too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
ewood said:'m using Access 2003 and my queries are summing a portion of my data and the
others are left blank. Below is a copy of the query with issues:
Sum(IIf([NSS]="Pilot" And [Rec Month]=#3/1/2007#,1,Null))