By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Query question:

P: n/a
I need help with a query.

I have the following dataset:

AgCode SumOfPremAmt AppStatus NumOfApps ProducerType
------------------------------------------------------------
11 $35,423.96 HYPOED 12 Broker
11 $28,791.36 HYPOED 13 FR/FTA
11 $17,203.39 NOT TAKEN 6 Broker
11 $25,847.22 NOT TAKEN 7 FR/FTA
11 $127,760.99 RECEIVED 61 Broker
11 $82,692.06 RECEIVED 39 FR/FTA
11 $1,433.53 RECEIVED 1 Pending
11 $1,731.70 REOPENED 2 Broker
11 $12,411.83 REOPENED 8 FR/FTA

Now, in a seperate query, I want the following:

Agency Wastage NumOfAppSubmitted Producer Type
-------------------------------------------------------
11 16 61 Broker

The wastage is calculated by:
(NumOfHypoed - NumOfReopended) + NumOfNotTaken

I know how to do this with multiple queries using joins, but was
wondering if there was a way to get it in one query.

Thanks!
Brian

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I don't know how to get it in one query, but I can get it in only three
using crosstab queries. Access provides a Wizard that helps you create
them, and the result is:

WastageBase_Crosstab:
TRANSFORM Sum(WastageBase.SumOfPremAmt) AS SumOfSumOfPremAmt
SELECT WastageBase.AgCode, WastageBase.ProducerType
FROM WastageBase
GROUP BY WastageBase.AgCode, WastageBase.ProducerType
PIVOT WastageBase.AppStatus;

That gives you the figures you need to add and subtract. Then you need
another query to get the total number of apps submitted:

WastageBase_NumOfApps:
SELECT WastageBase.AgCode, WastageBase.ProducerType, Sum
(WastageBase.NumOfApps) AS SumOfNumOfApps
FROM WastageBase
GROUP BY WastageBase.AgCode, WastageBase.ProducerType;

Then you can join the two together and make the appropriate calculations:

WastageQuery:
SELECT WastageBase_Crosstab.AgCode, WastageBase_Crosstab.ProducerType,
[HYPOED]-[REOPENED]+[NOT TAKEN] AS Wastage,
WastageBase_NumOfApps.SumOfNumOfApps
FROM WastageBase_Crosstab INNER JOIN WastageBase_NumOfApps ON
(WastageBase_Crosstab.ProducerType = WastageBase_NumOfApps.ProducerType)
AND (WastageBase_Crosstab.AgCode = WastageBase_NumOfApps.AgCode);

Note that this is very sensitive to the values in the AppStatus column:
"HYPOED " means the query above won't work (you'd need to call the column
[HYPOED ]). You should also think about whether you need an outer join on
the NumOfApps query so you records without any wastage figures are still
shown.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.