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.


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:

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:

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:

SELECT WastageBase_Crosstab.AgCode, WastageBase_Crosstab.ProducerType,
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

Message posted via
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.