Complicated Query problem

[Addy]

My MS SQL skills are under great test at the moment. I was wondering if
I could get some help on this problem.

So I have a Query which is pulling fields from different tables.
Below are the date elements of my query

FIELD NAMES

* Sum Of total Planned Sales (A sum of revenue from Table 1)

* Total Hrs (Total Air hours + Total Car hours + Total idle time) This
is also a summation field made up of three separate fields - Taken
from Table 2

* Functional ID - A ID assigned to a location - Taken from Table 1

I have data sets which look like the following

Functional ID Total Hours Sum of total Planned Sales
3344 66 $4500
3344 66 $4500
3346 66 $4500
3347 66 $4500
3347 66 $500
3349 66 $4500

This looks fine except when duplicate Funtcional IDs comes with a
diffent value for 'total planned sales. '

I want a query that will add the total hours per functional ID BUT if
there is a duplicate with a different 'Sum of Total planned Sales'
then I want the 'Total Hours' for the lower value of 'total
planned sales' equal to 0. Such is the case with Functional ID 3347.

 

[kingston via AccessMonster.com]

Create a query based on the existing query, output the first and last fields
directly, and use this calculation instead of Total Hours:

IIF([Sum...Sales]<DMax("[Sum...Sales]","[OriginalQuery]","[Functional ID]=" &
[Functional ID]),0,[Total Hours])

 

[Addy]

Thanks for the reply. I tried that but am getting the following errors.

First of all the Total Hrs field is displaying as #Error. Once I click
in that field this is what it says

"The expression you entered as a query parameter produced this error: '
The object does not contain the Automation object 'MJ'.
MJ part of the field in Functional ID

This is what my SQL looks like.

