SQL Help

B

Bwion

Hey All,

I need some help in writing a complex SQL statement. First my table structure:

tblLabor:
DEFAULT_ORG_LEVEL1
DEFAULT_ORG_LEVEL2
DEFAULT_ORG_PTR
END_DT
ENTITY_NUM
EVENT
FUNCTION_CODE
HRS
HRS_CLASS
hRS_PAY_DESG
LABORER
OPERATION_NUM
ORDER_ID
PAID_ORG_LEVEL1
PAID_ORG_PTR
POST_DATE
SHIFT_NUM
START_DT
TRANSACT_KEY
WORK_ORDER ----
|
tblAircraft: |
WorkorderNum ----
AircraftID
AircraftType
SN
DN
Customer
DeliveryDate
EngDueDate
FlightLineXferDate
CategoryID ----
|
tblCategory: |
CategoryID ----
Type

Alright, "tblLabor" contains the manhour charges against an aircraft. The
field "HRS" are the hours charged. The field "DEFAULT_ORG_LEVEL1" has the
department that made the charge. The field "WORK_ORDER" is a unique number
given to an aircraft to charge to (ex. 08601 is one aircraft's work order
number). The "tblAircraft" each aircraft's information. The field
"AircraftType" determines which aircraft type that aircraft is. The field
"SN" is the aircraft's serial number. The field "WorkorderNum" is the same as
the field "WORK ORDER" in "tblLabor." The field "CategoryID" is a forgein key
from "tblCategory", which identifies an aircraft as a duplicate,
non-duplicate, or exotic.

Now, here is the output I want

AcftType Category AverageD210Hours AverageD220Hours
747 Dup #### ####
747 Non-Dup #### ####
747 Exotic #### ####
737 Dup #### ####
737 Non-Dup #### ####

"AcftType" comes from the "tblAircraft", the "AircraftType" field.
"Category" comes from the "tblCategory", the "Type" field. "AverageD210Hours"
is the fun one. D210 is denoted in the "DEFAULT_ORG_LEVEL1" field as "210".
Then I want the average of the sums (i.e. sum all the D210 hours for each
aircraft and then take the average of that).

Maybe I am looking at two queries, one to do the sum and then one to do the
average. I don't know where to start. I tried using the SUM function and then
adding a criteria (ex. [tblLabor]![DEFAULT_ORG_LEVEL1]="210"), but I get the
"You tried to execute a query that does not include the specified experssion
as part of an aggregate function.

Any tips/advice/code?

Thanks in advance,

Ben
 
K

Ken Sheridan

Ben:

The way to conditionally aggregate values is to aggregate the values
multiplied by the return value of an expression which returns 1 or 0, e.g. in
a query grouped by aircraft:

SumOfD210Hours:SUM([hrs]*IIF([default_org_level1] = "210",1,0))

You could then use another query based on the first query to get the average
per aircraft type, by grouping the second query on aircraft type.

It would be possible to do the whole thing in one query by incorporating
subqueries within it, but you'll probably find it easier to use separate
queries.

Ken Sheridan
Stafford, England

Bwion said:
Hey All,

I need some help in writing a complex SQL statement. First my table structure:

tblLabor:
DEFAULT_ORG_LEVEL1
DEFAULT_ORG_LEVEL2
DEFAULT_ORG_PTR
END_DT
ENTITY_NUM
EVENT
FUNCTION_CODE
HRS
HRS_CLASS
hRS_PAY_DESG
LABORER
OPERATION_NUM
ORDER_ID
PAID_ORG_LEVEL1
PAID_ORG_PTR
POST_DATE
SHIFT_NUM
START_DT
TRANSACT_KEY
WORK_ORDER ----
|
tblAircraft: |
WorkorderNum ----
AircraftID
AircraftType
SN
DN
Customer
DeliveryDate
EngDueDate
FlightLineXferDate
CategoryID ----
|
tblCategory: |
CategoryID ----
Type

Alright, "tblLabor" contains the manhour charges against an aircraft. The
field "HRS" are the hours charged. The field "DEFAULT_ORG_LEVEL1" has the
department that made the charge. The field "WORK_ORDER" is a unique number
given to an aircraft to charge to (ex. 08601 is one aircraft's work order
number). The "tblAircraft" each aircraft's information. The field
"AircraftType" determines which aircraft type that aircraft is. The field
"SN" is the aircraft's serial number. The field "WorkorderNum" is the same as
the field "WORK ORDER" in "tblLabor." The field "CategoryID" is a forgein key
from "tblCategory", which identifies an aircraft as a duplicate,
non-duplicate, or exotic.

