Totals Query

[sislman]

Hi,

Im very new to MS Access so bear with me.

I have a table containing travel claims data. In some instances there are multiple claims attached to certain policy numbers. Currently the Table has a seperate line of data for each and every claim. What I need to do is to ensure that there is only ever one line of claims data per policy number and that columns containing data such as: Total Reserves / Total Paid / Net Reserves / Claims fees are totalled on that line.

The reason I need to do this is that I then want to run a query that compares total claims per policy against total premium per policy.

I would be grateful for any help.

Regards

Matt

 

[rsmonkey]

What you need is a unique identifier for each policy.. baisically a primary key. When you setup the query you wud have to have the first field as this unique identifier. e.g:
Policy_ID / Total Reserves / Total Paid / Net Reserves / Claims etc.
The unique identifier will mean that it will automatically sum up the values when you run the query..

Im pretty much a newb myself m8 but this is how i did it.. hehe

 

[sislman]

Ok cool thanks i'l give it a go.

Kind regards

 

[sislman]

Hi,

I tried what you suggested but it didnt seem to work.

If I let Access choose a Primary key it just gives each line a different number, therefore, if I have two lines of data with the same Policy ID they are numbered differently.

I tried setting Policy ID as the Unique Idenfier however it would not accept this as there are lots of entries for the same Policy ID (when two or more claims are received fro same Policy ID).

Any other ideas?

Regards

 

[Brianwarnock]

Can you continue with multiple records per policy but use Group By on Policy number?

Brian

 

[sislman]

Hi,

I used the Group By clause however I have to Group by at least 10 Fields or less if i have less than 10 fields in Query.

How can I just Group by 1 field?

Regards

 

[Brianwarnock]

I thought that you just wanted certain fields totalled against the Policy Number, in which case they would be Sum in the Toal line of the query grid, if there is other data unique to the policy in each record then Grouping on it shouldn't matter , perhaps we need more info regaring what your data looks like and what you actually want.

Brian