Query Too Complex with Total line

  • Thread starter Thread starter kratz
  • Start date Start date
K

kratz

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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
 
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 said:
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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
Click to expand...
 
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 said:
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 said:
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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
Click to expand...
Click to expand...
Click to expand...
 
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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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 said:
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 said:
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 said:
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


:

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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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 said:
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 said:
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.

:

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


:

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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
Click to expand...
Click to expand...
Click to expand...
 
New SQL:

SELECT Deductions.[Customer Number], Deductions.[Agent Number],
[Deductions]![Customer Number] & [Deductions]![Agent Number] AS [CustAgt
Number],
IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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 said:
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 said:
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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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 said:
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


:

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.

:

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


:

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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
New SQL:

SELECT Deductions.[Customer Number], Deductions.[Agent Number],
[Deductions]![Customer Number] & [Deductions]![Agent Number] AS [CustAgt
Number],
IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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 said:
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 said:
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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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.



:

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


:

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.

:

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


:

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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
New SQL:

SELECT Deductions.[Customer Number], Deductions.[Agent Number],
[Deductions]![Customer Number] & [Deductions]![Agent Number] AS [CustAgt
Number],
IIf(Deductions!Updated=Yes,Deductions!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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 said:
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


:

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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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.



:

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


:

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.

:

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


:

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!UpdatedMoDeductions,Deductions!OriginalMoDeduction)
AS MonthlyDeduction,
IIf(Deductions!Updated=Yes,Deductions!UpdatedTotalDeduction,Deductions!OriginalTotalDeduction)
AS TotalDeduct,
IIf(Deductions!Updated=Yes,CInt(Deductions!UpdatedNoMonths),CInt(Deductions!OriginalNoMonths))
AS NoOfMonths,
IIf(Deductions!Updated=Yes,Deductions!UpdatedStartMonth,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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top