Query help please

[Brian Collins]

I have to create a query that makes a based on a series of calculations.

Monthly I am given a figure relating to two amount of monies

Total_billed and Billed_to_Date.

I have a number of files that fall into six categories, these categories are allocated by the system when the file information is entered.

Costs to Date calc =

1. Total billed on cat 1 divided by count of cat1 + cat2 + cat3



2. (Total billed on cat 1 divided by count of cat1 + cat2 + cat3) plus (Billed_to_Date divided by count of cat2 + cat4)

3 As 2 above but .... Plus(Billed_to_Date divided by count of cat3 + cat5)

4 Billed_to_Date on cat4 divided by count of cat2 + cat4

5 Billed_to_Date on cat5 divided by count of cat3 + cat5


I would be very grateful if anyone could point me in the direction of doing this in an elegant way. My current effort is extremely messy and difficult to manage.

Many thanks for your time

Brian

[weekend00]

Please describe what you have now in more details, like what tables do you have and fields inside the tables and how the data is store in the table

and i don't understand what you want, do you mean you need to get 5 results, 1,2,3,4,5 as you said?

[Brian Collins]

Hi Weekend

Thanks for having a look.

With the exclusion of lookup tables relating to client file numbers, developments, agents and third parties i have two main tables.

1 - Table of Claims: contains all of the information relating to a number of claims for losses incurred when a series of property developments failed.

Contains:

Claimant details
Property details
A number of fields relating to monies paid by the claimant (these are given figures)

From this table I am running a central query containing a number of calculations based on the claimants given figures, such as claimed interest, tax penalties, conversion into sterling and euros. These work fine. Also, this query provides a number which relates to the category of claim (1-6)


Each month we are given, from another system, various figures:
Fees Billed, Costs Billed, Tax billed, Fees Unbilled, Costs Unbilled, Costs Reserve and Claims reserve. These figures live in the second table Statics.

I do not have a relationship between this table and Table of Claims

In the first example (Category claim type 1)
Costs_to_Date: (Fees_billed+Costs_billed+VAT_billed)/( nr of Category 1 + nr of Category 2 + nr of Category 3)

etc etc for the remaining category calcs.

an IIF statement should work, very messy and hard to read tho.

I dont know if this makes it any more clear. Its confusing the hell out of me and I have the data and spreadsheets.

Let me know what you think please

Regards

B

[weekend00]

I am still very confused, since you was talking about this:

1. Total billed on cat 1 divided by count of cat1 + cat2 + cat3

2. (Total billed on cat 1 divided by count of cat1 + cat2 + cat3) plus (Billed_to_Date divided by count of cat2 + cat4)

3 As 2 above but .... Plus(Billed_to_Date divided by count of cat3 + cat5)

4 Billed_to_Date on cat4 divided by count of cat2 + cat4

5 Billed_to_Date on cat5 divided by count of cat3 + cat5

I think your goal is to get these things:
[Total billed on cat 1], [count of cat1,2,3,4,5],[billed_to_date],[billed_to_date on cat4,5].

Since I don't know what's in you tables, I can only give you suggestion to use DCOUNT to get all the counts and use DSUM to get Billed_to_date, although DCOUNT, DSUM work very slowly, they are very easy to use.

[Brian Collins]

Thanks Weekend,

It is very confusing I agree.

There are 6 calculations to be made, I omitted the sixth category in my original post. If you look at those you see that there are subtle differences in which categories are counted and what monies are used.

I think I may have resolved it over the weekend - will be importing data this week.

Many thanks for your time and effort.

B

[weekend00]

u r welcome