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
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