Scratch my last thread, different question

[PavelowFE]

Need a query to output a sum of values, dependant on a different value in the record.
i.e.:
My table contains fields "Type of Aircraft" and "Aircrew per aircraft". The values for "Type of Aircraft" are AC-130, A-10, F-16, MH-60, etc.
The values for "Aircrew per Aircraft" are a number based on how many aircrew members are on an aircraft (i.e AC-130 is 14, A-10 is 1, etc).

I want to build a query that will total (sum) all of the "Aircrew per Aircraft", but grouped by the type of aircraft.
Thanks,

Vince

 

[PavelowFE]

Correction to the post above, my fields are "Asset Used" and "# of Aircrew Trained".

Here's where I'm at:

SELECT Sum([# of Aircrew Trained])(IIf([Asset Used]='AC_130H',1,0)), Sum([# of Aircrew Trained])(IIf([Asset Used]='CH-53E',1,0)) AS [CH_53E]
FROM [Mission Report]
WHERE ([Mission Report].[Date Tng Completed] Between #10/1/2008# And #9/30/2009#)

I know this isn't correct, but it might help you figure it out.

 

[lameid]

There are no implicit operators...

(x) is not a product...

(x)* is a product.

 

[PavelowFE]

Sorry, you're above my head on that.

 

[alvechurchdata]

I think lameid's referring to this snippet:

Sum([# of Aircrew Trained])(IIf([Asset Used]='AC_130H',1,0))


where you need something between

Sum([# of Aircrew Trained])

and

(IIf([Asset Used]='AC_130H',1,0))

perhaps a "*" if you're multiplying or a "," if they're two fields.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

total (sum) all of the "Aircrew per Aircraft", but grouped by the type of aircraft

SELECT [Asset Used], Sum([# of Aircrew Trained]) AS NumberOfTrained
FROM [Mission Report]
WHERE [Date Tng Completed] Between #2008-10-01# And #2009-09-30#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PavelowFE]

Hi PH,
I tried using your code, but it didn't work. Here's what I got:

"You tried to execute that does not include the specified expression 'Asset Used' as part of an aggregate function."

Vince

 

[PavelowFE]

This code gives me the information I'm looking for, but I can't get into my report.


SELECT DISTINCTROW [Mission Report].[Asset Used], Sum([Mission Report].[# of Aircrew Trained]) AS [Sum Of # of Aircrew Trained]
FROM [Mission Report]
WHERE ((([Mission Report].[Date Tng Completed]) Between #10/1/2008# And #9/30/2009#))
GROUP BY [Mission Report].[Asset Used];

If there's a way to show all the results of the query on the report I already have built, that would be great.

Thanks for the help.
Vince

 

[PavelowFE]

Nevermind, I got it.

I made a report from just that query, then in my original report, I added a subreport from the new one. The only problem now is that it doesn't let me do much formatting.

Thanks anyways.

Vince

 

[PHV]

Sorry for the missed GROUP BY clause ...