Lookup and Sumif

[hatimn]

I need to do two functions in Access which would be very simple in Excel. First one is lookup and the other is Sumif.



I have two tables UW and Clm (representing Underwriting and Claims respectively).

UW table contains following three columns:
1: ID (primary key)
2: Insured
3: Amt (currently empty)

Clm table contains the following columns:
1: ID (linked to ID in UW table. Can contain duplicate values)
2: Amt
3: Insured (currently empty)

First thing which I need to do is fill in UW.Amt with sum of Clm.Amt where ID is same. Eg. UW.ID = 1, the value in UW.Amt should be = 8,000. I tried Sum function in Update statement with either INNER JOIN or WHERE UW.Id = CLM.ID but neither are working.

Second thing which I need is to lookup the Insured name from UW table to Clm table by matching the ID numbers.

Like I said both these functions are very simple, however, I am getting stumped at it. Would appreciate if someone could create the update query in the attached file.

Thanks in advance for the help.

Best regards
Hatim

[jzwp11]

Welcome to the forum!

In relational databases, calculated values like sums are generally not stored in a table, you only store the individual data points that are used to get the sum. With that said, there would be no need for the Amt field in the UW nor would there be a need to run an update query. Also, you do not need to repeat data in one table that is already in another joined table. So you do not need the insured field in the Clm table.

You can accomplish what you are after with a totals query (air SQL text, not tested).

SELECT UW.ID, UW.Insured, Sum(Clm.Amt) as SumOfClaims
FROM UW INNER JOIN CLM ON UW.ID=CLM.ID
GROUP BY UW.ID, UW.Insured

Sorry that I could not do this with the database you posted, I only have Access 2003 here at work and are unable to open a 2007/2010 file.

[hatimn]

Hey thanks for the reply.

I agree with your comment on relational DBs and while for other purposes I use connected tables and queries. However, in this particular case the software to which I am uploading data, requires all the data to be in one particular table (and not even query). That is why I need to look up a value.

I will upload the DB in 2000 format tomorrow and any help would be appreciated.

[jzwp11]

The results of a query will look like any other table. I would think that the software you are uploading to should be able to handle it, but then again, you are more familiar with the receiving software than me.

If you build the query as I suggested, could you try to do the upload to see if it works?

Another option is the create the query I suggested and convert it to a Make Table query and make a temp table that you can use for uploading purposes. When done, delete the temp table

[hatimn]

This post contains the file in Access 2000 format. Thanks for the help.

[jzwp11]

I've attached the modified database with 2 queries. qrySumByPolNo illustrates the query I initially suggested that does the summing. qryMakeTableFromSums is the query that makes a table called newtable. I would think that the software you are using will accept the results from qrySumByPolNo. If not then you can use the results as shown in newtable

[hatimn]

Thanks for the response. Your idea works and solves my problem.

Really appreciate your help.

[jzwp11]

You're welcome. Good luck with your project