Now, here is the output I want

AcftType Category AverageD210Hours AverageD220Hours
747 Dup #### ####
747 Non-Dup #### ####
747 Exotic #### ####
737 Dup #### ####
737 Non-Dup #### ####

"AcftType" comes from the "tblAircraft", the "AircraftType" field.
"Category" comes from the "tblCategory", the "Type" field. "AverageD210Hours"
is the fun one. D210 is denoted in the "DEFAULT_ORG_LEVEL1" field as "210".
Then I want the average of the sums (i.e. sum all the D210 hours for each
aircraft and then take the average of that).

Maybe I am looking at two queries, one to do the sum and then one to do the
average. I don't know where to start. I tried using the SUM function and then
adding a criteria (ex. [tblLabor]![DEFAULT_ORG_LEVEL1]="210"), but I get the
"You tried to execute a query that does not include the specified experssion
as part of an aggregate function.

Any tips/advice/code?

Thanks in advance,

Ben
Click to expand...
 
B

Bwion

Hey Ken,

Sounds like a winner. Thanks a bunch.

Ben

Ken Sheridan said:
Ben:

The way to conditionally aggregate values is to aggregate the values
multiplied by the return value of an expression which returns 1 or 0, e.g. in
a query grouped by aircraft:

SumOfD210Hours:SUM([hrs]*IIF([default_org_level1] = "210",1,0))

You could then use another query based on the first query to get the average
per aircraft type, by grouping the second query on aircraft type.

It would be possible to do the whole thing in one query by incorporating
subqueries within it, but you'll probably find it easier to use separate
queries.

Ken Sheridan
Stafford, England

Bwion said:
Hey All,

I need some help in writing a complex SQL statement. First my table structure:

tblLabor:
DEFAULT_ORG_LEVEL1
DEFAULT_ORG_LEVEL2
DEFAULT_ORG_PTR
END_DT
ENTITY_NUM
EVENT
FUNCTION_CODE
HRS
HRS_CLASS
hRS_PAY_DESG
LABORER
OPERATION_NUM
ORDER_ID
PAID_ORG_LEVEL1
PAID_ORG_PTR
POST_DATE
SHIFT_NUM
START_DT
TRANSACT_KEY
WORK_ORDER ----
|
tblAircraft: |
WorkorderNum ----
AircraftID
AircraftType
SN
DN
Customer
DeliveryDate
EngDueDate
FlightLineXferDate
CategoryID ----
|
tblCategory: |
CategoryID ----
Type

Alright, "tblLabor" contains the manhour charges against an aircraft. The
field "HRS" are the hours charged. The field "DEFAULT_ORG_LEVEL1" has the
department that made the charge. The field "WORK_ORDER" is a unique number
given to an aircraft to charge to (ex. 08601 is one aircraft's work order
number). The "tblAircraft" each aircraft's information. The field
"AircraftType" determines which aircraft type that aircraft is. The field
"SN" is the aircraft's serial number. The field "WorkorderNum" is the same as
the field "WORK ORDER" in "tblLabor." The field "CategoryID" is a forgein key
from "tblCategory", which identifies an aircraft as a duplicate,
non-duplicate, or exotic.

Now, here is the output I want

AcftType Category AverageD210Hours AverageD220Hours
747 Dup #### ####
747 Non-Dup #### ####
747 Exotic #### ####
737 Dup #### ####
737 Non-Dup #### ####

"AcftType" comes from the "tblAircraft", the "AircraftType" field.
"Category" comes from the "tblCategory", the "Type" field. "AverageD210Hours"
is the fun one. D210 is denoted in the "DEFAULT_ORG_LEVEL1" field as "210".
Then I want the average of the sums (i.e. sum all the D210 hours for each
aircraft and then take the average of that).

Maybe I am looking at two queries, one to do the sum and then one to do the
average. I don't know where to start. I tried using the SUM function and then
adding a criteria (ex. [tblLabor]![DEFAULT_ORG_LEVEL1]="210"), but I get the
"You tried to execute a query that does not include the specified experssion
as part of an aggregate function.

Any tips/advice/code?

Thanks in advance,

Ben
Click to expand...
Click to expand...
 
Top