Tricky query...

[Whizzo]

Hi folks;

Hope the example data below displays properly for you?

CustName      FireID    CustID  PolicyNo  PolicyVal  BuildingVal
Claire Sheene   8       88221    125584    12,500       12,500
Claire Sheene   7       88220
Robin Murphy    9       78552    365557    35,000       35,000
Robin Murphy    6       74859    995554    25,000

This is the datasheet view of some tricky data I'm trying to manhandle. I'm going to be receiving 25000 records to analyse in the next week, and as usual they're going to be a mess! They're all details of fire insurance policies held by bank customers who have loans taken out against their businesses. Some customers have multiple customer numbers, to which are connected multiple insurance policies, each with their own IDs. My job is to find the total value of insurannce policies held by each individual and compare it to the total value of their assets insured, to make sure that if these places burn down then there's enough insurance in place to cover any losses. So I need to find a way to (in the case of the sample data above) add all the policy values and building values together for each customer. How does one do this with a query? To add PolicyVal in one record to PolicyVal in another but only if the CustName is the same? I've had a good look around the Web but without much luck. Any ideas folks?

[FishVal]

Well. Actually it doesn't seem that tricky.

Since [CustName] field value uniquely identifies customer you may use query grouping records on values of this field.

SELECT CustName, Sum(PolicyVal) AS SumOfPolicyVal, Sum(BuildingVal) AS SumOfBuildingVal FROM [TableName] GROUP BY CustName;
 

using HAVING clause you could filter those customers whos insurance doesn't cover building value

SELECT CustName, Sum(PolicyVal) AS SumOfPolicyVal, Sum(BuildingVal) AS SumOfBuildingVal FROM [TableName] GROUP BY CustName HAVING Sum(PolicyVal)<Sum(BuildingVal);
 

[NeoPa]

It does now ;)

Tip for the future :
Use spaces rather than tabs, and use the [ CODE ] tags, and it will layout as intended.

[Whizzo]

Many thanks, I'll see if I can get that working. In truth I've only just started creating queries in Design View within Access so I'm still finding the translation from raw code to the design grid a little opaque! I'm sure I'll be able to pull something out of the bag... Thanks!

[NeoPa]

Some typical and basic code would be :

SELECT Sum(X) AS SumX,
       Count(X) AS CountX,
       ...
 
FROM ...
 
GROUP BY [CustName]

I would however, urge caution whenever grouping by an unformatted text fields (such as names). There are so many potential problems with this that enumerating them is beyond my time limits.

It is probable that this will not succeed completely and that some manual intervention will be required.

[NeoPa]

@Whizzo
You know you can enter SQL into an Access query directly, and take it out too. Use View / SQL View & View / Design View to switch between alternative views of the same query.

[Whizzo]

I did try that, but the SQL in the text box has to be formatted just so. It also won't convert "experimental" code back unless it's been written specifically for your tables; Access spits it out again if there's references to objects that aren't in the DB.

I do have just one more question on the above, though...

As you can see in the sample data in the original quesiton, each individual can have several customer numbers. As part of the same query that adds up the liabilities is it possible for it to output the individual customer numbers too? So each individual will have a customer name in once text field, total value of all policies in another, and in a third all the policy numbers displayed as "1234, 2345, 3456"? This is so an advisor can then look up each individual policy from the main data and find which one isn't right.

Thanks!

[NeoPa]

@Whizzo
Yes. It validates the SQL before trying to parse it. That's actually quite sensible if you think about it.
@Whizzo
I don't think there's an article on this as such, but this thread should give you what you need.
Combining Rows-Opposite of Union