SELECT [Installed Base Eastern Region - Contract - MP].FunctLocation,
[Installed Base Eastern Region - Contract - MP].[SumOf*Total Planned
Sales], IIf([SumOf*Total Planned Sales]<DMax("[SumOf*Total Planned
Sales]","[Installed Base Eastern Region - Contract -
MP]","[FunctLocation]=" & [FunctLocation]),0,[Total Hrs]) AS Expr1,
[Installed Base Eastern Region - Contract - MP].[Total Hrs]
FROM [Installed Base Eastern Region - Contract - MP];

Create a query based on the existing query, output the first and last fields
directly, and use this calculation instead of Total Hours:

IIF([Sum...Sales]<DMax("[Sum...Sales]","[OriginalQuery]","[Functional ID]=" &
[Functional ID]),0,[Total Hours])

My MS SQL skills are under great test at the moment. I was wondering if
I could get some help on this problem.

So I have a Query which is pulling fields from different tables.
Below are the date elements of my query

FIELD NAMES

* Sum Of total Planned Sales (A sum of revenue from Table 1)

* Total Hrs (Total Air hours + Total Car hours + Total idle time) This
is also a summation field made up of three separate fields - Taken
from Table 2

* Functional ID - A ID assigned to a location - Taken from Table 1

I have data sets which look like the following

Functional ID Total Hours Sum of total Planned Sales
3344 66 $4500
3344 66 $4500
3346 66 $4500
3347 66 $4500
3347 66 $500
3349 66 $4500

This looks fine except when duplicate Funtcional IDs comes with a
diffent value for 'total planned sales. '

I want a query that will add the total hours per functional ID BUT if
there is a duplicate with a different 'Sum of Total planned Sales'
then I want the 'Total Hours' for the lower value of 'total
planned sales' equal to 0. Such is the case with Functional ID 3347.

 

[kingston via AccessMonster.com]

If [Functional ID] is not a number, you'll have to enclose it in quotes in
the query:

IIF([Sum...Sales]<DMax("[Sum...Sales]","[OriginalQuery]","[Functional ID]='"
& [Functional ID]) & "'",0,[Total Hours])

Thanks for the reply. I tried that but am getting the following errors.

First of all the Total Hrs field is displaying as #Error. Once I click
in that field this is what it says

"The expression you entered as a query parameter produced this error: '
The object does not contain the Automation object 'MJ'.
MJ part of the field in Functional ID

This is what my SQL looks like.

SELECT [Installed Base Eastern Region - Contract - MP].FunctLocation,
[Installed Base Eastern Region - Contract - MP].[SumOf*Total Planned
Sales], IIf([SumOf*Total Planned Sales]<DMax("[SumOf*Total Planned
Sales]","[Installed Base Eastern Region - Contract -
MP]","[FunctLocation]=" & [FunctLocation]),0,[Total Hrs]) AS Expr1,
[Installed Base Eastern Region - Contract - MP].[Total Hrs]
FROM [Installed Base Eastern Region - Contract - MP];

Create a query based on the existing query, output the first and last fields
directly, and use this calculation instead of Total Hours:

[quoted text clipped - 39 lines]

 

[Addy]

Thanks for the reply.

I think I am almost there.

The error message now is:

"Syntax error in string in query expressing '[FunctionalID] =
'555-444-555'.

This is what I have in the code section.

IIF([SumOf*Total Planned Sales]<DMax("[SumOf*Total Planned
Sales]","[Installed Base Eastern Region - Contract -
MP]","[FunctLocation]='"
& [FunctLocation]) & "'",0,[Total Hrs])

Please let me know where I am going wrong.

If [Functional ID] is not a number, you'll have to enclose it in quotes in
the query:

IIF([Sum...Sales]<DMax("[Sum...Sales]","[OriginalQuery]","[Functional ID]='"
& [Functional ID]) & "'",0,[Total Hours])

Thanks for the reply. I tried that but am getting the following errors.

First of all the Total Hrs field is displaying as #Error. Once I click
in that field this is what it says

"The expression you entered as a query parameter produced this error: '
The object does not contain the Automation object 'MJ'.
MJ part of the field in Functional ID

This is what my SQL looks like.

SELECT [Installed Base Eastern Region - Contract - MP].FunctLocation,
[Installed Base Eastern Region - Contract - MP].[SumOf*Total Planned
Sales], IIf([SumOf*Total Planned Sales]<DMax("[SumOf*Total Planned
Sales]","[Installed Base Eastern Region - Contract -
MP]","[FunctLocation]=" & [FunctLocation]),0,[Total Hrs]) AS Expr1,
[Installed Base Eastern Region - Contract - MP].[Total Hrs]
FROM [Installed Base Eastern Region - Contract - MP];

Create a query based on the existing query, output the first and last fields
directly, and use this calculation instead of Total Hours:

[quoted text clipped - 39 lines]

 

[kingston via AccessMonster.com]

This should be one long line:

IIF([SumOf*Total Planned Sales]<DMax("[SumOf*Total Planned Sales]","
[Installed Base Eastern Region - Contract - MP]","[FunctLocation]='" &
[FunctLocation] & "'),0,[Total Hrs])

Thanks for the reply.

I think I am almost there.

The error message now is:

"Syntax error in string in query expressing '[FunctionalID] =
'555-444-555'.

This is what I have in the code section.

IIF([SumOf*Total Planned Sales]<DMax("[SumOf*Total Planned
Sales]","[Installed Base Eastern Region - Contract -
MP]","[FunctLocation]='"
& [FunctLocation]) & "'",0,[Total Hrs])

Please let me know where I am going wrong.

If [Functional ID] is not a number, you'll have to enclose it in quotes in
the query:

[quoted text clipped - 30 lines]

 

[Addy]

Thanks a million. It seems to be working. Cheeers!!!

This should be one long line:

IIF([SumOf*Total Planned Sales]<DMax("[SumOf*Total Planned Sales]","
[Installed Base Eastern Region - Contract - MP]","[FunctLocation]='" &
[FunctLocation] & "'),0,[Total Hrs])

Thanks for the reply.

I think I am almost there.

The error message now is:

"Syntax error in string in query expressing '[FunctionalID] =
'555-444-555'.

This is what I have in the code section.

IIF([SumOf*Total Planned Sales]<DMax("[SumOf*Total Planned
Sales]","[Installed Base Eastern Region - Contract -
MP]","[FunctLocation]='"
& [FunctLocation]) & "'",0,[Total Hrs])

Please let me know where I am going wrong.

If [Functional ID] is not a number, you'll have to enclose it in quotes in
the query:

[quoted text clipped - 30 lines]