473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query question:

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
1 1187
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: majsen | last post by:
Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in...
8
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
3
by: John Ortt | last post by:
> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record...
3
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
2
by: mmitchell_houston | last post by:
I'm working on a .NET project and I need a single query to return a result set from three related tables in Access 2003, and I'm having trouble getting the results I want. The details: ...
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
3
by: Richard Hollenbeck | last post by:
I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.