If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Too Complex with Total line
I've had this query working in the past, but I made some adjustments, and now
the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
#2
|
|||
|
|||
Query Too Complex with Total line
I try to never reference an alias in another column in a query. I try to
repeat the entire expression. In your first query, you create CurrentLookup and StartLookup and then later use: [CurrentLookup]-[StartLookup] I don't know why you need complex expressions like: Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) Are you actually storing string values for month rather than a month number? This creates a ton of extra complexity. -- Duane Hookom Microsoft Access MVP "kratz" wrote: I've had this query working in the past, but I made some adjustments, and now the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
#3
|
|||
|
|||
Query Too Complex with Total line
The reason for the month lookup is that we record our months as yymm, so I
used the lookup so I can subtract months. (I worked awhile on that and couldn't figure out any other way.) My MonthIndex is an AutoNumber in the table that lists all months for several years. "Duane Hookom" wrote: I try to never reference an alias in another column in a query. I try to repeat the entire expression. In your first query, you create CurrentLookup and StartLookup and then later use: [CurrentLookup]-[StartLookup] I don't know why you need complex expressions like: Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) Are you actually storing string values for month rather than a month number? This creates a ton of extra complexity. -- Duane Hookom Microsoft Access MVP "kratz" wrote: I've had this query working in the past, but I made some adjustments, and now the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
#4
|
|||
|
|||
Query Too Complex with Total line
I don't understand why you would do math with an autonumber. This sounds very
error prone since you should never use the value of an autonumber for anything other than a unique value. Did you try what I suggested by not using the aliases? -- Duane Hookom Microsoft Access MVP "kratz" wrote: The reason for the month lookup is that we record our months as yymm, so I used the lookup so I can subtract months. (I worked awhile on that and couldn't figure out any other way.) My MonthIndex is an AutoNumber in the table that lists all months for several years. "Duane Hookom" wrote: I try to never reference an alias in another column in a query. I try to repeat the entire expression. In your first query, you create CurrentLookup and StartLookup and then later use: [CurrentLookup]-[StartLookup] I don't know why you need complex expressions like: Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) Are you actually storing string values for month rather than a month number? This creates a ton of extra complexity. -- Duane Hookom Microsoft Access MVP "kratz" wrote: I've had this query working in the past, but I made some adjustments, and now the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
#5
|
|||
|
|||
Query Too Complex with Total line
New SQL for first query:
SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)[NoOfMonths]),((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); This query and the next work fine. It's the third when I try to group and sum that won't work. "Duane Hookom" wrote: I don't understand why you would do math with an autonumber. This sounds very error prone since you should never use the value of an autonumber for anything other than a unique value. Did you try what I suggested by not using the aliases? -- Duane Hookom Microsoft Access MVP "kratz" wrote: The reason for the month lookup is that we record our months as yymm, so I used the lookup so I can subtract months. (I worked awhile on that and couldn't figure out any other way.) My MonthIndex is an AutoNumber in the table that lists all months for several years. "Duane Hookom" wrote: I try to never reference an alias in another column in a query. I try to repeat the entire expression. In your first query, you create CurrentLookup and StartLookup and then later use: [CurrentLookup]-[StartLookup] I don't know why you need complex expressions like: Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) Are you actually storing string values for month rather than a month number? This creates a ton of extra complexity. -- Duane Hookom Microsoft Access MVP "kratz" wrote: I've had this query working in the past, but I made some adjustments, and now the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
#6
|
|||
|
|||
Query Too Complex with Total line
You are still using an alias in another expression. Also if [Customer Number]
and [Agent Number] are text then use: Deductions![Customer Number] & Deductions![Agent Number] AS [CustAgt Number] Can you provide any background on why you do all the month lookups etc? What are you attempting to do with this? I would probably pull the larger, complex, hard to maintain expressions and put them in a user defined function. -- Duane Hookom Microsoft Access MVP "kratz" wrote: New SQL for first query: SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)[NoOfMonths]),((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); This query and the next work fine. It's the third when I try to group and sum that won't work. "Duane Hookom" wrote: I don't understand why you would do math with an autonumber. This sounds very error prone since you should never use the value of an autonumber for anything other than a unique value. Did you try what I suggested by not using the aliases? -- Duane Hookom Microsoft Access MVP "kratz" wrote: The reason for the month lookup is that we record our months as yymm, so I used the lookup so I can subtract months. (I worked awhile on that and couldn't figure out any other way.) My MonthIndex is an AutoNumber in the table that lists all months for several years. "Duane Hookom" wrote: I try to never reference an alias in another column in a query. I try to repeat the entire expression. In your first query, you create CurrentLookup and StartLookup and then later use: [CurrentLookup]-[StartLookup] I don't know why you need complex expressions like: Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) Are you actually storing string values for month rather than a month number? This creates a ton of extra complexity. -- Duane Hookom Microsoft Access MVP "kratz" wrote: I've had this query working in the past, but I made some adjustments, and now the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
#7
|
|||
|
|||
Query Too Complex with Total line
New SQL:
SELECT Deductions.[Customer Number], Deductions.[Agent Number], [Deductions]![Customer Number] & [Deductions]![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'"))))+1)(IIf([Deductions]![Updated]=Yes,CInt([Deductions]![UpdatedNoMonths]),CInt([Deductions]![OriginalNoMonths])))),((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'"))))+1)*[OriginalMoDeduction]),(IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedTotalDeduction],[Deductions]![OriginalTotalDeduction])))) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); An agent number has a deduction for a specific account number. The deduction can start at any month for any number of months. Example: one agent starts a deduction for $10/ month in July for 6 months. This would be recorded as starting 0907, for 6 months, $10/month, total is $60. As of October (0910), the expected deduction would be $40. In this same account, an agent could have a deduction for $50 for one month starting in July. This would be recorded as starting 0907, 1 month, $50/month, total is $50. As of October (0910), the expected deduction would be $50. The reason I created a lookup was because of the deductions that span over more than one year (starting in November 2009 (0911) and ending in April 2010 (1004)). This creates an issue when trying to subtract to get the expected amount (1004 - 0911 = 93, not 6 months). I thought the lookup was the best way. How would I create the user defined function? I'm guess ExpToDate would be the complicated expression you are talking about. Thanks "Duane Hookom" wrote: You are still using an alias in another expression. Also if [Customer Number] and [Agent Number] are text then use: Deductions![Customer Number] & Deductions![Agent Number] AS [CustAgt Number] Can you provide any background on why you do all the month lookups etc? What are you attempting to do with this? I would probably pull the larger, complex, hard to maintain expressions and put them in a user defined function. -- Duane Hookom Microsoft Access MVP "kratz" wrote: New SQL for first query: SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)[NoOfMonths]),((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); This query and the next work fine. It's the third when I try to group and sum that won't work. "Duane Hookom" wrote: I don't understand why you would do math with an autonumber. This sounds very error prone since you should never use the value of an autonumber for anything other than a unique value. Did you try what I suggested by not using the aliases? -- Duane Hookom Microsoft Access MVP "kratz" wrote: The reason for the month lookup is that we record our months as yymm, so I used the lookup so I can subtract months. (I worked awhile on that and couldn't figure out any other way.) My MonthIndex is an AutoNumber in the table that lists all months for several years. "Duane Hookom" wrote: I try to never reference an alias in another column in a query. I try to repeat the entire expression. In your first query, you create CurrentLookup and StartLookup and then later use: [CurrentLookup]-[StartLookup] I don't know why you need complex expressions like: Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) Are you actually storing string values for month rather than a month number? This creates a ton of extra complexity. -- Duane Hookom Microsoft Access MVP "kratz" wrote: I've had this query working in the past, but I made some adjustments, and now the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
#8
|
|||
|
|||
Query Too Complex with Total line
I would create a function that has arguments to accept these values:
[Forms]![frmAgentDifferences]![cboSelectMonth] [Deductions]![Updated] [Deductions]![UpdatedStartMonth] [Deductions]![OriginalStartMonth] [Deductions]![UpdatedNoMonths] [Deductions]![OriginalNoMonths] [OriginalMoDeduction] [Deductions]![UpdatedTotalDeduction] [Deductions]![OriginalTotalDeduction] The function will return a value for ExpToDate. It looks like you wrap this value in CCur() but ExpToDate suggests it is a date data type. I'm confused. Your entire query might work if you simply declare the data type of your cboSelectMonth in the Query Parameters. A simple function is easy to create. For instance Public Function TotCost (curPrice as Currency, dblQty as Double) as Currency 'function to return the price times quantity 'arguments will not accept nulls 'curPrice is the unit price 'dblQty is the quanity 'notice I am typing in comments to document the function TotCost = curPrice * dblQty 'Return the Price * Qty End Function If you have field [UnitPrice] and [PurchQty] in your query, you can get the total cost by using a column like: TotalCost: TotCost([UnitPrice], [PurchQty]) -- Duane Hookom Microsoft Access MVP "kratz" wrote: New SQL: SELECT Deductions.[Customer Number], Deductions.[Agent Number], [Deductions]![Customer Number] & [Deductions]![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'"))))+1)(IIf([Deductions]![Updated]=Yes,CInt([Deductions]![UpdatedNoMonths]),CInt([Deductions]![OriginalNoMonths])))),((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'"))))+1)*[OriginalMoDeduction]),(IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedTotalDeduction],[Deductions]![OriginalTotalDeduction])))) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); An agent number has a deduction for a specific account number. The deduction can start at any month for any number of months. Example: one agent starts a deduction for $10/ month in July for 6 months. This would be recorded as starting 0907, for 6 months, $10/month, total is $60. As of October (0910), the expected deduction would be $40. In this same account, an agent could have a deduction for $50 for one month starting in July. This would be recorded as starting 0907, 1 month, $50/month, total is $50. As of October (0910), the expected deduction would be $50. The reason I created a lookup was because of the deductions that span over more than one year (starting in November 2009 (0911) and ending in April 2010 (1004)). This creates an issue when trying to subtract to get the expected amount (1004 - 0911 = 93, not 6 months). I thought the lookup was the best way. How would I create the user defined function? I'm guess ExpToDate would be the complicated expression you are talking about. Thanks "Duane Hookom" wrote: You are still using an alias in another expression. Also if [Customer Number] and [Agent Number] are text then use: Deductions![Customer Number] & Deductions![Agent Number] AS [CustAgt Number] Can you provide any background on why you do all the month lookups etc? What are you attempting to do with this? I would probably pull the larger, complex, hard to maintain expressions and put them in a user defined function. -- Duane Hookom Microsoft Access MVP "kratz" wrote: New SQL for first query: SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)[NoOfMonths]),((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); This query and the next work fine. It's the third when I try to group and sum that won't work. "Duane Hookom" wrote: I don't understand why you would do math with an autonumber. This sounds very error prone since you should never use the value of an autonumber for anything other than a unique value. Did you try what I suggested by not using the aliases? -- Duane Hookom Microsoft Access MVP "kratz" wrote: The reason for the month lookup is that we record our months as yymm, so I used the lookup so I can subtract months. (I worked awhile on that and couldn't figure out any other way.) My MonthIndex is an AutoNumber in the table that lists all months for several years. "Duane Hookom" wrote: I try to never reference an alias in another column in a query. I try to repeat the entire expression. In your first query, you create CurrentLookup and StartLookup and then later use: [CurrentLookup]-[StartLookup] I don't know why you need complex expressions like: Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) Are you actually storing string values for month rather than a month number? This creates a ton of extra complexity. -- Duane Hookom Microsoft Access MVP "kratz" wrote: I've had this query working in the past, but I made some adjustments, and now the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
#9
|
|||
|
|||
Query Too Complex with Total line
So I have created my functions to calculate the expected deduction per
deduction per agent number per account number. The problem I am having now is that I need to sum up the deductions per agent number per account number. (Each agent may have multiple deductions in the account.) I was working on this DSum, but am getting a not defined error on DeductConcate: Public Function ExpectedDeductionsSum() As Variant ExpectedDeductionsSum = DSum("[ExpectedDeductionsToDate]", "[qryExpectedDeductions]", "[DeductConcate] = '" & [DeductConcate] & "'") End Function The final function that combines the others: Public Function ExpectedDeductions(CurrentLookup As String, FinalStartMonthLookup As String, FinalNoMonths As String, FinalMoDeduction As Currency, FinalTotalDeduction As Currency) As Currency 'Determine Expected Deductions If ((CurrentLookup - FinalStartMonthLookup) + 1) = 0 Then ExpectedDeductions = 0 Else If ((CurrentLookup - FinalStartMonthLookup) + 1) 0 And ((CurrentLookup - FinalStartMonthLookup) + 1) FinalNoMonths Then ExpectedDeductions = ((CurrentLookup - FinalStartMonthLookup) + 1) * FinalMoDeduction Else ExpectedDeductions = FinalTotalDeduction End If End If End Function CurrentLookup is a DLookup entered into the expression Public Function FinalStartMonthLookup(FinalStartMonth As String) As String 'Determine the Start Month Lookup from the Months table FinalStartMonthLookup = "SELECT [Months.MonthIndex] " & _ "FROM [Months] " & _ "WHERE [Months.Month] = '" & FinalStartMonth & "' " & _ "ORDER BY [Months.MonthIndex];" End Function Public Function FinalNoMonths(ONoMonths As Variant, UNoMonths As Variant, UpdatedMonth As Boolean) As Variant 'Determine Final Number of Months for each deduction If UpdatedMonth = True Then FinalNoMonths = UNoMonths Else FinalNoMonths = ONoMonths End If End Function Public Function FinalMoDeduction(OMoDeduction As Variant, UMoDeduction As Variant, UpdatedMonth As Boolean) As Variant If UpdatedMonth = True Then FinalMoDeduction = UMoDeduction Else FinalMoDeduction = OMoDeduction End If End Function Public Function FinalTotalDeduction(OTotalDeduction As Variant, UTotalDeduction As Variant, UpdatedMonth As Boolean) As Variant If UpdatedMonth = True Then FinalTotalDeduction = UTotalDeduction Else FinalTotalDeduction = OTotalDeduction End If End Function I have tried to create a query and use the Total line to Group By the agent number / account number and Sum the deduction amount, but it is too complex. Any other suggestions? Thanks "Duane Hookom" wrote: I would create a function that has arguments to accept these values: [Forms]![frmAgentDifferences]![cboSelectMonth] [Deductions]![Updated] [Deductions]![UpdatedStartMonth] [Deductions]![OriginalStartMonth] [Deductions]![UpdatedNoMonths] [Deductions]![OriginalNoMonths] [OriginalMoDeduction] [Deductions]![UpdatedTotalDeduction] [Deductions]![OriginalTotalDeduction] The function will return a value for ExpToDate. It looks like you wrap this value in CCur() but ExpToDate suggests it is a date data type. I'm confused. Your entire query might work if you simply declare the data type of your cboSelectMonth in the Query Parameters. A simple function is easy to create. For instance Public Function TotCost (curPrice as Currency, dblQty as Double) as Currency 'function to return the price times quantity 'arguments will not accept nulls 'curPrice is the unit price 'dblQty is the quanity 'notice I am typing in comments to document the function TotCost = curPrice * dblQty 'Return the Price * Qty End Function If you have field [UnitPrice] and [PurchQty] in your query, you can get the total cost by using a column like: TotalCost: TotCost([UnitPrice], [PurchQty]) -- Duane Hookom Microsoft Access MVP "kratz" wrote: New SQL: SELECT Deductions.[Customer Number], Deductions.[Agent Number], [Deductions]![Customer Number] & [Deductions]![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'"))))+1)(IIf([Deductions]![Updated]=Yes,CInt([Deductions]![UpdatedNoMonths]),CInt([Deductions]![OriginalNoMonths])))),((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & (IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedStartMonth],[Deductions]![OriginalStartMonth])) & "'"))))+1)*[OriginalMoDeduction]),(IIf([Deductions]![Updated]=Yes,[Deductions]![UpdatedTotalDeduction],[Deductions]![OriginalTotalDeduction])))) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); An agent number has a deduction for a specific account number. The deduction can start at any month for any number of months. Example: one agent starts a deduction for $10/ month in July for 6 months. This would be recorded as starting 0907, for 6 months, $10/month, total is $60. As of October (0910), the expected deduction would be $40. In this same account, an agent could have a deduction for $50 for one month starting in July. This would be recorded as starting 0907, 1 month, $50/month, total is $50. As of October (0910), the expected deduction would be $50. The reason I created a lookup was because of the deductions that span over more than one year (starting in November 2009 (0911) and ending in April 2010 (1004)). This creates an issue when trying to subtract to get the expected amount (1004 - 0911 = 93, not 6 months). I thought the lookup was the best way. How would I create the user defined function? I'm guess ExpToDate would be the complicated expression you are talking about. Thanks "Duane Hookom" wrote: You are still using an alias in another expression. Also if [Customer Number] and [Agent Number] are text then use: Deductions![Customer Number] & Deductions![Agent Number] AS [CustAgt Number] Can you provide any background on why you do all the month lookups etc? What are you attempting to do with this? I would probably pull the larger, complex, hard to maintain expressions and put them in a user defined function. -- Duane Hookom Microsoft Access MVP "kratz" wrote: New SQL for first query: SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)[NoOfMonths]),((((Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")))-(Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'"))))+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); This query and the next work fine. It's the third when I try to group and sum that won't work. "Duane Hookom" wrote: I don't understand why you would do math with an autonumber. This sounds very error prone since you should never use the value of an autonumber for anything other than a unique value. Did you try what I suggested by not using the aliases? -- Duane Hookom Microsoft Access MVP "kratz" wrote: The reason for the month lookup is that we record our months as yymm, so I used the lookup so I can subtract months. (I worked awhile on that and couldn't figure out any other way.) My MonthIndex is an AutoNumber in the table that lists all months for several years. "Duane Hookom" wrote: I try to never reference an alias in another column in a query. I try to repeat the entire expression. In your first query, you create CurrentLookup and StartLookup and then later use: [CurrentLookup]-[StartLookup] I don't know why you need complex expressions like: Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) Are you actually storing string values for month rather than a month number? This creates a ton of extra complexity. -- Duane Hookom Microsoft Access MVP "kratz" wrote: I've had this query working in the past, but I made some adjustments, and now the query is too complex. I am trying to group/sum per agent number per account. SELECT qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], Sum(qryCurrentExpDeductions.ExpToDate) AS SumOfExpToDate, qryCurrentExpDeductions.Comments FROM qryCurrentExpDeductions GROUP BY qryCurrentExpDeductions.[CustAgt Number], qryCurrentExpDeductions.[Customer Number], qryCurrentExpDeductions.[Agent Number], qryCurrentExpDeductions.Comments; Underlying Query 'qryCurrentExpDeductions': SELECT qryExpectedDeductions.[CustAgt Number], qryExpectedDeductions.[Customer Number], qryExpectedDeductions.[Agent Number], qryExpectedDeductions.ExpToDate, qryExpectedDeductions.Comments FROM qryExpectedDeductions WHERE (((qryExpectedDeductions.StartLookup)[qryExpectedDeductions]![CurrentLookup] Or (qryExpectedDeductions.StartLookup)=[qryExpectedDeductions]![CurrentLookup])); Underlying Query 'qryExpectedDeductions': SELECT Deductions.[Customer Number], Deductions.[Agent Number], Deductions![Customer Number]+Deductions![Agent Number] AS [CustAgt Number], IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDed uctions,Deductions!OriginalMoDeduction) AS MonthlyDeduction, IIf(Deductions!Updated=Yes,Deductions!UpdatedTotal Deduction,Deductions!OriginalTotalDeduction) AS TotalDeduct, IIf(Deductions!Updated=Yes,CInt(Deductions!Updated NoMonths),CInt(Deductions!OriginalNoMonths)) AS NoOfMonths, IIf(Deductions!Updated=Yes,Deductions!UpdatedStart Month,Deductions!OriginalStartMonth) AS StartMonth, CStr(Forms!frmAgentDifferences!cboSelectMonth) AS CurrentMonth, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [StartMonth] & "'")) AS StartLookup, Int(DLookUp("[Months]![MonthIndex]","[Months]","[Months]![Month] = '" & [Forms]![frmAgentDifferences]![cboSelectMonth] & "'")) AS CurrentLookup, -CCur(IIf(((([CurrentLookup]-[StartLookup])+1)[NoOfMonths]),((([CurrentLookup]-[StartLookup])+1)*[OriginalMoDeduction]),[TotalDeduct])) AS ExpToDate, Deductions.Comments FROM tblSOAccountInfo INNER JOIN (Deductions INNER JOIN Months ON Deductions.OriginalStartMonth = Months.Month) ON (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) AND (tblSOAccountInfo.[Customer Number] = Deductions.[Customer Number]) WHERE (((tblSOAccountInfo.[Open/Closed])=No)); Originally, I had this in just two queries, but I was having a 'too complex' issue, so I split it so that I could at least get part of this done. Thanks for the help! |
Thread Tools | |
Display Modes | |
|
|